Extract JSON data from column using Presto

Question

I am using Presto and have a column_A with complex JSON data where each cell has an array that looks like this:

[{"value":"aaa", "items":["246","123"],...}, {"value":"bbb", "items":["357","123"],...}]

What I am trying to do is extract from each cell only the JSON object where "items" has code "357".

So ideally the output of the query would be new data in Column_B that has one JSON object (not an array) and looks like this:

{"value":"bbb", "items":["357","123"],...}

Note: for each cell, there is only one object with the code "357", however, "item" may have more than 1 code inside its array. As in the examples above.

So far I tried the below, but it doesnt seem to be working:

SELECT JSON_EXTRACT(column_a, '$.(SELECT * from column_a where column_a::text LIKE '%357%')') AS column_extracted
FROM column_a

Does anyone have an idea of what I could try instead?

Martin Traverso · Accepted Answer · 2022-06-15 15:36:30Z

1

You can use the newly introduced JSON path functions in Trino (formerly known as Presto SQL) for this:

WITH t(data) AS (
    VALUES '[{"value":"aaa", "items":["246","123"]}, {"value":"bbb", "items":["357","123"]}]'
)

SELECT json_query(t.data, 'strict $[*]?(@.items[*] == "357")')
FROM t

which produces:

                 _col0
---------------------------------------
 {"value":"bbb","items":["357","123"]}
(1 row)

If you look at $[*]?(@.items[*] == "357"), it says "find all the elements of the top-level array that match the condition @.items[*] == "357"", which, in turn, checks whether any of the elements in the items array has a value of 357.

answered Jun 15, 2022 at 15:36

Martin Traverso

5,40619 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract JSON data from column using Presto

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related