Geek Answers Handbook

How to extract keys in nested json array object in Presto?

I am using the last (0.117) Presto and trying to execute CROSS JOIN UNNEST with a complex JSON array like this.

[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}, ...]

To do this, first I tried to do ARRAY with id values ​​on

 SELECT CAST(JSON_EXTRACT('[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}]', '$..id') AS ARRAY<BIGINT>)

but that will not work.

What is the best JSON path to extract id values?

+5
source share
3 answers

You can include JSON in ARRAY OF MAP and use the transform lambda function to retrieve the id key:

 select TRANSFORM(CAST(JSON_PARSE(arr1) AS ARRAY<MAP<VARCHAR, VARCHAR>>), entry->entry['id']) from (values ('[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}]')) t(arr1)

output:

  [1, 2]
+3
source

Now you can use presto-third-functions , it provides the json_array_extract function, you can extract information about the json array as follows:

  select json_array_extract_scalar(arr1, '$.book.id') from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1)

:

  [12, 14]
+4
source

I finally refused to search for a simple JSON path to extract them.

Instead, I wrote a redundant dirty query like the one below to do the task.

 SELECT ... FROM ( SELECT SLICE(ARRAY[ JSON_EXTRACT(json_column, '$[0].id'), JSON_EXTRACT(json_column, '$[1].id'), JSON_EXTRACT(json_column, '$[2].id'), ... ], JSON_ARRAY_LENGTH(json_column)) ids FROM the.table ) t1 CROSS JOIN UNNEST(ids) AS t2(id) WHERE ...

I still want to know the best practice if you know another good CROSS JOIN way!

+1
source

More articles:


All Articles