Mixing IN operator with SQL database and arrays

[oliviermilla]

Hello,

I’ve followed the step described in Mixing Queries and Arrays into SQL / Observable | Observable to get a query with a IN operator working:

then using selectedFundInstitRetail in a SQL query running on Snowflake:

SELECT
...LONG...LIST...
FROM SNOWFLAKE
WHERE COLUMN_IN_QUESTION IN (${selectedFundInstitRetail})

Here comes the funny part:

• When I select both Institutional and Retail, I only get Institutional in the results.
• When I select only Institutional, I only get Institutional in the results.
• When I select only Retail, I only get Retail in the results.

In short, one could say that the interpolation only uses the FIRST value of the array.

I tried to work around by separating the stuffs:

1. Remove the WHERE clause in the above query.
2. Create a new SQL Cell, with the above query as the source.
3. Try applying the WHERE clause in there.

This yields the error:

Error: Invalid column type encountered for argument 0.

Which I don’t get. Indeed that means I’m querying a JS Object “as if” a SQL database. But I understood that was a super-trick of Observable.

Thx for your Help.

[mootari]

That sounds like it’s dropping everything after the first array item which happens in vanilla Snowflake connectors if you pass arrays. Make sure you define a new data source in your notebook through extendDB(...), and to select that source in your SQL cell.

You can verify that the arguments get passed through correctly by running the following query:

select ${["foo", "bar"]}

---

For future reference, here’s a list of how the various clients handle arrays by default:

[oliviermilla]

Thank you @mootari !