JSON Shredding

Analytics
dae
N/A

JSON Shredding

Hi,

I am trying to shred a JSON field with "nested array" but I am not able to perform that operation (below is the source code with the result I would like to get and the results I got).

Any kind of help would be greatly appreciated,

Thanks a lot, Didier.

CREATE SET TABLE   CDH_STG.ITEM_JSON_DAE
(
ITEM_OID BYTEINT NOT NULL
, ITEM_JSON JSON(8388096) CHARACTER SET UNICODE NOT NULL
)
;

INSERT INTO CDH_STG.ITEM_JSON_DAE
(
ITEM_OID
, ITEM_JSON
)
SELECT CAST(1 AS BYTEINT)
, NEW JSON ('{"RangeCheck":[{"checkValue":[{"tvalue":"0.0"}, {"tvalue":"1.0"}],"comparator":"GE"},{"checkValue":[{"tvalue":"5.0"}],"comparator":"LE"}]}')
;

-- THE RESULT I WOULD LIKE TO GET:
-------------------------------------

ITEM_OID TVALUE COMPARATOR
--------- ---------- ----------
1 0.0 GE
1 1.0 GE
1 5.0 LE

-- SHREDDING AT THE DEEPEST LEVEL:
-------------------------------------

SELECT T.ITEM_OID
, T.TVALUE
FROM JSON_TABLE
(
ON (

SELECT ITEM_JSON_DAE.ITEM_OID
, ITEM_JSON_DAE.ITEM_JSON
FROM CDH_STG.ITEM_JSON_DAE ITEM_JSON_DAE
WHERE 1 = 1

)

USING ROWEXPR('$.RangeCheck[*].checkValue[*]')
COLEXPR(
'[
{"jsonpath":"$.tvalue","type":"DECIMAL(18,0)"}
]'
)
) AS T (
ITEM_OID
, TVALUE
)
;

ITEM_OID TVALUE
--------- ----------
1 0.0
1 1.0
1 5.0

-- SHREDDING AT THE HIGHEST LEVEL:
-------------------------------------

SELECT T.ITEM_OID
, T.COMPARATOR
FROM JSON_TABLE
(
ON (

SELECT ITEM_JSON_DAE.ITEM_OID
, ITEM_JSON_DAE.ITEM_JSON
FROM CDH_STG.ITEM_JSON_DAE ITEM_JSON_DAE
WHERE 1 = 1

)

USING ROWEXPR('$.RangeCheck[*]')
COLEXPR(
'[
{"jsonpath":"$.comparator","type":"CHAR(10)"}
]'
)
) AS T (
ITEM_OID
, COMPARATOR
)
;

ITEM_OID COMPARATOR
--------- ----------
1 GE
1 LE