Using SQL to work with JSON and how to diagnose issues

on

Problem


You have code that is reading JSON using the apex_json package.
Problem is, some values are not returned. The source may have changed, you may be querying the wrong variable names.

Solution


Move to SQL (Supported since 12.1).

So take your JSON input, save it to a table, and then use SQL to interrogate it.

Create table
create table barry_tbl_with_json (
    json_clob  clob,
   constraint json_clob_chk check (json_clob is json));

Put some data into it (the JSON data).
insert into barry_tbl_with_json (json_clob) values ('{"people":[{"site-owner":false,"twitter":"","last-name":"Winkof","useShorthandDurations":false,"userUUID":"","user-name":"Adam.Winkof@companyplc.com","id":"90194","phone-number-office":"","last-active":"","phone-number-mobile":"","user-type":"account","first-name":"Adam","im-service":"","im-handle":"","login-count":"1","openId":"","phone-number-office-ext":"","company-id":"25765","address-zip":"","has-access-to-new-projects":false,"phone-number-fax":"","last-login":"2015-07-15T19:23:16Z","companyId":"25765","address-city":"","administrator":false,"pid":"","phone-number-home":"","email-address":"Adam.Winkof@companyplc.com","tags":[],"company-name":"company PLC","last-changed-on":"2015-07-14T19:40:55Z","address-state":"","address-country":"","deleted":false,"notes":"","phone-number-mobile-parts":{"phone":"","prefix":"","countryCode":""},"permissions":{"can-manage-people":false,"can-add-projects":false},"user-invited-status":"COMPLETE","address":{"zipcode":"","countrycode":"","state":"","line1":"","country":"","line2":"","city":""},"address-line-2":"","address-line-1":"","created-at":"2015-07-14T19:40:55Z","textFormat":"HTML","user-invited-date":"2015-07-14T19:40:55Z","avatar-url":"https:\/\/companyplc1.teamwork.com\/images\/noPhoto2.png","in-owner-company":true,"user-invited":"2","email-alt-1":"","email-alt-2":"","email-alt-3":"","title":"Software Developer"},{"site-owner":false,"twitter":"","last-name":"Tomovici","useShorthandDurations":false,"userUUID":"","user-name":"Alexandru.Tomovici@companyplc.com","id":"144168","phone-number-office":"","last-active":"2016-10-03T22:26:28Z","phone-number-mobile":"","user-type":"account","first-name":"Alexandru","im-service":"","im-handle":"","login-count":"143","openId":"","phone-number-office-ext":"","company-id":"25765","address-zip":"","has-access-to-new-projects":false,"phone-number-fax":"","last-login":"2016-10-03T17:28:02Z","companyId":"25765","address-city":"","administrator":false,"pid":"","phone-number-home":"","email-address":"alexandru.tomovici@companyplc.com","tags":[],"company-name":"company PLC","last-changed-on":"2016-07-28T16:14:20Z","address-state":"","address-country":"","deleted":false,"notes":"","phone-number-mobile-parts":{"phone":"","prefix":"","countryCode":""},"permissions":{"can-manage-people":false,"can-add-projects":false},"user-invited-status":"COMPLETE","address":{"zipcode":"","countrycode":"","state":"","line1":"","country":"","line2":"","city":""},"address-line-2":"","address-line-1":"","created-at":"2016-07-18T09:44:37Z","textFormat":"HTML","user-invited-date":"2016-07-18T16:05:10Z","avatar-url":"https:\/\/s3.amazonaws.com\/TWFiles\/228676\/users\/u144168\/F6DD95BBEDC19A1290B905FCDAE007CC.jpg","in-owner-company":true,"user-invited":"2","email-alt-1":"","email-alt-2":"","email-alt-3":"","title":"Oracle Developer"}],"STATUS":"OK"}');

So this is two rather large JSON records.

This is the syntax that you would use to query the records

select jt.question, jt.last_name
from barry_tbl_with_json,
json_table(json_clob, '$.people[*]'
columns (
question varchar2 path '$.id' ,
last_name varchar2 path '$.last-name'
error on error
) 
) as jt

I get "ORA-40442: JSON path expression syntax error"
So now we know that we are using the wrong variable names.

I played around with the variables names, until I found that this worked.

select jt.question, jt.last_name
from barry_tbl_with_json,
json_table(json_clob, '$.people[*]'
columns (
question varchar2 path '$.id' ,
last_name varchar2 path '$."last-name"'
error on error
)
) as jt

(Put the variables in quotes)


Acknowledgement

http://dgielis.blogspot.co.uk/2016/08/sqlcl-to-rescue-when-database-and-apex.html
Here is me trying to work it out, with my original code and me answering my own question
https://community.oracle.com/message/14053888#14053888

Comments

Post a Comment