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())

Answer 1

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