PostgreSQL single-column table to JSON array

Question

I have a query like this:

SELECT DISTINCT floor FROM rooms WHERE building_id = 23;

I want to convert the result into a JSON array. I was able to use json_agg() as follows:

SELECT json_agg(a) from (SELECT DISTINCT floor FROM rooms WHERE building_id = 23) as a;

But that results in an array of key/value pairs:

[{"floor":null},{"floor":1},{"floor":4},{"floor":2},{"floor":0},{"floor":3}]

When really I want an array of just the values:

[null,1,4,2,0,3]

For performance and bandwidth reasons, I don't want to have the column name repeated unnecessarily. Any ideas on how to make a JSON array of just the values?

(I also tried array_to_json(array_agg()), but that produced the same results as json_agg())

Why not convert the data structure to json in whatever language you are using to do the query? — Matt Koskela
– Matt Koskela, Commented Feb 9, 2019 at 22:31
@MattKoskela I could, but building the JSON in Postgres is (potentially) much faster. Postgres's implementation of JSON encoding is probably faster than the library I can use in my application, and by doing it all in Postgres there aren't any extraneous intermediate formats. Rather than Table -> ODBC? -> Psycopg2 -> Python data structures -> JSON, it gets turned into JSON before it even leaves Postgres, and it's just a string from then on. — A. Davis
– A. Davis, Commented Feb 9, 2019 at 23:48

Andomar · Accepted Answer · 2019-02-09 22:44:53Z

4

The a in json_agg(a) is a table alias. If you encode that into JSON, you'll get a dictionary with column name / column value pairs. Refer to the column name instead:

select  json_agg(floor)
from    (
        select  distinct floor
        from    rooms 
        where   building_id = 23
        ) a

Or even simpler, use json_agg(distinct ...) instead of a subquery:

select  json_agg(distinct floor)
from    rooms
where   building_id = 23

edited Feb 9, 2019 at 22:44

answered Feb 9, 2019 at 22:39

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL single-column table to JSON array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related