#!/bin/bash

psql -X << SQL
drop   table if exists bookclub ;
create table           bookclub (
    id   integer primary key
  , jcol jsonb               -- aka 'JCOL'
);

insert into bookclub (id, jcol) values 
(111, '
{
  "Name" : "John Smith",
  "address" : {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state" : "NY",
    "postalCode" : 10021
  },
  "phoneNumber" : [
    { "type" : "home", "number" : "212 555-1234" },
    { "type" : "fax", "number" : "646 555-4567" }
  ],
  "books" : [ { "title" : "The Talisman",
      "authorList": [ "Stephen King", "Peter Straub" ],
      "category": [ "SciFi", "Novel" ]
    },
    {
      "title" : "Far from the Madding Crowd",
      "authorList" : [ "Thomas Hardy" ],
      "category" : [ "Novel" ]
    }
  ]
}
'),

(222,
'{
  "Name" : "Peter Walker",
  "address" : { "streetAddress": "111 Main Street",
                "city": "San Jose",
                "state" : "CA",
                "postalCode" : 95111 },
  "phoneNumber" : [ { "type" : "home"  , "number" : "408 555-9876" },
                    { "type" : "office", "number" : "650 555-2468" } ],
  "books" : [ { "title":"Good Omens",
                "authorList" : [ "Neil Gaiman", "Terry Pratchett" ],
                "category" : [ "Fantasy", "Novel" ] },
              { "title" : "Smoke and Mirrors",
                "authorList" : [ "Neil Gaiman" ],
                "category" : ["Novel"] } ]
}
'::jsonb),

(333,
'
{
  "Name" : "James Lee"
}
')
SQL

psql -qX -a << SQL
-- table 15 in   TECHNICAL REPORT ISO/IEC TR 19075-6   First edition 2017-03
select jt.rowseq, jt.name, jt.zip
from bookclub
, json_table (bookclub.jcol, 'lax \$ '
              columns ("rowseq" for ordinality
                     , "name"   varchar(30)  path 'lax \$."Name"'
                     , "zip"    char(5)      path 'lax \$."address"."postalCode"'
                     )
  ) as jt
;
SQL
 

#-- TECHNICAL REPORT ISO/IEC TR 19075-6   First edition 2017-03
#
#-- The result of the query is shown in Table 15, “Query result”.
#-- Table 15 — Query result
#-- ROWSEQ  NAME           ZIP
#--  1      John Smith    10021
#--  2      Peter Walker  95111
#--  3      James Lee