CODEWITHSUNDEEP
sqljsonprestotrino
Miguel Barrientos
Miguel Barrientos

Reputation: 49

Extract all values from JSON

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!

Upvotes: 0

Views: 487

Answers (1)

Guru Stron
Guru Stron

Reputation: 141755

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

Upvotes: 0

Related Questions