Although TD_JSONSHRED does not support extracting data directly from nested arrays in a JSON document, you can nest calls to TD_JSONSHRED to shred data from nested arrays. For example:SELECT * from TD_JSONSHRED
(ON
(SELECT * FROM TD_JSONSHRED
(ON
(SELECT ID, jsondoc FROM jsontable)
USING
ROWEXPR('employees')
COLEXPR('address')
RETURNTYPES('VARCHAR(100)')
) AS d1
)
USING
ROWEXPR('')
COLEXPR('street','city','state','zipcode')
RETURNTYPES('VARCHAR(20)','VARCHAR(20)','VARCHAR(2)','VARCHAR(10)')
) AS d2;
The inner TD_JSONSHRED creates a derived table (d1) having a column that holds the value of the JSON address object. This value is an array that was nested in the "employees" array in the original JSON document. The nested address array itself is a JSON document, so its constituent values can be shredded by the outer TD_JSONSHRED into individual columns in the derived table, d2. See Example: TD_JSONSHRED Nested Arrays.