Nested JSON Object Workaround

Nested JSON Object Workaround

book

Article ID: KB0082856

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization ALL VERSIONS

Description

Question: I am not able to parse nested JSON object and parse it into tabular format:
 
{ATTRIBUTE<<INCREMENTAL NUMBER>>:{ATTRIBUTE1:VALUE1,ATTRIBUTE2:VALUE2}, ATTRIBUTE<<INCREMENTAL NUMBER>>:{ATTRIBUTE1:VALUE1,ATTRIBUTE2:VALUE2}}
 
Sample JSON RESPONSE
 
{"0":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-188947-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"1":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-188948-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"2":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-188949-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"3":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-0832-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"4":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-0911-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"5":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-0783-01","Q3FY16":"0.01","Q4FY16":"0.01","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"6":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-101211-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"7":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-A33264-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"8":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-1016-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"9":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-197534-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"10":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-197495-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"11":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-197496-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"12":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-101146-01","Q3FY16":"0","Q4FY16":"0","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"},"13":{"SUPPLIER_NAME":"MURATA-RFQ","CPN_NAME":"11-2453-01","Q3FY16":"0.02","Q4FY16":"0.02","Q1FY17":"0","Q2FY17":"0","Q3FY17":"0"}}
 

Resolution

Split the view in parent and child view.
 
Parent View:
 
SELECT
    property,
    propValue
 
FROM
 
    JSON_TABLE(JSONFILE."output",'$.OUTPUT'COLUMNS(property VARCHAR(100) PATH key,propValue VARCHAR(200) PATH value)) JT,
/shared/Projects/DV_POC_ENV16/Physical/Metadata/SAP_HANA_REST_API/SupplierCPNQuote('MOLEX-RFQ', '') JSONFILE
 
 
Child View: -- Need to replace path of parent view
 
SELECT
   -- property,
   -- propValue
SUPPLIER_NAME,CPN_NAME,Q3FY16
FROM
 
/shared/Projects/DV_POC_ENV16/ViewParent    v1,
    JSON_TABLE(v1.PropValue,'$'COLUMNS(SUPPLIER_NAME VARCHAR(100) PATH '$.SUPPLIER_NAME',CPN_NAME VARCHAR(100) PATH '$.CPN_NAME',Q3FY16 DOUBLE PATH '$.Q3FY16')) JT2
 

Issue/Introduction

Nested JSON Object Workaround
Powered by Wolken Software