Question 303301 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Unnest elements of each object in Trino/Presto

I have a column called users with this JSON.

{
"[email protected]":[1.0,5.95],
"[email protected]":[2.0,30.733],
"[email protected]":[1.0,4.433],
"[email protected]":[2.0,16.25]
}

I want to unwrap it in this way

splitUsers firstValue secondValue
[email protected] 1.0 5.95
[email protected] 2.0 30.733
[email protected] 1.2 4.433
[email protected] 2.0 16.25

I managed to split the emails with query below, however I can’t get the array values as in seperate columns.

select * from tableName 
CROSS JOIN UNNEST(SPLIT(array_join(map_keys(CAST(json_parse(users) AS MAP(VARCHAR, JSON))),  ', '),',')) 
AS t (splitUsers)

Is there a way to achieve what I’m looking for?

Tags:

2

Answers


  1. 0

    You seem to be very close, one of the options would be using json extract functions (for example json_extract_scalar) on the unnested value (after casting to map, note the succinct syntax for the unnest):

    -- sample data
with dataset (users) as (
    values ('{
        "[email protected]":[1.0,5.95],
        "[email protected]":[2.0,30.733],
        "[email protected]":[1.0,4.433],
        "[email protected]":[2.0,16.25]
    }')
)

-- query
select k splitUsers,
    json_extract_scalar(v, '$[0]') firstValue,
    json_extract_scalar(v, '$[1]') secondValue
from dataset
, unnest (cast(json_parse(users) as map(varchar, json))) as t(k,v)
;

    Output:

    splitUsers firstValue secondValue
    [email protected] 2.0 16.25
    [email protected] 2.0 30.733
    [email protected] 1.0 4.433
    [email protected] 1.0 5.95
    Login or Signup to reply.
  2. 0

    You need to first unnest the map into separate rows, then extract out of the JSON array

    SELECT
  tn.*,
  t.splitUsers,
  json_extract_scalar(t.vals, '$[0]') AS firstValue,
  json_extract_scalar(t.vals, '$[1]') AS secondValue
from tableName AS tn
CROSS JOIN UNNEST(CAST(json_parse(users) AS MAP(VARCHAR, JSON))) AS t (splitUsers, vals);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search