From: andrew cooke <andrew@...>

Date: Sat, 24 Jan 2015 14:46:12 -0300

In part 1 - http://acooke.org/cute/Constructi0.html - I showed how to construct JSON objects by doing the following: * Converting a table into a column of JSON objects with "row_to_json()" * Creating arrays with "json_agg()" * "Digging down" into JSON values with -> * Extracting JSON values as strings with ->> * Combining these steps using "with" But the end result was an over-complex mess. Here I'll show two more techniques that help construct something that looks much nicer (I said it required one more trick, but when I sat down to explain things it was better to split it into two - sorry): * Split the data into two - IDs needed for construction, and JSON values * Construct objects from a restricted set of columns. So what I am going to do here is use the same data as part 1, but throw those two extra ideas into the mix. I won't explain things that I think I have already covered, so you may need to jump back to the previous article to understand some steps. The Data -------- To save you some time, here are the data (exactly as before): drop table grandchild; drop table child; drop table parent; create table parent ( id serial primary key, name text ); create table child ( id serial primary key, parent int references parent(id), name text ); create table grandchild ( id serial primary key, parent int references child(id), name text ); insert into parent (name) values ('albert'), ('barbara'), ('claude'); insert into child (name, parent) values ('delilah', 1), ('ebeneezer', 1), ('francoise', 3); insert into grandchild (name, parent) values ('george', 1), ('henrietta', 2), ('ibrahim', 3), ('jenny', 3); The Target ---------- And here's what we're aiming for (hand written, without quotes, may contain errors): [{name: albert, children: [{name: delilah, children: [george]}, {name: ebeneezer, children: [henrietta]}]}, {name: barbara, children: []}, {name: claude, children: [{name: francoise, children: [ibrahim, jenny]}]}] As before, I'll build things up from "the inside". Here goes... List Grandchildren ------------------ Looking at the target, the "innermost" data are lists of grandchildren. We will need to join these to the "child" table, so we need the "parent" column from "grandchild", but we don't want it in the JSON itself. So we need to construct a table with two columns: with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent) select * from json_grandchild_arr; parent | children --------+---------------------- 1 | ["george"] 3 | ["ibrahim", "jenny"] 2 | ["henrietta"] (3 rows) Join With Child --------------- Now we need to join with "child", so let's do that: with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent), child_grandchild_join as (select * from child as c inner join json_grandchild_arr as g on c.id = g.parent) select * from child_grandchild_join; id | parent | name | parent | children ----+--------+-----------+--------+---------------------- 1 | 1 | delilah | 1 | ["george"] 3 | 3 | francoise | 3 | ["ibrahim", "jenny"] 2 | 1 | ebeneezer | 2 | ["henrietta"] (3 rows) Create Child Object ------------------- OK, here's the clever bit. We want to create a JSON object using just the "name" and "children" columns. We can do this by creating a "row" with those contents. There's just one problem: with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent), child_grandchild_join as (select c.id, c.parent, c.name, g.children from child as c inner join json_grandchild_arr as g on c.id = g.parent) select id, parent, row_to_json(row(name, children)) from child_grandchild_join; id | parent | row_to_json ----+--------+---------------------------------------------- 1 | 1 | {"f1":"delilah","f2":["george"]} 3 | 3 | {"f1":"francoise","f2":["ibrahim", "jenny"]} 2 | 1 | {"f1":"ebeneezer","f2":["henrietta"]} (3 rows) (I explicitly selected "c.id, c.parent, c.name, g.children" to avoid the ambiguous parent column in the previous result; without that the "g.parent" gives an error - try it and see). The problem above is that the names of the JSON attributes are wrong! Instead of "name" and "children" we have "f1" and "f2". Bummer. To fix this we need to cast the row we create to a pre-existing type, with those column names. Effectively, that means we need a table with those names. So we need to create a table. Note - we are not putting anything in this table! We're using it ONLY so that the column names appear as JSON attributes. But, despite not putting anything in the table, the column types must match whatever we are casting. Here's an example. First, our problem: select row_to_json(row(1, 'a')); row_to_json ------------------- {"f1":1,"f2":"a"} (1 row) and the solution via a new table (I'll prefix these tables with "type_" to indicate that they are used only as types in the code): create table type_example ( foo int, bar text ); select row_to_json(row(1, 'a')::type_example); row_to_json --------------------- {"foo":1,"bar":"a"} (1 row) Yay! The JSON object we create now has attributes called "foo" and "bar". So, back to our problem. Here's the next step, fixed with a type: drop table type_child; create table type_child ( name text, children json ); with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent), child_grandchild_join as (select c.id, c.parent, c.name, g.children from child as c inner join json_grandchild_arr as g on c.id = g.parent), json_child as (select parent, row_to_json(row(name, children)::type_child) as child from child_grandchild_join) select * from json_child; parent | child --------+------------------------------------------------------ 1 | {"name":"delilah","children":["george"]} 3 | {"name":"francoise","children":["ibrahim", "jenny"]} 1 | {"name":"ebeneezer","children":["henrietta"]} (3 rows) Which is pretty awesome. Group By Parent --------------- There's nothing new here, or in any of the following steps. I'm just applying the tricks above to go the process of building things up, piece by piece. drop table type_child; create table type_child ( name text, children json ); with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent), child_grandchild_join as (select c.id, c.parent, c.name, g.children from child as c inner join json_grandchild_arr as g on c.id = g.parent), json_child as (select parent, row_to_json(row(name, children)::type_child) as child from child_grandchild_join), json_child_arr as (select parent, json_agg(child) as children from json_child group by parent) select * from json_child_arr; parent | children --------+------------------------------------------------------------------------------------------- 1 | [{"name":"delilah","children":["george"]}, {"name":"ebeneezer","children":["henrietta"]}] 3 | [{"name":"francoise","children":["ibrahim", "jenny"]}] (2 rows) Join With Parent ---------------- drop table type_child; create table type_child ( name text, children json ); with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent), child_grandchild_join as (select c.id, c.parent, c.name, g.children from child as c inner join json_grandchild_arr as g on c.id = g.parent), json_child as (select parent, row_to_json(row(name, children)::type_child) as child from child_grandchild_join), json_child_arr as (select parent, json_agg(child) as children from json_child group by parent), parent_child_join as (select p.name, c.children from json_child_arr as c inner join parent as p on p.id = c.parent) select * from parent_child_join; name | children --------+------------------------------------------------------------------------------------------- albert | [{"name":"delilah","children":["george"]}, {"name":"ebeneezer","children":["henrietta"]}] claude | [{"name":"francoise","children":["ibrahim", "jenny"]}] (2 rows) Ooops! We've lost a parent because there was no child for the inner join. So we need to fix that up (and also where we could have had the same problem earlier): drop table type_child; create table type_child ( name text, children json ); with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent), child_grandchild_join as (select c.id, c.parent, c.name, case when g.children is null then '[]'::json else g.children end from child as c right join json_grandchild_arr as g on c.id = g.parent), json_child as (select parent, row_to_json(row(name, children)::type_child) as child from child_grandchild_join), json_child_arr as (select parent, json_agg(child) as children from json_child group by parent), parent_child_join as (select p.name, case when c.children is null then '[]'::json else c.children end from json_child_arr as c right join parent as p on p.id = c.parent) select * from parent_child_join; name | children ---------+------------------------------------------------------------------------------------------- albert | [{"name":"delilah","children":["george"]}, {"name":"ebeneezer","children":["henrietta"]}] claude | [{"name":"francoise","children":["ibrahim", "jenny"]}] barbara | [] (3 rows) Final Parent Object ------------------- We don't need a type table here because we can use the whole table (there's no need for an extra column for an "id" or "parent" because we have no more work to do). And, finally, we stick everything inside a JSON array. drop table type_child; create table type_child ( name text, children json ); with json_grandchild_arr as (select parent, json_agg(name) as children from grandchild group by parent), child_grandchild_join as (select c.id, c.parent, c.name, case when g.children is null then '[]'::json else g.children end from child as c right join json_grandchild_arr as g on c.id = g.parent), json_child as (select parent, row_to_json(row(name, children)::type_child) as child from child_grandchild_join), json_child_arr as (select parent, json_agg(child) as children from json_child group by parent), parent_child_join as (select p.name, case when c.children is null then '[]'::json else c.children end from json_child_arr as c right join parent as p on p.id = c.parent), json_parent as (select row_to_json(parent_child_join) as parent from parent_child_join) select json_agg(parent) from json_parent group by true; json_agg ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"name":"albert","children":[{"name":"delilah","children":["george"]}, {"name":"ebeneezer","children":["henrietta"]}]}, {"name":"claude","children":[{"name":"francoise","children":["ibrahim", "jenny"]}]}, {"name":"barbara","children":[]}] (1 row) And here's the same JSON result formatted more nicely: [{"name":"albert", "children":[{"name":"delilah", "children":["george"]}, {"name":"ebeneezer", "children":["henrietta"]}]}, {"name":"claude", "children":[{"name":"francoise", "children":["ibrahim", "jenny"]}]}, {"name":"barbara","children":[]}] Perfect! As a parting comment, it's worth noting that the above no longer uses -> or ->>. I suspect that it's more efficient as a result. Andrew