CODEWITHSUNDEEP
sqlgoogle-cloud-spanner
Andrew Q
Andrew Q

Reputation: 57

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

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" ]?

Upvotes: 1

Views: 2231

Answers (2)

Chaotic Pechan
Chaotic Pechan

Reputation: 964

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

Upvotes: 2

Andrew Q
Andrew Q

Reputation: 57

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

Upvotes: 1

Related Questions