Get Postgres Table Data as Json Without Field Names

Question

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.

I'm pretty sure that JSON requires field names. So whatever you might call your format, "JSON" doesn't seem appropriate. — Gordon Linoff
– Gordon Linoff, Commented May 1, 2020 at 13:40
This is really a question that would be specific to whatever ORM and/or programming language you are using, because JSON supports defining arrays. You can of course encode only the row values from your query and store them as an array of JSON arrays (essentially treating the array containing each row's values as a tuple). — Everett
– Everett, Commented May 1, 2020 at 19:15
@Everett we are using Java Hibernate as ORM. But we don't prefer to use Java for business logic. Our developers have experince on Database development (STP). We are aiming stored procedures could be directly accessed from react based frontend structure over JAVA proxy application. On JAVA side we will check only permissions. We are plannging to send table body and column names separately to the frontend side. — java_developer
– java_developer, Commented May 2, 2020 at 6:28

GMB · Accepted Answer · 2020-05-01 13:46:35Z

2

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"]] |

answered May 1, 2020 at 13:46

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

java_developer Over a year ago

Thanks @GMB , this method mainly solved my problem. But I want to ask is there any way of getting all columns without specifying column names, I am trying to find such method select jsonb_build_array(*) js from mytable (I've tried this but not worked.) . I will try to get column names via different function.

Collectives™ on Stack Overflow

Get Postgres Table Data as Json Without Field Names

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related