zam6ak zam6ak - 2 months ago 8
JSON Question

Postgres - return row data as JSON array or arrays

On PG v9.4 and higher, I would like to export some data (based on SELECT statement) as JSON array of arrays.

The standard json_agg returns what I want but it returns array of objects (where object keys are column names)
For example this query:

SELECT json_agg(data_rows)
FROM (
-- in memory table sample
SELECT * FROM
(VALUES
('John',now(),1940,'Winston','Lennon'),
('Paul',now(),1942,'','McCartney'),
('George',now(),1943,NULL,'Harrison'),
('Ringo',now(),1940,'my passions are ring,drum and shades','Starr')
) AS x("FirstName", "CurrentDt", "BirthYear", "MiddleName", "LastName")
ORDER BY "BirthYear" DESC, "FirstName" DESC
) AS data_rows


returns following:

[
{"FirstName":"George","CurrentDt":"2016-09-12T13:13:07.862485-04:00","BirthYear":1943,"MiddleName":null,"LastName":"Harrison"},
{"FirstName":"Paul","CurrentDt":"2016-09-12T13:13:07.862485-04:00","BirthYear":1942,"MiddleName":"","LastName":"McCartney"},
{"FirstName":"Ringo","CurrentDt":"2016-09-12T13:13:07.862485-04:00","BirthYear":1940,"MiddleName":"my passions are ring,drum and shades","LastName":"Starr"},
{"FirstName":"John","CurrentDt":"2016-09-12T13:13:07.862485-04:00","BirthYear":1940,"MiddleName":"Winston","LastName":"Lennon"}
]


but what I want is:

[
["George","2016-09-12T13:13:07.862485-04:00",1943,null,"Harrison"},
["Paul","2016-09-12T13:13:07.862485-04:00",1942,"","McCartney"},
["Ringo","2016-09-12T13:13:07.862485-04:00",1940,"my passions are ring,drum and shades","Starr"},
["John","2016-09-12T13:13:07.862485-04:00",1940,"Winston","Lennon"}
]


I have attempted to use the trick mentioned here to convert rows to hstore first, but the problem with this is that column ordering is not preserved....
So this query:

SELECT json_agg(avals(hstore(data_rows)))
FROM (
-- in memory table sample
SELECT * FROM
(VALUES
('John', now(),1940,'Winston','Lennon'),
('Paul', now(),1942,'','McCartney'),
('George',now(),1943,NULL,'Harrison'),
('Ringo', now(),1940,'my passions are ring,drum and shades','Starr')
) AS x("FirstName", "CurrentDt", "BirthYear", "MiddleName", "LastName")
ORDER BY "BirthYear" DESC, "FirstName" DESC
) AS data_rows
-- placing order by 'outside' did not make any difference
--ORDER BY "BirthYear" DESC, "FirstName" DESC


returns following (which has wrong column ordering)

[
["Harrison","1943","2016-09-12 14:07:06.772227-04","George",null],
["McCartney","1942","2016-09-12 14:07:06.772227-04","Paul",""],
["Starr","1940","2016-09-12 14:07:06.772227-04","Ringo","my passions are ring,drum and shades"],
["Lennon","1940","2016-09-12 14:07:06.772227-04","John","Winston"]
]


Does anyone know how to get result as JSON array of arrays?

Answer

You probably want something like that:

SELECT json_agg(info)
FROM (
    SELECT json_build_array("LastName","BirthYear","CurrentDt","FirstName","MiddleName") AS info
    FROM 
        (VALUES
            ('John',now(),1940,'Winston','Lennon'),
            ('Paul',now(),1942,'','McCartney'),
            ('George',now(),1943,NULL,'Harrison'),
            ('Ringo',now(),1940,'my passions are ring,drum and shades','Starr')
        ) AS x("FirstName", "CurrentDt", "BirthYear", "MiddleName", "LastName")
    ORDER BY "BirthYear" DESC, "FirstName" DESC
) as t;

I used json_build_array to put all the values for each person in an json array and then in the outside query, I used json_agg to collect all those arrays into a single array of arrays.

You can also move the ORDER BY clause inside the aggragate function to obtain the following:

SELECT json_agg(json_build_array("LastName","BirthYear","CurrentDt","FirstName","MiddleName") ORDER BY "BirthYear" DESC, "FirstName" DESC)
    FROM 
        (VALUES
            ('John',now(),1940,'Winston','Lennon'),
            ('Paul',now(),1942,'','McCartney'),
            ('George',now(),1943,NULL,'Harrison'),
            ('Ringo',now(),1940,'my passions are ring,drum and shades','Starr')
        ) AS x("FirstName", "CurrentDt", "BirthYear", "MiddleName", "LastName");