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.

Answer 1

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: