| Andrew Cooke | Contents | RSS | Twitter | Previous

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.

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

List of Good Fiction Books

From: andrew cooke <andrew@...>

Date: Sun, 25 Jan 2015 10:36:41 -0300

http://4chanlit.wikia.com/wiki/Recommended_Reading/Literature_by_type

Andrew

Permalink | Comment on this post

Previous Entries

For comments, see relevant pages (permalinks).

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

Permalink

Constructing JSON From Postgres (Part 1)

From: andrew cooke <andrew@...>

Date: Sat, 24 Jan 2015 11:13:09 -0300

Recent releases of Postgres have included support for JSON.  You can store
JSON objects, access values within the objects, and construct new objects.

The most relevant docs are 

 * http://www.postgresql.org/docs/9.4/static/datatype-json.html

 * http://www.postgresql.org/docs/9.4/static/functions-json.html

 * http://www.postgresql.org/docs/9.4/static/functions-aggregate.html


However, there don't seem to be many good examples on the web for how to use
these.  So work asked me to look at how we can construct nested JSON objects
from complex queries.  These posts are a summary of what I have found.

Disclaimer: I don't claim that the techniques here are optimal, or even a good
idea.  They're purely to show what can be done.  There may be better ways to
do these things, and it may be best not to do them in Postgres at all...


Example Data
------------

These are the data I will be using.  They describe a relatively simple 1-many
parent/child/grandchild relationship.

  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);

Our eventual aim (which won't be complete until part 2) is to construct
something like (I'm writing this by hand, so forgive the lack of quotes and any
other errors):

[{name: albert,
  children: [{name: delilah,
              children: [george]},
             {name: ebeneezer,
              children: [henrietta]}]},
 {name: barbara,
  children: []},
 {name: claude,
  children: [{name: francoise,
              children: [ibrahim, jenny]}]}]

But this is quite ambitious - the output doesn't include IDs, and the
grandchildren are treated as simple strings.  In this first post I will aim
for something simpler, with objects at each level, and including IDs.


With Queries
------------

We will build the JSON structure in multiple steps, following a few simple
"recipes".  This can get quite messy in SQL - one way to simplify things is to
use "with" to isolate and name each step.

Here's a simple example (all the examples here assume that the data above are
present in the database):

    with with_example
      as (select parent,
                 array_agg(name) as children
            from child
           group by parent)
  select *
    from with_example as e
   inner join parent as p
      on p.id = e.parent;

This works like a temporary definition of a table, called "with_example",
which has the "child" table grouped by the "parent" attribute.  That table is
then joined with the "parent" table, giving:

   parent |      children       | id |  name  
  --------+---------------------+----+--------
        1 | {delilah,ebeneezer} |  1 | albert
        3 | {francoise}         |  3 | claude
  (2 rows)

Note that "array_agg()" is a function that constructs native Postgres arrays
(in a moment we will use "json_agg()" which is similar, but constructs JSON
arrays).

More generally, you can have a series of "with" definitions, that work like
you're defining a series of tables.  This lets you work through the process of
creating the JSON (or doing any other query) in simple steps, one at a time.

If you're confused about what's happening, you can always put a simple select
at the end of a series of "with" queries to display what you have so far.

For example:

    with with_example
      as (select parent,
                 array_agg(name) as children
            from child
           group by parent)
  select *
    from with_example;

   parent |      children       
  --------+---------------------
        1 | {delilah,ebeneezer}
        3 | {francoise}
  (2 rows)


Plan of Attack
--------------

In the next few sections I am going to create a nested JSON object, using
"with" queries (see above).

The general idea is that we start from the "innermost" objects and work our
way up.  Each step will be a new "with" query.


Creating JSON Objects
---------------------

The simplest way to create a JSON object is from a row in a select statement.
This creates an object whose names are taken from the columns, and whose
values are taken from the rows.

    with json_grandchild
      as (select row_to_json(grandchild) as grandchild_obj
            from grandchild)
  select *
    from json_grandchild;

               grandchild_obj             
  ----------------------------------------
   {"id":1,"parent":1,"name":"george"}
   {"id":2,"parent":2,"name":"henrietta"}
   {"id":3,"parent":3,"name":"ibrahim"}
   {"id":4,"parent":3,"name":"jenny"}
  (4 rows)

If this seems complicated, break it down:

 * The "select * from json_grandchild" is just there so we can see the
   results.

 * The "as grandchild_obj" gives a name to the column in the final 
   result.

 * The "select row_to_json(grandchild) ... from grandchild" is just 
   applying the "row_to_json()" function to each row of the table, 
   creating a JSON object.

 * The "with json_grandchild as" is saving what we've done as something like a
   temporary table, called "json_grandchild" (with one column, called
   "grandchild_obj", which contains a JSON object for each line of the
   "grandchild" table).


Inspecting JSON Values
----------------------

Now that we have JSON objects, we need to be able to look "inside" them.
There is a small problem here - JSON is "untyped".  That is, the database does
not "know" that "id" in the objects above is an integer.  So the only way to
access it is as a string.  If we (as programmers) know that it is an integer,
then we can cast that string to an "int" as a separate step.

So here's an intermediate step that shows how we access the "id":

    with json_grandchild
      as (select row_to_json(grandchild) as grandchild_obj
            from grandchild)
  select (grandchild_obj->>'id')::int as id
    from json_grandchild;

   id 
  ----
    1
    2
    3
    4
  (4 rows)

Note the clumsy syntax - we need quotes around "id" and parens around the
value we are casting.


Creating JSON Arrays
--------------------

Given that we have "grandchild" objects, we now need to group them by parent
(this is the next step in constructing the output, building things up).

    with json_grandchild
      as (select row_to_json(grandchild) as grandchild_obj
            from grandchild),
         json_grandchild_arr
      as (select json_agg(json_grandchild) as grandchild_arr
            from json_grandchild
           group by (grandchild_obj->>'parent')::int)
  select *
    from json_grandchild_arr;

                         grandchild_arr                        
  -------------------------------------------------------------
   [{"grandchild_obj":{"id":1,"parent":1,"name":"george"}}]
   [{"grandchild_obj":{"id":3,"parent":3,"name":"ibrahim"}},  +
    {"grandchild_obj":{"id":4,"parent":3,"name":"jenny"}}]
   [{"grandchild_obj":{"id":2,"parent":2,"name":"henrietta"}}]
  (3 rows)

Again, if this looks complex, break it down.  All we've done is add an extra
step to what we had before, where we're using "group by" and "json_agg" to
place all the objects with the same "parent" value in an array.


Join With Child
---------------

Next we need to join what we have with the "child" table.  This is your normal
SQL join.  The only complication is that we need to extract the "parent" value
from the arrays above (every entry in the array has the same parent value, so
we use the first).

    with json_grandchild
      as (select row_to_json(grandchild) as grandchild_obj
            from grandchild),
         json_grandchild_arr
      as (select json_agg(json_grandchild) as grandchild_arr
            from json_grandchild
           group by (grandchild_obj->>'parent')::int),
         child_grandchild_join
      as (select *
            from json_grandchild_arr as g
           inner join child as c
              on c.id = (g.grandchild_arr->0->'grandchild_obj'->>'parent')::int)
  select *
    from child_grandchild_join;

                         grandchild_arr                        | id | parent |
                         name    
  -------------------------------------------------------------+----+--------+-----------
   [{"grandchild_obj":{"id":1,"parent":1,"name":"george"}}]    |  1 |      1 |
                         delilah
   [{"grandchild_obj":{"id":3,"parent":3,"name":"ibrahim"}},  +|  3 |      3 |
                         francoise
    {"grandchild_obj":{"id":4,"parent":3,"name":"jenny"}}]     |    |        | 
   [{"grandchild_obj":{"id":2,"parent":2,"name":"henrietta"}}] |  2 |      1 |
                         ebeneezer
  (3 rows)

This is getting a little tricky to read, sorry (it's going to get worse before
it gets better).

The most important thing to understand here is that we used "->" to dive
inside the JSON data.  This is NOT THE SAME as the "->>" we used earlier:

 * ->> selects a string (which we have typically then cast to an integer)

 * -> selects a JSON value of some type (object, array, int, string, etc).

So "g.grandchild_arr->0->'grandchild_obj'->>'parent'" is taking the first (0
index) array entry, then the "grandchild_obj" attribute, then converting the
"parent" attribute of that to a (Postgres) string.


Create A JSON Object (Again)
----------------------------

From here on we're basically repeating ourselves for the next level.  Things
look worse and worse, but it's the same logic as before.

    with json_grandchild
      as (select row_to_json(grandchild) as grandchild_obj
            from grandchild),
         json_grandchild_arr
      as (select json_agg(json_grandchild) as grandchild_arr
            from json_grandchild
           group by (grandchild_obj->>'parent')::int),
         child_grandchild_join
      as (select *
            from json_grandchild_arr as g
           inner join child as c
              on c.id = (g.grandchild_arr->0->'grandchild_obj'->>'parent')::int),
         json_child
      as (select row_to_json(child_grandchild_join) as child_obj
            from child_grandchild_join)
  select *
    from json_child;

                                                        child_obj                                                      
  ---------------------------------------------------------------------------------------------------------------------
   {"grandchild_arr":[{"grandchild_obj":{"id":1,"parent":1,"name":"george"}}],"id":1,"parent":1,"name":"delilah"}
   {"grandchild_arr":[{"grandchild_obj":{"id":3,"parent":3,"name":"ibrahim"}},   +
    {"grandchild_obj":{"id":4,"parent":3,"name":"jenny"}}],"id":3,"parent":3,"name":"francoise"}
   {"grandchild_arr":[{"grandchild_obj":{"id":2,"parent":2,"name":"henrietta"}}],"id":2,"parent":1,"name":"ebeneezer"}
  (3 rows)


Create JSON Array (Again)
-------------------------

As before, group by "parent".

    with json_grandchild
      as (select row_to_json(grandchild) as grandchild_obj
            from grandchild),
         json_grandchild_arr
      as (select json_agg(json_grandchild) as grandchild_arr
            from json_grandchild
           group by (grandchild_obj->>'parent')::int),
         child_grandchild_join
      as (select *
            from json_grandchild_arr as g
           inner join child as c
              on c.id = (g.grandchild_arr->0->'grandchild_obj'->>'parent')::int),
         json_child
      as (select row_to_json(child_grandchild_join) as child_obj
            from child_grandchild_join),
         json_child_arr
      as (select json_agg(json_child) as child_arr
            from json_child
           group by (child_obj->>'parent')::int)
  select *
    from json_child_arr;

                                                                child_arr                                                              
  -------------------------------------------------------------------------------------------------------------------------------------
   [{"child_obj":{"grandchild_arr":[{"grandchild_obj":{"id":1,"parent":1,"name":"george"}}],"id":1,"parent":1,"name":"delilah"}},   +
    {"child_obj":{"grandchild_arr":[{"grandchild_obj":{"id":2,"parent":2,"name":"henrietta"}}],"id":2,"parent":1,"name":"ebeneezer"}}]
   [{"child_obj":{"grandchild_arr":[{"grandchild_obj":{"id":3,"parent":3,"name":"ibrahim"}},   +
    {"grandchild_obj":{"id":4,"parent":3,"name":"jenny"}}],"id":3,"parent":3,"name":"francoise"}}]
  (2 rows)


Join With Parent
----------------

As before.  And we'll create the final object too.  And we'll wrap it all in a
list.


    with json_grandchild
      as (select row_to_json(grandchild) as grandchild_obj
            from grandchild),
         json_grandchild_arr
      as (select json_agg(json_grandchild) as grandchild_arr
            from json_grandchild
           group by (grandchild_obj->>'parent')::int),
         child_grandchild_join
      as (select *
            from json_grandchild_arr as g
           inner join child as c
              on c.id = (g.grandchild_arr->0->'grandchild_obj'->>'parent')::int),
         json_child
      as (select row_to_json(child_grandchild_join) as child_obj
            from child_grandchild_join),
         json_child_arr
      as (select json_agg(json_child) as child_arr
            from json_child
           group by (child_obj->>'parent')::int),
         parent_child_join
      as (select *
            from json_child_arr as c
           inner join parent as p
              on p.id = (c.child_arr->0->'child_obj'->>'parent')::int),
         json_parent
      as (select row_to_json(parent_child_join) as parent_obj
            from parent_child_join),
         json_parent_arr
      as (select json_agg(json_parent) as parent_arr
            from json_parent)
  select *
    from json_parent_arr;

                                                                             parent_arr                                                                           
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------
   [{"parent_obj":{"child_arr":[{"child_obj":{"grandchild_arr":[{"grandchild_obj":{"id":1,"parent":1,"name":"george"}}],"id":1,"parent":1,"name":"delilah"}}, +
    {"child_obj":{"grandchild_arr":[{"grandchild_obj":{"id":2,"parent":2,"name":"henrietta"}}],"id":2,"parent":1,"name":"ebeneezer"}}],"id":1,"name":"albert"}},  +
    {"parent_obj":{"child_arr":[{"child_obj":{"grandchild_arr":[{"grandchild_obj":{"id":3,"parent":3,"name":"ibrahim"}},  +
    {"grandchild_obj":{"id":4,"parent":3,"name":"jenny"}}],"id":3,"parent":3,"name":"francoise"}}],"id":3,"name":"claude"}}]
  (1 row)

And that's it!  It's ugly as sin, and it contains way too much structure, but
it's a nested JSON object that contains all the data that was in our database.


Conclusions
-----------

I've shown how a few simple JSON-specific steps (creating objects and arrays)
can be combined with normal SQL operations like joins to create nested JSON
structures.

We can also "dive into" those structures to select values, extract the values
as strings, and then cast them to the correct type.

Finally, we can piece together multiple steps using "with" queries.  That
allows us to construct arbitrarily complex objects as a series of simple
steps.


Next post I'll shows how to make the output nicer - it's basically one
additional trick.

Andrew

Permalink

Postgres in Docker

From: andrew cooke <andrew@...>

Date: Sat, 24 Jan 2015 09:05:55 -0300

I do nearly all my development these days in Virtual Machines (one per
project, using VirtualBox with the display semalessly integrated into my
desktop).

But sometimes a VM seems like a lot of trouble.  For example (see next post)
I was recently asked to look at how we can generate JSON from Postgres data.
It seems like overkill to install a VM for that, but at the same time I don't
want to use my main machine (I don't think it has the very latest Postgres
version and I want to be able to restart / delete / reset without worrying
what else I might be touching).

So the obvious answer is to use docker.  I installed it from Yast (OpenSuse),
but the general install instructions are here -
https://docs.docker.com/installation/#installation (note that even if you
install from a package manager you may need to (1) start the service and (2)
add your user to the appropriate group).

Once installed, these commands are useful:

  # to download the latest docker image
  docker pull postgres

The availabe images can be seen at https://registry.hub.docker.com/_/postgres/

  # to start the image
  docker run --name pg -d postgres

  # to connect to the running postgres with psql (interactive)
  docker run -it --link pg:postgres --rm postgres \
  sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

  # to see what is running
  docker ps

  # to stop the running postgres
  docker kill pg

  # to see what is stopped
  docker ps -a

  # to remove the killed postgres
  docker rm pg
  # sometimes you also need docker rm XXX where XXX identifies whatever was
  # displayed using the previous command

In short, this lets you create a Postgres database that can easily be deleted
and recreated, and where you can play around without worrying about the impact
on anything else.

Andrew

Permalink

Why Poor Places Are More Diverse

From: andrew cooke <andrew@...>

Date: Fri, 23 Jan 2015 05:24:13 -0300

https://www.youtube.com/watch?v=mWVATekt4ZA

Thought-provoking.  Didn't know it was true for biodiversity; am curious if
it's really true for people.

Andrew

Permalink

Smart Writing on Graceland

From: andrew cooke <andrew@...>

Date: Tue, 20 Jan 2015 21:55:27 -0300

Was listening to afrobeat and juju on shuffle and got the urge to go back to
Graceland.  Then starting searching to if anyone had said anything interesting
about the music (rather than the politics).  Found this - 
http://www.robertchristgau.com/xg/rock/simon-86.php

Andrew

PS The bass run on You Can Call Me Al is by Bakithi Kumalo and I need to dig
up more from him.  http://en.wikipedia.org/wiki/Bakithi_Kumalo

Permalink

Free Speech in France

From: andrew cooke <andrew@...>

Date: Wed, 14 Jan 2015 19:24:34 -0300

http://www.thejournal.ie/dieudonne-arrest-1880765-Jan2015/

Andrew

Permalink

MTB Cornering - Where Should We Point Our Thrusters?

From: andrew cooke <andrew@...>

Date: Sat, 10 Jan 2015 11:10:37 -0300

[This is an attempt to clarify and extend some ideas that started with the
post http://www.reddit.com/r/MTB/comments/2rqn6r/physicists_leaning_bikes/
and which I deleted after it descended into name-calling.]


In Mastering Mountain Bike Skills (MMBS - a respected book on how to ride MTB)
riders are encouraged to "over-lean" the bike (ie lean it more than the body):

  This position gives you extra traction and stability in flat and off-camber
  corners, or any time your lean exceeds the angle of the ground. Weight your
  outside pedal. That levers your tires into the ground, so slipping doesn't
  happen. In almost every turn, this is the best, safest, and fastest way to
  go. Practice cornering this way. It'll never steer you wrong. This is the
  best cornering position for any turn whose traction might let you down.


Now one reason I think MMBS is a good book is because I can often (with some
work) understand the text in physical terms (ie using fairly simple,
first-year university dynamics).  And that has helped my riding, because it
sheds more light on exactly what was meant.  For example, doing a manual (a
"wheelie without pedalling") is intimately connected with angular momentum
about the axis perpendicular to the bike.

Interestingly, I've found that advice in the book that is initially
counter-intuitive makes more sense once you consider "dynamics".  By that, I
mean that it's not the physics of the equilibrium solution that is important,
but the addition of accelerations, typically from body movement, over short
time periods.  Indeed, much of "good MTB riding" is about moving your body
relative to the bike, and timing that movement to get a particular effect when
most important.  These accelerations are limited - you cannot continue to
"push down" on the bike once your arms and legs are fully extended, for
example.


Given the above, I started to wonder about the physics involved in cornering:
why is it better to lean the bike more?

The equilibrium solution has the centre of mass of the bike plus rider offset
relative to the contact position between bike and trail so that the vertical
force matches gravity and the horizontal force accelerates the rider towards
the centre of the curve.  In other words, the angle of the lean for the centre
of mass of the entire system is fixed by the mass of rider+bike, the speed,
and the radius of curvature of the turn.

In other words, from that viewpoint, leaning the bike relative to the body
doesn't seem to do much except change the angle with which the tyre meets the
ground (see later).


I'm not alone in noticing this.  Here is a nice image and related quote
http://advrider.com/forums/showpost.php?p=8190069&postcount=20 which explains
things nicely - the roll angle for the combined centre of mass does not
change.  I'll copy the text here:

  Cocco in "Motorcycle Design and Technology" basically says that staying
  inline with the bike vs. hanging off vs. pushing the bike down make no
  difference in the roll angle of the combined bike/rider CG with respect to
  the ground ([phi] in the illustration below) and assuming you aren't
  dragging parts and assuming the tire offers the same traction, no technique
  has an explicit speed advantage. So it's really just a rider preference of
  whether they want to motorcycle itself to be less leaned or more leaned
  (Δ[phi]) for any given speed which affects what parts drag and what profile
  of the tire is presented to the pavement. He views it as a rider style
  preference thing, not a a physics thing.


At this point I should acknowledge that the angle between tyre and ground is
important in its own right.  In fact, MMBS describes this on page 31 where it
says:

  A square tire requires more commitment in the corners: you really have to
  lean it onto the side knobs; otherwise the tire feels sketchy.

Which is consistent with the above, but makes the argument depend on the tyre
profile, while the quote I started with seems to be more general.

Perhaps I am misreading things.  Perhaps the instructions should be more like
"adjust the lean so that your tyre bites well; this may require more or less
lean than an 'inline' riding position"?

Anyway, for the sake of curiosity I will assume that there is some further
reason, which makes the "over-lean" a more general rule, and try to find out
what it might be.


Let's take a moment to summarise where we are and where we are going.  The
equilibrium solution for a cornering bicycle suggests that relative lean
(between rider and bike) is unimportant (except for tyre profile).  So if
there is an explanation we need to look at "dynamics" for an answer.  What
(relatively sudden) changes can happen, and how are they related to lean?

Below I discuss a couple of ideas.


1 - Rotation on slipping

This is my best attempt at understanding
http://mspaintbike.blogspot.com/2013/10/why-you-cannot-corner-mountain-bike.html
which I find rather vague.  The argument there seems to be that if the wheels
start to slide then the position of the rider alters how the bike rotates (it
moves upright rather than falling towards the inside of the curve).

Consider the following diagram (bike and rider viewed head-on) showing the
centre of mass (rider and bike) M, the contact between tyre and ground T, the
vertical force Y that counteracts gravity, and the horizontal force X that
accelerates the bike and rider towards the centre of the curve.

          Y ^  M
            | /
            |/
            T----->
                  X

(This is the same as http://www.whizmoandgizmo.com/Misc/MCLeaning.jpg but I'm
considering only the joint centre of mass and being more careful about
directions to avoid any discussion about whether "centrifugal" is "real" or
not.)

The vector sum of X and Y must pass from T to M (or the entire system would
rotate).  This is the argument I made above about the angle being fixed by
mass, speed, and radius of curvature.

Now, if X suddenly disappears (or decreases), what happens?  The resulting
force becomes more vertical (Y dominates X), implying a rotation about the
centre of mass that causes bike and rider to fall into the turn.  This is the
opposite of what is argued in the link above, and is independent of the
relative position of bike and rider.


2 - Space to react

I've already noted that good MTB riding involves accelerating the body
relative to the bike at critical moments.  This is like a spaceship firing
thrusters for control, but instead of thrusters we have to use our bodies.
For example, if you want to push the bike down for more grip, you push your
body upwards (and the "equal and opposite" force on the bike is downwards).

If we look at cornering from this point of view, we can ask "how should we
position the body relative to the bike so that it is ready to move in the
direction we need?".  In other words: "where should we point our thrusters?"

This is important because, as I already noted, our movement is restricted.  We
can only move our body a limited amount, so these temporary adjustments are of
a limited duration.  For example, this explains why the attack position is low
- so that we have space to move the body upwards - but not too low, because we
also need space to "suck up" bumps by moving the body down (relative to the
bike).

OK, so how would we want to move our body?  And does that explain how we
should lean the bike?

Let's assume that the tyre slips.  Momentarily we are faced with:

          Y ^  M
            | /
            |/
            T
             
which is going to be ugly.  In terms of thrusters, what we would like is
something like this:

            |
	    | Thruster
            V 

          Y ^  M
            | /
            |/
            T

In other words - a downwards vertical force, over the contact patch.  Which we
would achieve by firing the thruster vertically upwards.

That would push the tyre down, achieve grip, hopefully, and save our sorry
ass.

Now, we don't have thrusters, we have our body.  So we want our body to be
moving upwards, over the contact patch.  Which means that we want our body "on
top" of the cycle.  Which means that we need to "over-lean" the bike...


So far I have tried to avoid any imaginary dialogue with other riders, to
avoid being called "defensive".  But at this point I cannot ignore a chorus of
"but that's what we said" and "everyone said you should push down on the
outside pedal".

So what is new here?  The important difference is that this is a sudden,
momentary correction.  It's not the "steady state".  Putting your static
weight on the pedal is not enough (is, in fact, irrelevant - no more important
to the physics than the force between bones in your knee, for example).  What
is important here is being able to give an extra push, firing your body
upwards, when grip is lost.

Now in practice there are likely multiple slips in a corner, and the
distinction between static weight and extra push is likely blurred.  Of
course.  That's why this problem was hard to understand...

Anyway, my apologies to anyone who thinks I am making a big fuss over a tiny
distinction.  I hope that mental cost is balanced by someone, somewhere, with
a background like mine, understanding this a little more.


In summary, then, I suggest that the reason it is recommended to over-lean an
MTB bike on cornering is for at least two reasons:

First, many tyres have a profile with distinct outer knobs.  Leaning the bike
helps these "dig in" and so grip better.

Second, placing the body above the bike lets you launch yourself upwards,
pushing the bike downwards, when you lose grip.  This is not a static thing -
it's to give you room to actually, physically, and perhaps violently, move
your body upwards in response to slip.

Andrew (aka science boy).

Permalink

Secure Secure Shell

From: andrew cooke <andrew@...>

Date: Tue, 6 Jan 2015 12:25:57 -0300

https://stribika.github.io/2015/01/04/secure-secure-shell.html

(How to configure ssh well)

Andrew

Permalink

Java Generics over Primitives

From: andrew cooke <andrew@...>

Date: Thu, 1 Jan 2015 10:23:57 -0300

Good exchange here:

http://mail.openjdk.java.net/pipermail/valhalla-dev/2014-December/000474.html
http://mail.openjdk.java.net/pipermail/valhalla-dev/2014-December/000475.html

(The big point - to me at least - being that this is a low-level issue related
to (avoiding) boxing.  Hence the mess.)

Andrew

Permalink

2014 (Charlie Brooker)

From: andrew cooke <andrew@...>

Date: Wed, 31 Dec 2014 17:25:51 -0300

Depressing.

https://www.youtube.com/watch?v=t3EoNsGHZD0

I'm never really sure when Adam Curtis is serious and when he's paraodying
himself, but this section is, well, interesting -
https://www.youtube.com/watch?v=t3EoNsGHZD0&feature=youtu.be&t=28m23s

Andrew

Permalink

How I am 7

From: andrew cooke <andrew@...>

Date: Sat, 27 Dec 2014 07:42:50 -0300

Sorry, these are not very interesting or public, but this is a good place to
keep a permanent record that's useful when trying to track long-term changes.

http://acooke.org/cute/HowIAm0.html
http://acooke.org/cute/HowIAm20.html
http://acooke.org/cute/HowIAm30.html
http://acooke.org/cute/HowIAm40.html
http://acooke.org/cute/HowIAm50.html
http://acooke.org/cute/HowIAm60.html

Almost 25 months from being diagnosed, 24 months on Betaferon.

Right heel tendonitis has improved, but now have issues with something similar
in left knee.  But no real connection with MS that we know of.

Otherwise, things continue as usual - about every 4-6 months there's a period
when symptoms seem to flare up, and currently I'm in one of those.

Something relatively new is muscles twitching in my face and lips, but an NMR
soon after I started feeling those showed nothing new.  It's worth noting,
perhaps, that I had similar symptoms years ago - went to La Serena hospital
wondering what was happening and was prescribed a tranquiliser (in retrospect
perhaps an early small outbreak).

A few nights recently I have woken with pins+needles in my hands, similar to
the first big outbreak.  That may be swelling from re-mylenisation - arguably
my right hand has improved slightly (very slight, very sparse pains in hand,
ache today in arm).  In response I took a little Ibuprofen and did some
exercise (cycling, despite knee).  Things seemed to improve (suggesting
inflamation?).

Other than that, some twitching in legs (lower, inner thigh - a new area) and
more sensitivity to touch in the band in the upper chest.

In short - just small details.  Lots of "noise", no clear long-term trend.

Hello to future self.  Hope you are OK.

Andrew

Permalink