SQL - how to query where a column is an array and you want to get records where the element is in th-CodePudding

In Google Spanner how can I create a query with a WHERE clause about a column, FOO that is of type ARRAY<STRING> where the column, FOO array contains the value [ "BAR" ]?

CodePudding user response：

Figured this out shortly after: SELECT * FROM SOME_TABLE WHERE ( "BAR" ) IN UNNEST (FOO);

CodePudding user response：

There are different ways to do this. But as you same mentioned. You can use UNNEST method into an struct array type data:

SELECT * 
FROM TABLE_NAME
WHERE ("BAR") IN UNNEST (FOO).

If you want to use as subquery could be:

SELECT ARRAY( SELECT * FROM UNNEST(FOO) AS x where x = "BAR")

You can find more details for working with arrays in cloud spanner here: https://cloud.google.com/spanner/docs/reference/standard-sql/arrays