CODEWITHSUNDEEP
sqlpostgresql
ddibiase
ddibiase

Reputation: 1502

Combining results into a JSON object

I'm wondering if it's possible to return a list of results as a JSON object. My query is more complex than the below lets on but for the sake of simplicity express what I'm aiming for.

SELECT 
    r.name, r.address
FROM "Locations" AS r 
LEFT JOIN "Units" AS f 
    ON r.city = f.city

There are many potential Units per location but it would be great of those came back as JSON as opposed to modifying my full table structure.

So the response I'm looking for is one row for each location with the columns: name (string), address (string) and units (JSON representing all the rows).

This would never be possible in MySQL, wondering if I can do it in Postgres. The data that will be JSONd isn't large so I'm not too concerned about performance.

Upvotes: 0

Views: 1031

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324465

You're going to want something like:

SELECT 
    json_build_object(
      'name', r.name,
      'address', r.address,
      'units', json_agg( json_build_object(
        'number', f.number,
        -- etc etc fields of units here
      ))
    )
FROM "Locations" AS r 
LEFT JOIN "Units" AS f 
    ON r.city = f.city
GROUP BY r.id;

It's hard to be entirely sure since you haven't provided a schema, sample data, expected output, etc.

Note that you cannot nest json_agg calls. A different approach is needed for multi-level results where there are several levels of join. Try providing something that better reflects your actual use case, with sample data and expected results, if that's a concern.

You might need to reformulate with a nested series of queries in FROM to get the required aggregation of nested levels. I believe condition pull-up and push-down should still prevent the creation of JSON for rows to be discarded, but haven't conclusively verified this.

Upvotes: 2

Related Questions