Query a Array in google cloud spanner

Question

How can i query an array in google cloud spanner?

I have tried this

let query = {
    sql:`SELECT id, name, description FROM articles WHERE lang = '` + req.swagger.params.lang.value + `' AND tags ARRAY['` + req.swagger.params.tag.value + `']::varchar[]`
  };

Answer 1

You'll want to implicitly unnest your array element. As an example

SELECT id, name, description
FROM articles as a, a.tags as single_tag
WHERE lang = "your lang value"
      AND single_tag = "your tag value"

First, I implicitly unnested the array column tags to the column single_tag.

Second, I changed your where clause to an equality against the new single_tag column

Note: If multiple values in tags match your criteria, you can have the same row returned multiple times. You can address this by adding in a DISTINCT clause as long as none of the columns you are returning are arrays or structs. For example:

SELECT DISTINCT id, name, description
FROM articles as a, a.tags as single_tag
WHERE lang = "your lang value"
      AND single_tag = "your tag value"