3

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]\

RF1991
  • 2,037
  • 4
  • 8
  • 17
sKarwasra
  • 45
  • 2

2 Answers2

1

I have tried this on my end i got the same error when i tried with below query

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

Instead of the ARRAY\_INCLUDES function as answered by @Knut Olav Løite you can use the below query as well.

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

In above I have changed the query where comparing the array column cartype with an array literal (["Ford","Honda","Suzuki"])

UNNEST operator converts an array to a table. Here cartype is a table column in which these UNNEST values may appear.

Sathi Aiswarya
  • 2,068
  • 2
  • 11
0

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"])
Knut Olav Løite
  • 2,964
  • 7
  • 19