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 /


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.