CODEWITHSUNDEEP
sqlarraysjsonpostgresqlaggregate-functions
java_developer
java_developer

Reputation: 13

Get Postgres Table Data as Json Without Field Names

I want to convert Postgres table data to JSON without repeated field names at JSON result. When I use current PostgreSQL json functions, JSON results look likes similar to this : [{"id":"1234","name":"XYZ"},....]. But by this way, all field names unnecessarily exists on every row. So we does not prefer this way for the network bandwith.

We want to get JSON result such as [["1234","XYZ"],....]. So total length of result json string will be much smaller.

Upvotes: 1

Views: 1513

Answers (1)

GMB
GMB

Reputation: 222622

Well, you could use json(b)_build_array() to turn each record to an array - this requires you to enumerate the column names:

select jsonb_build_array(id, name) js from mytable

If you want all rows in a single array of arrays, then you can use aggregation on top of this:

select jsonb_agg(jsonb_build_array(id, name)) res from mytable

Demo on DB Fiddle:

select jsonb_agg(jsonb_build_array(id, name)) res 
from (values(1, 'foo'), (2, 'bar')) as t(id, name)

| res                      |
| :----------------------- |
| [[1, "foo"], [2, "bar"]] |

Upvotes: 2

Related Questions