CosmosDB query in Logic App: Empty response when querying across partitions

In Logic App, I am querying from CosmosDB using the following Query Documents V5 activity:

(https://i.sstatic.net/bHgOdXUr.png)

I want to pass in a list of GameID for this Query activity. However, even though the same query works fine in the Data Explorer, CosmosDB always returns an empty response for me in the Logic App.

Since these GameIDs are from different partitions, I have purposefully left the partition key field empty to search in all partitions. Here is a sample request:

{
    "method": "get",
    "queries": {
        "queryText": "SELECT c.Moves, c.WhiteElo, c.BlackElo FROM c WHERE ARRAY_CONTAINS([[\"2013-02-1218\",\"2013-03-1095\",\"2013-01-588\"]], c.GameID)"
    },
    "path": "/v5/cosmosdb/AccountNameFromSettings/dbs/{my-account-name}/colls/games/query",
    "host": {
        "connection": {
            "name": "/subscriptions/2f5a8e0d-7231-4f3a-afae-4c4825eda114/resourceGroups/{my-resource-group-name}/providers/Microsoft.Web/connections/documentdb-2"
        }
    }
}

And response from CosmosDB:

{
    "statusCode": 200,
    "headers": {
        "Cache-Control": "no-store, no-cache",
        "Pragma": "no-cache",
        "Transfer-Encoding": "chunked",
        "Vary": "Accept-Encoding",
        "Set-Cookie": "ARRAffinity=8cb9eb8a9c8e49bb32964ef5e087477636164e3b1bd119e62b62b2d516d04b33;Path=/;HttpOnly;Secure;Domain=documentdb-cc.azconn-cc-001.p.azurewebsites.net,ARRAffinitySameSite=8cb9eb8a9c8e49bb32964ef5e087477636164e3b1bd119e62b62b2d516d04b33;Path=/;HttpOnly;SameSite=None;Secure;Domain=documentdb-cc.azconn-cc-001.p.azurewebsites.net",
        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
        "x-ms-request-id": "fe1b4258-368a-4b3f-90c0-aa744576a101",
        "X-Content-Type-Options": "nosniff",
        "X-Frame-Options": "DENY",
        "x-ms-connection-parameter-set-name": "keyBasedAuth",
        "Timing-Allow-Origin": "*",
        "x-ms-apihub-cached-response": "true",
        "x-ms-apihub-obo": "false",
        "Date": "Fri, 03 May 2024 19:28:22 GMT",
        "Content-Type": "application/json",
        "Content-Length": "162",
        "Expires": "-1"
    },
    "body": {
        "value": [],
        "ContinuationToken": null,
        "Count": 0,
        "RequestCharge": 2.8,
        "SessionToken": "0:-1#2152",
        "ActivityId": "1f732ebe-d76b-4001-ace3-4266708c9e33",
        "nextLink": null
    }

Below is a sample record from the database. Event is the partition key so I am querying for chess GameIDs across different Events like "Blitz","Classical",etc.

{
    "GameID": "2013-01-736",
    "Moves": "1. e4 e5 2. Nf3 d6 3. c3 f5 4. Qe2 f4 5. d4 Nc6 6. dxe5 Nxe5 7. Bxf4 Nxf3+ 8. Qxf3 Nf6 9. Bc4 Qe7 10. O-O Nxe4 11. Re1 Bf5 12. Nd2 O-O-O 13. Nxe4 d5 14. Bxd5 Qd7 15. Ng3 Bg4 16. Bxb7+ Kb8 17. Qe4 g6 18. Ba6 Qd5 19. f3 Bc5+ 20. Be3 Rd6 21. Rad1 Qxe4 22. Nxe4 Rxd1 23. Rxd1 Bxe3+ 24. Kf1 Bc8 25. Nc5 Bxc5 26. b4 Be7 27. b5 Bd8 28. c4 c6 29. Rd6 cxb5 30. cxb5 Kc7 31. Rc6+ Kd7 32. Bxc8+ Ke7 33. Re6+ Kf7 34. Ra6 Re8 35. Rxa7+ Kf8 36. a4 Bb6 37. Ra6 Bc5 38. Bd7 Rd8 39. Bc6 Bb4 40. Ra8 Rxa8 41. Bxa8 Ke7 42. b6 Kd7 43. b7 Kc7 44. Ke2 Kb8 45. Kd3 Ka7 46. Kc4 Ba3 47. Kb5 Bc1 48. g3 Be3 49. Kc6 Bg1 50. Kc7 Bxh2 51. b8=Q+ Ka6 52. Qb6# 1-0",
    "WhiteElo": 1273,
    "BlackElo": 1168,
    "TimeControl": "300+5",
    "Event": "Classical"
}

I have also found that if I specify only one GameID in the list (e.g., ["2013-01-736"]), the query works as expected, even without specifying a partition key.

Other threads with similar issues suggest using the ContinuationToken to possibly retrieve the remaining results, but in my case it's null.

There were also some hints about setting EnableCrossPartitionQuery to true, but I can't find the option to modify the request header in Logic App. It's possible it might have been enabled by default with CosmosDB SDK V3

So... here's your query, extracted from the call:

SELECT c.Moves, c.WhiteElo, c.BlackElo
FROM c
WHERE ARRAY_CONTAINS([["2013-02-1218","2013-03-1095","2013-01-588"]], c.GameID)

The issue is that the first parameter of ARRAY_CONTAINS() needs to be an array of values, and somehow it's wrapped in an outer array, so in your case, the entire array of values is treated as a single value.

If you remove the outer array, then ARRAY_CONTAINS() should work correctly:

SELECT c.Moves, c.WhiteElo, c.BlackElo
FROM c
WHERE ARRAY_CONTAINS(["2013-02-1218","2013-03-1095","2013-01-588"], c.GameID)