Unnest elements of each object in Trino/Presto

Question

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?

Answer 1

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

Answer 2

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); 

Answer 3

Here's a version that builds on previous answers but avoids having to use json_extract_scalar.

It relies on the fact that in Trino you can cast a JSON object to a SQL map, and a JSON array to a SQL row: CAST(json_parse(users) AS map(varchar, row(double, double)))

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]
    }')
)

SELECT
    k AS splitUsers,
    v[1] AS firstValue,
    v[2] AS secondValue
FROM 
    dataset, 
    UNNEST(cast(json_parse(users) as map(varchar, row(double, double)))) as t(k,v)

In the latest version of Trino, you can use JSON_TABLE to process the data directly and extract the fields without having to call json_parse or convert the JSON values to a SQL type:

WITH dataset(users) AS ( ... )
SELECT t.*
FROM dataset, JSON_TABLE(users, 'strict $.keyvalue()' COLUMNS (
    splitUsers VARCHAR PATH 'strict $.name',
    firstValue DOUBLE PATH 'strict $.value[0]',
    secondValue DOUBLE PATH 'strict $.value[1]'
)) AS t