Parameterized SQL queries with IN keyword

[yvele]

Are parameterized SQL with IN keyword supposed to be working?

Neither this

{      
    query: "SELECT * FROM root r WHERE r.foo IN (@bar)",     
    parameters: [          
        { name: "@bar", value: ["a", "b", "c"] }       
    ] 
}

or this

{      
    query: "SELECT * FROM root r WHERE r.foo IN @bar",     
    parameters: [          
        { name: "@bar", value: ["a", "b", "c"] }       
    ] 
}

are working.. They gives a syntax error.

PS: Parameterized SQL query documentation states that:

Parameter values can be any valid JSON (strings, numbers, Booleans, null, even arrays or nested JSON). Also since DocumentDB is schema-less, parameters are not validated against any type.

Any workaround other than the usual SQL server hack?

[moderakh]

@yvele a workaround to this is to rewrite the query as:

{      
    query: "SELECT * FROM root r WHERE ARRAY_CONTAINS(@bar, r.foo)",     
    parameters: [          
        { name: "@bar", value: ["a", "b", "c"] }       
    ] 
}

[moderakh]

I am closing this as it is not an issue, if still it doesn't work for you please reopen/comment.

[JannesV]

I can confirm this is still an issue, the workaround works but it's not very clear. Seeing there is no documentation surrounding this whatsoever.

[pebo]

Any plans for adding parameter support for IN queries or documenting the known limitations and workarounds?

[bingbing8]

ARRAY_CONTAINS does not work for me either

[vathakur]

@bingbing8 ARRAY_CONTAINS works for me. Mistake that I was doing before was to enclose the array in double quotes and values in single quotes like "['aa448e10-88a7-448a-b385-124d02624ffe', '1fccbe54-6ed0-4c88-87ad-2b10d86d8990', '6082b3bf-c472-4a30-a387-24c18495e6ba']"
However, it works like below
["aa448e10-88a7-448a-b385-124d02624ffe", "1fccbe54-6ed0-4c88-87ad-2b10d86d8990", "6082b3bf-c472-4a30-a387-24c18495e6ba"].
Don't enclose the complete array in qoutes and use double quotes for values.