#!/bin/bash

schema=__json_table
 table=bookclub 
     t=$schema.$table

sql_create_insert=$(cat << SQL_TXT
-- table 18 from ISO .pdf:
drop   table if exists $t ;
create table           $t (
    id   integer primary key
  , jcol jsonb               -- aka 'JCOL' in the .pdf
);

insert into $t (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_TXT
)

if [[ 1 -eq 1 ]]
then
  echo "$sql_create_insert" | psql -qX
# echo "select id, jsonb_pretty(jcol) from $t" | psql -qX 
# exit
fi

sql=$(cat << SQL
--
--  syntax errors at NESTED PATH aliases books, auth, and cat
--  outcomment to run without errors
--
select 'table 18', bookclub.id, jt.name, jt.title, jt.author, jt.category
from __json_table.bookclub as bookclub,
     json_table (bookclub.jcol, 'lax $'
                 columns (name varchar(30) path 'lax $."Name"',
                          nested path 'lax $.books[*]' as books                    -- error
                          columns (title varchar(60) path 'lax $.title',
                                   nested path 'lax $."authorList"[*]'      as auth -- error
                                   columns (author varchar(30) path 'lax $ '),
                                   nested path 'lax $.category[*]'          as cat  -- error
                                   columns (category varchar(30) path 'lax $ ')
                                  )
                         )
) as jt ;
SQL
)

# service HEAD is 909eebf27b9
echo "$sql" | psql -qX service=HEAD       # | md5sum

# output:

#  ?column? | id  |     name     |           title            |     author      | category
# ----------+-----+--------------+----------------------------+-----------------+----------
#  table 18 | 111 | John Smith   | The Talisman               | Stephen King    |
#  table 18 | 111 | John Smith   | The Talisman               | Peter Straub    |
#  table 18 | 111 | John Smith   | The Talisman               |                 | SciFi
#  table 18 | 111 | John Smith   | The Talisman               |                 | Novel
#  table 18 | 111 | John Smith   | Far from the Madding Crowd | Thomas Hardy    |
#  table 18 | 111 | John Smith   | Far from the Madding Crowd |                 | Novel
#  table 18 | 222 | Peter Walker | Good Omens                 | Neil Gaiman     |
#  table 18 | 222 | Peter Walker | Good Omens                 | Terry Pratchett |
#  table 18 | 222 | Peter Walker | Good Omens                 |                 | Fantasy
#  table 18 | 222 | Peter Walker | Good Omens                 |                 | Novel
#  table 18 | 222 | Peter Walker | Smoke and Mirrors          | Neil Gaiman     |
#  table 18 | 222 | Peter Walker | Smoke and Mirrors          |                 | Novel
#  table 18 | 333 | James Lee    |                            |                 |
# (13 rows)