PostgreSQL 9.2 row_to_json() with nested joins

Update: In PostgreSQL 9.4 this improves a lot with the introduction of to_json, json_build_object, json_object and json_build_array, though it's verbose due to the need to name all the fields explicitly:

select        json_build_object(                'id', u.id,                'name', u.name,                'email', u.email,                'user_role_id', u.user_role_id,                'user_role', json_build_object(                        'id', ur.id,                        'name', ur.name,                        'description', ur.description,                        'duty_id', ur.duty_id,                        'duty', json_build_object(                                'id', d.id,                                'name', d.name                        )                )    )from users uinner join user_roles ur on ur.id = u.user_role_idinner join role_duties d on d.id = ur.duty_id;

For older versions, read on.

It isn't limited to a single row, it's just a bit painful. You can't alias composite rowtypes using AS, so you need to use an aliased subquery expression or CTE to achieve the effect:

select row_to_json(row)from (    select u.*, urd AS user_role    from users u    inner join (        select ur.*, d        from user_roles ur        inner join role_duties d on d.id = ur.duty_id    ) urd(id,name,description,duty_id,duty) on urd.id = u.user_role_id) row;

produces, via http://jsonprettyprint.com/:

{  "id": 1,  "name": "Dan",  "email": "someemail@gmail.com",  "user_role_id": 1,  "user_role": {    "id": 1,    "name": "admin",    "description": "Administrative duties in the system",    "duty_id": 1,    "duty": {      "id": 1,      "name": "Script Execution"    }  }}

You will want to use array_to_json(array_agg(...)) when you have a 1:many relationship, btw.

The above query should ideally be able to be written as:

select row_to_json(    ROW(u.*, ROW(ur.*, d AS duty) AS user_role))from users uinner join user_roles ur on ur.id = u.user_role_idinner join role_duties d on d.id = ur.duty_id;

... but PostgreSQL's ROW constructor doesn't accept AS column aliases. Sadly.

Thankfully, they optimize out the same. Compare the plans:

• The nested subquery version; vs
• The latter nested ROW constructor version with the aliases removed so it executes

Because CTEs are optimisation fences, rephrasing the nested subquery version to use chained CTEs (WITH expressions) may not perform as well, and won't result in the same plan. In this case you're kind of stuck with ugly nested subqueries until we get some improvements to row_to_json or a way to override the column names in a ROW constructor more directly.

Anyway, in general, the principle is that where you want to create a json object with columns a, b, c, and you wish you could just write the illegal syntax:

ROW(a, b, c) AS outername(name1, name2, name3)

you can instead use scalar subqueries returning row-typed values:

(SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS outername

Or:

(SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS outername

Additionally, keep in mind that you can compose json values without additional quoting, e.g. if you put the output of a json_agg within a row_to_json, the inner json_agg result won't get quoted as a string, it'll be incorporated directly as json.

e.g. in the arbitrary example:

SELECT row_to_json(        (SELECT x FROM (SELECT                1 AS k1,                2 AS k2,                (SELECT json_agg( (SELECT x FROM (SELECT 1 AS a, 2 AS b) x) )                 FROM generate_series(1,2) ) AS k3        ) x),        true);

the output is:

{"k1":1, "k2":2, "k3":[{"a":1,"b":2},  {"a":1,"b":2}]}

Note that the json_agg product, [{"a":1,"b":2}, {"a":1,"b":2}], hasn't been escaped again, as text would be.

This means you can compose json operations to construct rows, you don't always have to create hugely complex PostgreSQL composite types then call row_to_json on the output.

My suggestion for maintainability over the long term is to use a VIEW to build the coarse version of your query, and then use a function as below:

CREATE OR REPLACE FUNCTION fnc_query_prominence_users( )RETURNS json AS $$DECLARE    d_result            json;BEGIN    SELECT      ARRAY_TO_JSON(                    ARRAY_AGG(                        ROW_TO_JSON(                            CAST(ROW(users.*) AS prominence.users)                        )                    )                )        INTO    d_result        FROM    prominence.users;    RETURN d_result;END; $$LANGUAGE plpgsqlSECURITY INVOKER;

In this case, the object prominence.users is a view. Since I selected users.*, I will not have to update this function if I need to update the view to include more fields in a user record.

I am adding this solution becasue the accepted response does not contemplate N:N relationships. aka: collections of collections of objects

If you have N:N relationships the clausula with it's your friend.In my example, I would like to build a tree view of the following hierarchy.

A Requirement - Has - TestSuitesA Test Suite - Contains - TestCases.

The following query represents the joins.

SELECT reqId ,r.description as reqDesc ,array_agg(s.id)            s.id as suiteId , s."Name"  as suiteName,            tc.id as tcId , tc."Title"  as testCaseTitlefrom "Requirement" r inner join "Has"  h on r.id = h.requirementid inner join "TestSuite" s on s.id  = h.testsuiteidinner join "Contains" c on c.testsuiteid  = s.id inner join "TestCase"  tc on tc.id = c.testcaseid  GROUP BY r.id, s.id;

Since you can not do multiple aggregations, you need to use "WITH".

with testcases as (select  c.testsuiteid,ts."Name" , tc.id, tc."Title"  from "TestSuite" tsinner join "Contains" c on c.testsuiteid  = ts.id inner join "TestCase"  tc on tc.id = c.testcaseid),                requirements as (    select r.id as reqId ,r.description as reqDesc , s.id as suiteId    from "Requirement" r     inner join "Has"  h on r.id = h.requirementid     inner join "TestSuite" s on s.id  = h.testsuiteid    ) , suitesJson as ( select  testcases.testsuiteid,         json_agg(                json_build_object('tc_id', testcases.id,'tc_title', testcases."Title" )            ) as suiteJson    from testcases     group by testcases.testsuiteid,testcases."Name" ),allSuites as (    select has.requirementid,           json_agg(                json_build_object('ts_id', suitesJson.testsuiteid,'name',s."Name"  , 'test_cases', suitesJson.suiteJson )            ) as suites            from suitesJson inner join "TestSuite" s on s.id  = suitesJson.testsuiteid            inner join "Has" has on has.testsuiteid  = s.id            group by has.requirementid),allRequirements as (    select json_agg(            json_build_object('req_id', r.id ,'req_description',r.description , 'test_suites', allSuites.suites )            ) as suites            from allSuites inner join "Requirement" r on r.id  = allSuites.requirementid) select * from allRequirements

What it does is building the JSON object in small collection of items and aggregating them on each with clausules.

Result:

[  {    "req_id": 1,    "req_description": "<character varying>",    "test_suites": [      {        "ts_id": 1,        "name": "TestSuite",        "test_cases": [          {            "tc_id": 1,            "tc_title": "TestCase"          },          {            "tc_id": 2,            "tc_title": "TestCase2"          }        ]      },      {        "ts_id": 2,        "name": "TestSuite",        "test_cases": [          {            "tc_id": 2,            "tc_title": "TestCase2"          }        ]      }    ]  },  {    "req_id": 2,    "req_description": "<character varying> 2 ",    "test_suites": [      {        "ts_id": 2,        "name": "TestSuite",        "test_cases": [          {            "tc_id": 2,            "tc_title": "TestCase2"          }        ]      }    ]  }]