Extract all values from JSON

Question

I have some data that looks like this:

Data From Other Columns	JSON Data
Data 1	JSON 1
Data 2	JSON 2

The JSON data looks like this:

{
    "status": "x"
    ,"campaigns": [
        {"channel": "channel_1", "name": "sample_1"}
        ,{"channel": "channel_2", "name": "sample2"}
        ,{"channel": "channel_3", "name": "sample_3"}
    ]
 }

I need to create a query that gives me 1 row for every channel. Using JSON_QUERY() only gives me an output if the campaign has only 1 channel. In the example above, JSON_QUERY() would fail because there are 3 channels in the campaign.

SELECT
    normal_data
    ,json_array
    ,json_query(
        json_array
        ,'lax $.campaigns.channel'
     ) AS test
FROM test

However, this only gets me the instances with only ONE channel and produces an error otherwise. I'd need to get every instance of a channel and name.

My expected output is:

Normal Data From Other Columns	Channel	Name
data_1	channel_1	sample_1
data_1	channel_2	sample_2
data_1	channel_3	sample_3
data_2	channel_1	sample_1
data_2	channel_2	sample_2
data_2	channel_2	sample_3

Any help is appreciated!

Answer 1

Personally I would just use the json_extract function with some casting (to array) and unnesting. For example:

-- sample data
with dataset (json_col) as (values ('{
    "status": "x","campaigns": [
        {"channel": "channel_1", "name": "sample_1"}
        ,{"channel": "channel_2", "name": "sample2"}
        ,{"channel": "channel_3", "name": "sample_3"}
    ]
 }'))

-- query
select m['channel'] channel,
       m['name']    name
from dataset
, unnest(
    -- or array(map(varchar, json))
    cast(json_extract(json_parse(json_col), '$.campaigns') as array(map(varchar, varchar))) 
  ) as t(m);

Output:

channel	name
channel_1	sample_1
channel_2	sample2
channel_3	sample_3