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

Question

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?

Jay Gong Jay Gong · Accepted Answer · 2020-03-31T02:25:23

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:

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

1 Answers