Combining results into a JSON object

Question

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.

I certainly did, just having trouble adapting it to my solution. — ddibiase, Jul 06 '15 at 01:47
Initially marked this as a dup of http://stackoverflow.com/q/13227142/398670 but it isn't, strictly, since that one doesn't involve aggregation. Please show *sample data* (preferably as `CREATE TABLE` and `INSERT` statements) and *expected results*. — Craig Ringer, Jul 06 '15 at 02:10

Craig Ringer · Accepted Answer · 2015-07-06T03:38:10.903

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.

Combining results into a JSON object

1 Answers1