Select particular values from JSON column in Presto

Question

I have a JSON column points of type VARCHAR in a table which I want to parse in Presto. For example:

points = {"0": 0.2, "1": 1.2, "2": 0.5, "15": 1.2, "20": 0.7}

I want to select only the values for keys "0", "2" and "20". How do I use the UNNEST functionality of Presto to get them. What I've done till now is:

select t.value from myTable CROSS JOIN UNNEST(points) AS t(key, value) limit 1

But this gives this error:

Cannot unnest type: varchar

Update:

I ran the following query and got the result but it is returning one random key-value pair from the JSON whereas I need specific keys.

select key, value from myTable CROSS JOIN UNNEST(SPLIT_TO_MAP(points, ',', ':')) AS t(key, value) limit 1

score 1 · Answer 1 · answered Aug 05 '17 at 06:20

You can unnest an Array or Map. So you first need to convert the JSON string into a MAP:

CAST(json_parse(str) AS MAP<BIGINT, DOUBLE>)

Here is an example:

presto> select tt.value
     -> from (VALUES '{"0": 0.2, "1": 1.2, "2": 0.5, "15": 1.2, "20": 0.7}') as t(json)
     -> CROSS JOIN UNNEST(CAST(json_parse(json) AS MAP<BIGINT, DOUBLE>)) AS tt(key, value)
     -> ;
 value
-------
   0.2
   1.2
   1.2
   0.5
   0.7
(5 rows)

kjones · Answer 2 · 2017-06-27T01:08:45.333

0

You may need to cast to json datatype first according to these docs: enter link description here

UNNEST(CAST(points AS JSON))

Full query:

select t.value from myTable
CROSS JOIN UNNEST(CAST(points AS JSON)) AS t(key, value) limit 1

edited Jun 27 '17 at 01:08

answered Jun 27 '17 at 00:58

kjones

1,339
1
13
28

What should be the full query? – Hellboy Jun 27 '17 at 01:07
I added it to the answer, let me know how it goes – kjones Jun 27 '17 at 01:09
I tried this but this also didn't work: select t.value from myTable CROSS JOIN UNNEST(CAST(JSON CAST(points AS JSON) AS MAP(INTEGER, REAL))) AS t(key, value) limit 1 – Hellboy Jun 27 '17 at 01:16
ahh, `unnest` is expecting an array, see if you can cast your json payload to an array – kjones Jun 27 '17 at 01:18
Maybe: `select t.value from myTable CROSS JOIN UNNEST(CAST(CAST(points AS JSON) AS ARRAY) AS t(key, value) limit 1` – kjones Jun 27 '17 at 01:21
See these two answers: https://stackoverflow.com/questions/34496811/presto-unnest-json and https://stackoverflow.com/questions/29932950/how-to-cross-join-unnest-a-json-array-in-presto – kjones Jun 27 '17 at 01:24

Select particular values from JSON column in Presto

2 Answers2