Re: Need help with json_table

[Peter Dow <petercdow@xxxxxxxxx>]

I was a little too hasty.  It doesn't like the "path '$'" in the column 
definition.  If you leave that off it defaults to $. plus the column 
name, which works.  Or you can specify path '$.app'.

Also, the link I provided previously was to the DB2 11.5 manual. For IBM 
i the link is https://www.ibm.com/docs/en/i/7.3?topic=functions-json-table.



That works as long as you do not have COMMIT(*NONE) on your connection.  
Otherwise you get SQLSTATE 42926 "Message [SQL0433] LOB and XML locators 
are not allowed with COMMIT(*NONE)".

On 11/4/2021 1:31 PM, Tim Fathers wrote:
> Exactly. I think this ought to work, but I'm not in front of a machine to check it.
>
> insert into vtxtaxid (taxareaid)
> select t.*
> from json_table (
>          get_clob_from_file('/vertexCloud/output/xl_0000401.json', 1),
>          '$.meta' columns (
>              app char(50) path '$'
>          )
>      ) as t
>
>
> ________________________________
> From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx>  on behalf of Peter Dow<petercdow@xxxxxxxxx>
> Sent: 04 November 2021 19:40
> To:midrange-l@xxxxxxxxxxxxxxxxxx  <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: Need help with json_table
>
> I never used json_table before, so I had to look it up. According to the
> manual, "json_expression" is not a file name, it's the actual json.  The
> example in the manual
> (https://www.ibm.com/docs/en/db2/11.5?topic=functions-json-table) gets
> the json_expression from a column in a table:
>
> SELECT U."id", U."first name", U."last name", U."phone number" FROM
> EMPLOYEE_TABLE E JSON_TABLE(E.jsondoc, 'strict $' COLUMNS( "id" INTEGER,
> "firstname" VARCHAR(20), "lastname" VARCHAR(20), "phoneno" VARCHAR(20))
> ERROR ON ERROR) AS U Also, the "path" goes with the column, not in the
> "strict $". The following works on a v7r3 box:
>
> select t.*
>      from json_table (
> '
>    {
>     "meta": {
>       "app": "Vertex REST API v0.1.0",
>       "timeReceived": "2021-06-22T14:08:07.838Z",
>       "timeElapsed(ms)": 17
>     },
>     "data": {
>       "lookupResults": [
>         {"jurisdictions":
>           [
>             {"jurisdictionLevel": "COUNTRY"
>             },
>             {"jurisdictionLevel": "STATE"
>             },
>             {"jurisdictionLevel": "COUNTY"
>               }
>           ],
>           "postalAddresses":
>           [
>             {"streetAddress1": "2301 Renaissance Blvd Ste 7"
>             }
>           ],
>           "statuses":
>            [
>             {"lookupResult": "NORMAL"
>             }
>            ],        "taxAreaId": 390910000,
>           "asOfDate": "2021-06-18",
>           "confidenceIndicator": 100
>         }
>       ]
>     }
> }'
> , 'strict $'
>                       COLUMNS ("app" varchar(50) path '$.meta.app')
>                       ERROR ON ERROR) as t;
>
> --
> *Peter Dow* /
> Dow Software Services, Inc.
> 909 793-9050
> petercdow@xxxxxxxxx
> pdow@xxxxxxxxxxxxxx  /
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:https://lists.midrange.com/mailman/listinfo/midrange-l
> or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> athttps://archive.midrange.com/midrange-l.
>
> Please contactsupport@xxxxxxxxxxxxxxxxxxxx  for any subscription related questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate link:https://amazon.midrange.com