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:

column1	column2	column3
name 1	0	10
name 2	0	10
name 3	0	10

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
– Bergi, Commented Apr 16, 2021 at 13:32
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
– Bergi, Commented Apr 16, 2021 at 13:34
i have the tabe in postgresql, i want to create the json file from the postgresql table with a query — Tibor
– Tibor, Commented Apr 16, 2021 at 13:45
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
– Bergi, Commented Apr 16, 2021 at 13:51

user330315 · Accepted Answer · 2021-04-16 13:51:13Z

3

You can use jsonb_object_agg() for this:

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

Online example

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

answered Apr 16, 2021 at 13:51

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Tibor Over a year ago

if the column name are x, y, width, height, pixelRatio, sdf in this sequence and i create the json output the result sequence is random and i get {"S1R1": {"x": 0, "y": 0, "sdf": false, "width": 20, "height": 10, "pixelRatio": 1}} how can i get the origin sequence

user330315 Over a year ago

The order of keys in a JSON value is irrelevant

Tibor · Accepted Answer · 2021-04-16 15:02:59Z

0

copy (select jsonb_object_agg(column1, to_jsonb(t) - 'column1') from the_table t) to 'c:\test4json\test4json.json'; works for the output as a json file

answered Apr 16, 2021 at 15:02

Tibor

1912 silver badges8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related