JSONB sort aggregation

I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document.

(Fake, generated random data)

SELECT jsonb_agg(elem)
FROM  (
   SELECT *
   FROM   jsonb_array_elements('[{
        "id": "1",
        "first_name": "Maximo",
        "last_name": "Sambiedge",
        "email": "msambiedge0@economist.com",
        "gender": "Male",
        "ip_address": "242.145.232.65"
    }, {
        "id": "2",
        "first_name": "Maria",
        "last_name": "Selland",
        "email": "aselland1@sitemeter.com",
        "gender": "Female",
        "ip_address": "184.174.58.32"
   }]') a(elem)
   ORDER  BY (elem->>'email')  -- order by integer value of "ts"
) sub;

As we can see, this works with hardcoded data which doesn’t quite fit my needs. I can’t seem to figure out how to replace the JSON data with the jsonb column in my table.

My attempt below yields ‘data is not defined’

SELECT jsonb_agg(elem), (SELECT data FROM file_metadata)
FROM  (
   SELECT *
   FROM   jsonb_array_elements(data) a(elem)
   ORDER  BY (elem->>'email')
) sub;

My suspicions are that a subquery is needed inside the FROM clause?

Here is a SQLFiddle of my issue to help describe the table and how the structure is defined: http://sqlfiddle.com/#!17/41102/92

Answer

You are almost there. You just need to bring in the original table, like so:

SELECT jsonb_agg(elem)
FROM  (
   SELECT elem
   FROM file_metadata, jsonb_array_elements(data) a(elem)
   ORDER BY (elem->>'email')
) sub;

Updated DB Fiddle

Advertisement

Answer