How can I query JSON data with a nested, nested array using JSON_TABLE in SQL?

Question

enter code hereI am kind of new to JSON and trying to query a complex JSON data structure using JSON_TABLE. Here is the data and a the query I came up with. The issue is that phonenumber is an array with in an array and not displaying the results. Any help would be appreciated.

Sample JSON Data

{ "instId": 1, "instappId": 1000, "instappNumber": "Z1009", "appDate": "2020-09-19T09:29:04.633", "contactInfo": { "instId": 1, "instappId":1000, "firstName": "Dan", "middleName": null, "lastName": "Wood", "emailAddress": "[email protected]", "phoneNumbers": [ { "instId": 1, "phoneID: 2001, "phoneType": "Home Phone", "phoneNumber": "1234567890", } } ] "workHistory": [ { "instId": 1,
"companyiterationID: 1, "companyName": "ABC Inc, "startDate": "2019-01-11, "EndDate": null,

            }
        ]

}

Query

SELECT A.* FROM mytable b, JSON_TABLE (b.json_data, '$.data[]' COLUMNS ( instId NUMBER(5) PATH '$.instId', instappId number(15) PATH '$.instappId', instappNumber VARCHAR2(30) PATH '$.instappNumber', appDate DATE PATH '$.appDate', NESTED PATH '$.contactInfo[]' COLUMNS ( firstName VARCHAR2(50) PATH '$.firstName', middleName VARCHAR2(50) PATH '$.middleName', lastName VARCHAR2(50) PATH '$.lastName', emailAddress VARCHAR2(50) PATH '$.emailAddress', NESTED PATH '$.phoneNumbers[]' COLUMNS ( phoneNumberId NUMBER(15) PATH '$.phoneNumberId', phoneType VARCHAR2(50) PATH '$.phoneType', phoneNumber VARCHAR2(50) PATH '$.phoneNumber')), NESTED PATH '$.workHistory[]' COLUMNS ( companyName VARCHAR2(50) PATH '$.companyName', startDate DATE PATH '$.startDate' endDate DATE PATH '$.endDate') ) ) A;

Can you verify if your JSON is valid? - I think it's not. Also, this is Oracle, right? if so, please edit and add the appropiated tags — Mauricio Arias Olave
– Mauricio Arias Olave, Commented May 26, 2023 at 17:27

Mauricio Arias Olave · Accepted Answer · 2023-05-26 18:03:52Z

I checked your code and definitely, your JSON is not valid.

Check closely your JSON - check first how you're bulding this object or how you're receiving it - because it's not valid.

This is the code I was able to build:

SELECT *
FROM JSON_TABLE('{
    "instId": 1,
    "instappId": 1000,
    "instappNumber": "Z1009",
    "appDate": "2020-09-19T09:29:04.633",
    "contactInfo": {
        "instId": 1,
        "instappId": 1000,
        "firstName": "Dan",
        "middleName": null,
        "lastName": "Wood",
        "emailAddress": "[email protected]",
        "phoneNumbers": [{
                "instId": 1,
                "phoneID": 2001,
                "phoneType": "Home Phone",
                "phoneNumber": "1234567890"
            }]
        },
    "workHistory": [{
        "instId": 1,
        "companyiterationID": 1,
        "companyName": "ABC Inc",
        "startDate": "2019-01-11",
        "EndDate": null
      }]
}', '$[*]' COLUMNS(instId NUMBER(5) PATH '$.instId',
           instappId number(15) PATH '$.instappId',
           instappNumber VARCHAR2(30) PATH '$.instappNumber',
           appDate DATE PATH '$.appDate',
           firstName VARCHAR2(100) PATH '$.contactInfo.firstName', 
           middleName VARCHAR2(50) PATH '$.middleName',
           lastName VARCHAR2(50) PATH '$.lastName',
           emailAddress VARCHAR2(50) PATH '$.emailAddress',
  NESTED PATH  '$.phoneNumbers[*]' COLUMNS (
             phoneNumberId NUMBER(15) PATH '$.phoneNumberId',
             phoneType VARCHAR2(50) PATH '$.phoneType',
             phoneNumber VARCHAR2(50) PATH '$.phoneNumber'),
  NESTED PATH  '$.workHistory[*]' COLUMNS (
            companyName VARCHAR2(50) PATH '$.companyName',
            startDate DATE PATH '$.startDate',
            endDate DATE PATH '$.EndDate'))
  );

INSTID	INSTAPPID	INSTAPPNUMBER	APPDATE	FIRSTNAME	MIDDLENAME	LASTNAME	EMAILADDRESS	PHONENUMBERID	PHONETYPE	PHONENUMBER	COMPANYNAME	STARTDATE	ENDDATE
1	1000	Z1009	19-SEP-20	Dan	null	null	null	null	null	null	ABC Inc	11-JAN-19	null

db<>fiddle demo

Thanks for your time and response. I will check the JSON structure as it is from a legitimate API which we are using to download the file. I am quite sure that there won't be any issue with the structure as it is used by many applications.

Collectives™ on Stack Overflow

How can I query JSON data with a nested, nested array using JSON_TABLE in SQL?

Sample JSON Data

Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample JSON Data

Query

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related