query array in SQL

Raymond_Garcia — Tue, 05 Jul 2022 17:38:34 GMT

Hello I have a databricks question I was not able to answer myself

I have this query

select count(*) from table

where object[0].value is not null and object[0].value.value1 = "s"

and created_year = 2022 and created_month = 7 and created_day = 4

you can see object[0] is an array

The question is: how can I query the whole array?

Thanks!

Re: query array in SQL

Raymond_Garcia — Wed, 06 Jul 2022 14:36:03 GMT

SELECT count(*)

FROM (

SELECT explode(mmycolumn)

FROM table

WHERE created_year = 2022 and created_month = 7 and created_day = 5

)

WHERE col.field is not null and col.field.field! = "signal"