Re: Need help with json_table

[Tim Fathers <tim@xxxxxxxxxxxxx>]

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
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com