Search code examples
javaazure-cosmosdbazure-cosmosdb-sqlapiwhere-inazure-java-sdk

How to create the SqlQuerySpec in Java in order to retrieve a list of documents with given ids (where-in clause)


I am working on Azure Cosmos DB with SQL Api. I am using Azure SDK from:

<dependency>
    <groupId>com.microsoft.azure</groupId>
    <artifactId>azure-documentdb</artifactId>
    <version>2.4.7</version>
</dependency>

I have a list of ids, and I would like to find all documents with ids from my list. In order to achieve that I an using Azure SDK SqlQuerySpec, where i defined a "WHERE IN" query as follows:

List<String> ids = Lists.newArrayList("1");
SqlQuerySpec spec = new SqlQuerySpec(
    "SELECT * FROM MyLog c WHERE c.id IN (@ids)", 
    new SqlParameterCollection(new SqlParameter("@ids", ids)));
FeedResponse<Document> documentFeedResponse = documentClient.queryDocuments(collectionLink, spec, queryOptions);
List<Document> documents = documentFeedResponse.getQueryIterable().toList();

But unfortunately the "documents" list is empty, although in my database I have a document with id=1. Just to double check I have tried to run the query in portal:

SELECT * FROM c where c.id IN ("1")

it returns the data correctly. So I am not sure what I am doing wrong. Anyone have already created the SqlQuerySpec in order to retrieve a list of documents with given ids?


Solution

  • Actually, IN is used like where c.id IN ("1"), the param is not array.

    You could use Array_Contains to implement your need:

    List<String> ids = new ArrayList<String>();
    ids.add("1");
    ids.add("2");
    SqlQuerySpec spec = new SqlQuerySpec(
               "SELECT * FROM c WHERE array_contains(@ids, c.id )",
               new SqlParameterCollection(new SqlParameter("@ids", ids)));
    FeedResponse<Document> documentFeedResponse = documentClient.queryDocuments(collectionLink, spec, feedOptions);
                List<Document> documents = documentFeedResponse.getQueryIterable().toList();
    

    Output:

    enter image description here