Search code examples
google-cloud-spanner

Spanner DB query on Array type column

I wanted to write a select query to fetch all the records but I am getting error if I try to match more than one type against a array type column, for example Below query is working

SELECT * FROM Car where ("Ford") IN UNNEST (cartype)

But this query is failing

SELECT * FROM Car where (["Ford","Honda","Suzuki"]) IN UNNEST (cartype)

HTTPError 400: {"code":3, "message":"First argument to IN UNNEST of type ARRAY does not support equality comparison [at 1:42]\

Solution

  • It's not entirely clear from your question whether you want just one of the given values to be in the cartype array, or all, but you probably want one of these functions:

    1. ARRAY_INCLUDES_ALL: This function returns true if all the array elements of the first array are included in the second array (https://cloud.google.com/spanner/docs/reference/standard-sql/array_functions#array_includes_all)
    2. ARRAY_INCLUDES_ANY: This function returns true if at least one of the array elements of the first array is included in the second array (https://cloud.google.com/spanner/docs/reference/standard-sql/array_functions#array_includes_any)

    Assuming that you want at least one to be included in the array, then your query should look like this:

    SELECT *
FROM Car
where ARRAY_INCLUDES_ANY(cartype, ["Ford","Honda","Suzuki"])