I'm trying to map the results of a query to JSON using the row_to_json()
function that was added in PostgreSQL 9.2.
I'm having trouble figuring out the best way to represent joined rows as nested objects (1:1 relations)
Here's what I've tried (setup code: tables, sample data, followed by query):
-- some test tables to start out with: create table role_duties ( id serial primary key, name varchar ); create table user_roles ( id serial primary key, name varchar, description varchar, duty_id int, foreign key (duty_id) references role_duties(id) ); create table users ( id serial primary key, name varchar, email varchar, user_role_id int, foreign key (user_role_id) references user_roles(id) ); DO $$ DECLARE duty_id int; DECLARE role_id int; begin insert into role_duties (name) values ('Script Execution') returning id into duty_id; insert into user_roles (name, description, duty_id) values ('admin', 'Administrative duties in the system', duty_id) returning id into role_id; insert into users (name, email, user_role_id) values ('Dan', '[email protected]', role_id); END$$;
The query itself:
select row_to_json(row) from ( select u.*, ROW(ur.*::user_roles, ROW(d.*::role_duties)) as user_role from users u inner join user_roles ur on ur.id = u.user_role_id inner join role_duties d on d.id = ur.duty_id ) row;
I found if I used ROW()
, I could separate the resulting fields out into a child object, but it seems limited to a single level. I can't insert more AS XXX
statements, as I think I should need in this case.
I am afforded column names, because I cast to the appropriate record type, for example with ::user_roles
, in the case of that table's results.
Here's what that query returns:
{ "id":1, "name":"Dan", "email":"[email protected]", "user_role_id":1, "user_role":{ "f1":{ "id":1, "name":"admin", "description":"Administrative duties in the system", "duty_id":1 }, "f2":{ "f1":{ "id":1, "name":"Script Execution" } } } }
What I want to do is generate JSON for joins (again 1:1 is fine) in a way where I can add joins, and have them represented as child objects of the parents they join to, i.e. like the following:
{ "id":1, "name":"Dan", "email":"[email protected]", "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" } } } }
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 u inner join user_roles ur on ur.id = u.user_role_id inner 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": "[email protected]", "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 u inner join user_roles ur on ur.id = u.user_role_id inner 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:
ROW
constructor version with the aliases removed so it executesBecause 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With