Andrew Cooke | Contents | Latest | RSS | Previous | Next

C[omp]ute

Welcome to my blog, which was once a mailing list of the same name and is still generated by mail. Please reply via the "comment" links.

Always interested in offers/projects/new ideas. Eclectic experience in fields like: numerical computing; Python web; Java enterprise; functional languages; GPGPU; SQL databases; etc. Based in Santiago, Chile; telecommute worldwide. CV; email.

Personal Projects

Choochoo Training Diary

Last 100 entries

Surprise Paradox; [Books] Good Author List; [Computing] Efficient queries with grouping in Postgres; [Computing] Automatic Wake (Linux); [Computing] AWS CDK Aspects in Go; [Bike] Adidas Gravel Shoes; [Computing, Horror] Biological Chips; [Books] Weird Lit Recs; [Covid] Extended SIR Models; [Art] York-based Printmaker; [Physics] Quantum Transitions are not Instantaneous; [Computing] AI and Drum Machines; [Computing] Probabilities, Stopping Times, Martingales; bpftrace Intro Article; [Computing] Starlab Systems - Linux Laptops; [Computing] Extended Berkeley Packet Filter; [Green] Mainspring Linear Generator; Better Approach; Rummikub Solver; Chilean Poetry; Felicitations - Empowerment Grant; [Bike] Fixing Spyre Brakes (That Need Constant Adjustment); [Computing, Music] Raspberry Pi Media (Audio) Streamer; [Computing] Amazing Hack To Embed DSL In Python; [Bike] Ruta Del Condor (El Alfalfal); [Bike] Estimating Power On Climbs; [Computing] Applying Azure B2C Authentication To Function Apps; [Bike] Gearing On The Back Of An Envelope; [Computing] Okular and Postscript in OpenSuse; There's a fix!; [Computing] Fail2Ban on OpenSuse Leap 15.3 (NFTables); [Cycling, Computing] Power Calculation and Brakes; [Hardware, Computing] Amazing Pockit Computer; Bullying; How I Am - 3 Years Post Accident, 8+ Years With MS; [USA Politics] In America's Uncivil War Republicans Are The Aggressors; [Programming] Selenium and Python; Better Walking Data; [Bike] How Fast Before Walking More Efficient Than Cycling?; [COVID] Coronavirus And Cycling; [Programming] Docker on OpenSuse; Cadence v Speed; [Bike] Gearing For Real Cyclists; [Programming] React plotting - visx; [Programming] React Leaflet; AliExpress Independent Sellers; Applebaum - Twilight of Democracy; [Politics] Back + US Elections; [Programming,Exercise] Simple Timer Script; [News] 2019: The year revolt went global; [Politics] The world's most-surveilled cities; [Bike] Hope Freehub; [Restaurant] Mama Chau's (Chinese, Providencia); [Politics] Brexit Podcast; [Diary] Pneumonia; [Politics] Britain's Reichstag Fire moment; install cairo; [Programming] GCC Sanitizer Flags; [GPU, Programming] Per-Thread Program Counters; My Bike Accident - Looking Back One Year; [Python] Geographic heights are incredibly easy!; [Cooking] Cookie Recipe; Efficient, Simple, Directed Maximisation of Noisy Function; And for argparse; Bash Completion in Python; [Computing] Configuring Github Jekyll Locally; [Maths, Link] The Napkin Project; You can Masquerade in Firewalld; [Bike] Servicing Budget (Spring) Forks; [Crypto] CIA Internet Comms Failure; [Python] Cute Rate Limiting API; [Causality] Judea Pearl Lecture; [Security, Computing] Chinese Hardware Hack Of Supermicro Boards; SQLAlchemy Joined Table Inheritance and Delete Cascade; [Translation] The Club; [Computing] Super Potato Bruh; [Computing] Extending Jupyter; Further HRM Details; [Computing, Bike] Activities in ch2; [Books, Link] Modern Japanese Lit; What ended up there; [Link, Book] Logic Book; Update - Garmin Express / Connect; Garmin Forerunner 35 v 230; [Link, Politics, Internet] Government Trolls; [Link, Politics] Why identity politics benefits the right more than the left; SSH Forwarding; A Specification For Repeating Events; A Fight for the Soul of Science; [Science, Book, Link] Lost In Math; OpenSuse Leap 15 Network Fixes; Update; [Book] Galileo's Middle Finger; [Bike] Chinese Carbon Rims; [Bike] Servicing Shimano XT Front Hub HB-M8010; [Bike] Aliexpress Cycling Tops; [Computing] Change to ssh handling of multiple identities?; [Bike] Endura Hummvee Lite II; [Computing] Marble Based Logic; [Link, Politics] Sanity Check For Nuclear Launch; [Link, Science] Entropy and Life

© 2006-2017 Andrew Cooke (site) / post authors (content).

Constructing JSON From Postgres (Part 2)

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

Comment on this post