JSONB sort aggregation

Question

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": "[email protected]",
        "gender": "Male",
        "ip_address": "242.145.232.65"
    }, {
        "id": "2",
        "first_name": "Maria",
        "last_name": "Selland",
        "email": "[email protected]",
        "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

GMB · Accepted Answer · 2019-10-13 20:20:20Z

1

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

edited Oct 13, 2019 at 20:20

answered Oct 13, 2019 at 20:17

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Sterling Archer Over a year ago

Thank you for the quick answer, I was unsure how to bring in the original table to access the data column. It appears to work just as needed!

GMB Over a year ago

Welcome @SterlingArcher! Your question was well-asked and you were so close, this makes it easier to respond quickly!

Collectives™ on Stack Overflow

JSONB sort aggregation

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related