Hi @QueryingQuagga ,

So your problem comes from the fact, that you have nested json string as a value. That's why you get null when you're trying to do following:

SELECT extendedinformation:[0].value.name

So what you can do is to explode elements in extendedinformation array and then you can use from_json to parse string contained in value key:

"value": "[{\"id\":\"DUMMYID1\",\"name\":\"DUMMYCHANNEL1\",\"role\":\"DUMMYROLE1\"}]"

Below is a full example:

%sql

WITH src AS (
  SELECT 
    parse_json('{
        "extendedinformation": [
            {
                "name": "CHANNEL",
                "value": "[{\\"id\\":\\"DUMMYID1\\",\\"name\\":\\"DUMMYCHANNEL1\\",\\"role\\":\\"DUMMYROLE1\\"}]"
            },
            {
                "name": "CHANNEL2",
                "value": "[{\\"id\\":\\"DUMMYID2\\",\\"name\\":\\"DUMMYCHANNEL2\\",\\"role\\":\\"DUMMYROLE2\\"}]"
            }
        ]
    }') AS data
),
extended AS (
  SELECT exp.value as extendedinformation_exploded   
  FROM src,
  LATERAL variant_explode(data:extendedinformation) exp
)
SELECT *, from_json(extendedinformation_exploded:value::STRING, 'ARRAY<MAP<STRING, STRING>>')[0].name AS value_array 
FROM extended