IN UNNEST(@array_parameter) - how specify @array_parameter as array of objects

Question

I am using spanner nodejs client library. I need to request query like:

database.run({
sql: `SELECT * FROM Goods@{FORCE_INDEX=GoodsByCodeName} WHERE (code, name) in UNNEST(@codesNames)`,
params: {codesNames: codesNames}}, ...

How specify type of array_parameter - codesNames when it is array of objects [{string, string},...]

Answer 1

i change select by:

database.run({
sql: `SELECT * FROM Goods WHERE (code, name) in (SELECT (code, name) FROM (UNNEST (@codes) as code WITH OFFSET AS num JOIN UNNEST (@names) as name WITH OFFSET AS num using(num)))`,
params: {codes: codes, names: names},....

Answer 2

Cloud Spanner doesn't support object typed parameters yet. To get around that, you could break down your object into parallel array parameters for each of the fields, join them on the offset to create a subquery with rows of the same type as your object type (code<STRING>, name<STRING>) and join that subquery against your table like so:

SELECT * FROM Goods@{FORCE_INDEX=GoodsByCodeName} g 
JOIN (
  SELECT p.code, q.name from
  (SELECT code, o_code FROM UNNEST(@code_array) as code WITH OFFSET o_code) p
  JOIN
  (SELECT name, o_name FROM UNNEST(@name_array) as name WITH OFFSET o_name) q
  ON p.o_code = q.o_name)
) r ON g.code = r.code and g.name = r.name