query array in SQL

[Raymond_Garcia]

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!

[Raymond_Garcia · Accepted solution]

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"