how to create a single json string from a postgresql table with sql query

Question

how can i create a json file from a postgresql table "test4json" with sql query:

a single json file in one row...withot CRLF such this:

{"name 1": {"column2": 0,"column3": 10},"name 2": {"column2": 0,"column3": 10},"name 3":{"column2": 0,"column3": 10}}

for the values column 1 i dont't need the name of the column !

and how can i create from the result a test4json.json file in a directory c:\test4json ?

Origin Table is

name	y	width	height	pixelRatio	sdf
S1R1	0	20	10	1	false
S1R2	10	20	10	1	false
S1R3	20	20	10	1	false
S1R4	30	20	10	1	false
S1R5	40	20	10	1	false

thx

It's really unclear what you are asking for. You already have successfully created a json text representing your table. Now put it into a file and save it. What exactly is the problem? — Bergi
Ah, I had missed the [postgresql] tag on the question. Are you saying you don't have a HTML table, but an arbitrary database table, and want to convert that to json? Have a look at dba.stackexchange.com/q/90482/188406 — Bergi
i have the tabe in postgresql, i want to create the json file from the postgresql table with a query — Tibor
Please post your table definition (and sample data, if necessary) as SQL. Also show us what you tried and how the existing Q&As didn't help — Bergi

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2021-04-16T13:51:13

You can use jsonb_object_agg() for this:

select jsonb_object_agg(column1, to_jsonb(t) - 'column1')
from the_table t;

How you save that as a JSON file depends completely on the SQL client you are using. In psql you could use the \o ("output to") meta command