C[omp]ute

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