All Forums Analytics
dae 13 posts Joined 07/12
10 Jan 2016
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

 

You must sign in to leave a comment.