CODEWITHSUNDEEP
sqlarraysjsonprestotrino
ali60vip
ali60vip

Reputation: 430

Extract values from a JSON Array in Presto

I have a column with JSON arrays like below:

{data=[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]}

I'm trying to parse the array and extract specific values based on conditions. For example

value of "min" where "name"="col1": 0

value of "avg" where "name"="col3": 34

Does anyone have a solution for that?

Upvotes: 6

Views: 9466

Answers (3)

Aakash Parsi
Aakash Parsi

Reputation: 103

I tried using the solution mentioned by @mingdinghan as it was the exact solution I was looking for. However, I ended up getting errors.

I made changes to the above code & was successful in getting the solution. Below is the solution if anyone is facing issue with the above code.

with mydata as (
select '[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]' jsonobj
)
select 
    max(case when a.name = 'col1' then a.min end) min_col1,
    max(case when a.name = 'col3' then a.avg end) avg_col3
from mydata
CROSS JOIN
    UNNEST(
            CAST(json_parse(jsonobj) as array(ROW(name VARCHAR, min INTEGER, max INTEGER, avg INTEGER))
                 )
          ) as x(a)

Result is still the same as above.

Upvotes: 1

mingdinghan
mingdinghan

Reputation: 11

Following up on the above question from @siraj-alam, and extending on the answer by @leftjoin, if the data is a JSON array, i.e.

with mydata as (
select '[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]' json
)

This query gives the same answer

select max(case when x.name = 'col1' then x.min end) min_col1,
       max(case when x.name = 'col3' then x.avg end) avg_col3
from mydata
CROSS JOIN
    UNNEST(
            CAST(
                JSON_EXTRACT(json,'$')
                    as ARRAY(ROW(name VARCHAR, min INTEGER, max INTEGER, avg INTEGER))
                 )
          ) as x(name, min, max, avg) --column aliases

min_col1    avg_col3
0           34

Upvotes: 1

leftjoin
leftjoin

Reputation: 38325

Your JSON is not valid one. it should be {"data":[ not {data = [

If JSON is valid ( you can easily fix it in a subquery ), extract data, cast it to array(row) and get values using CASE expressions. I added max() aggregation here to remove NULL records and get all required values in single row, you can use filter instead (for example where x.name = 'col1'), depending on what you need:

with mydata as (
select '{"data":[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]}' json
)

select max(case when x.name = 'col1' then x.min end) min_col1,
       max(case when x.name = 'col3' then x.avg end) avg_col3
from mydata
CROSS JOIN
    UNNEST(
            CAST(
                JSON_EXTRACT(json,'$.data')
                    as ARRAY(ROW(name VARCHAR, min INTEGER, max INTEGER, avg INTEGER))
                 )
          ) as x(name, min, max, avg) --column aliases

Result:

min_col1    avg_col3
0           34

Upvotes: 5

Related Questions