Postgres - return row data as JSON array or arrays
<p>On PG v9.4 and higher, I would like to export some data (based on SELECT statement) as <strong>JSON array of arrays</strong>.</p>

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

<pre><code>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
</code></pre>

<p>returns following:</p>

<pre><code>[
 {"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"}
]
</code></pre>

<p><strong>but what I want is:</strong></p>

<pre><code>[
 ["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"}
]
</code></pre>

<p>I have attempted to use the trick <a href="https://stackoverflow.com/a/11472794/481904">mentioned here</a> to convert rows to hstore first, but the problem with this is that column ordering is not preserved....
So this query:</p>

<pre><code>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
</code></pre>

<p>returns following (which has wrong column ordering)</p>

<pre><code>[
 ["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"]
]
</code></pre>

<p>Does anyone know how to get result as JSON array of arrays?</p>