How to loop through array with JSON objects, to find item that meets condition

Question

Breaking my head on this. In Snowflake my field city_info looks like (for 3 sample records)

[{"name": "age", "content": 35}, {"name": "city", "content": "Chicago"}]
[{"name": "age", "content": 20}, {"name": "city", "content": "Boston"}]
[{"name": "city", "content": "New York"}, {"name": "age", "content": 42}]

I try to extract a column city from this

Chicago
Boston
New York

I tried to flatten this

select *
from lateral flatten(input =>
  select city_info::VARIANT as event
  from data
)

And from there I can derive the value, but this only allows me to do this for 1 row (so I have to add limit 1 which doesn't makes sense, as I need this for all my rows).

If I try to do it for the 3 rows, it tells me subquery returns more than one row.

Lukasz Szozda · Accepted Answer · 2024-12-21 13:10:49Z

8

You can write it as:

SELECT value:content::string AS city_name
FROM tab,
LATERAL FLATTEN(input => tab.city_info)
WHERE value:name::string = 'city';

Alternative approach using higher order function FILTER:

CREATE TABLE tab(col VARIANT) AS
SELECT [{'name':'age', 'content': 35},{'name':'city', 'content': 'Chicago'}] UNION
SELECT [{'name':'age', 'content': 20},{'name':'city', 'content': 'Boston'}] UNION 
SELECT [{'name':'city', 'content': 'New York'},{'name':'age', 'content': 42}];

Query:

SELECT *, FILTER(col, x -> x:name::TEXT ILIKE 'City')[0]:content::TEXT
FROM tab;

Output:

edited Dec 21, 2024 at 13:10

answered Nov 26, 2020 at 19:42

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

How to loop through array with JSON objects, to find item that meets condition

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