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 · Accepted Answer · 2020-03-31 02:25:23Z

2

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:

answered Mar 31, 2020 at 2:25

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

fascynacja Over a year ago

this works perfectly. On the top of that question, If I had a list of type Long? List<Long> ids = ... then is there a way to retrieve the data without transforming longs into strings?

Jay Gong Over a year ago

@fascynacja no matter it is long or string,it should be corresponding.

fascynacja Over a year ago

it did not work for me. Could you try to run your example but instead List<String> use List<Long> ?

Jay Gong Over a year ago

@fascynacja The id is cosmos db should be string, i think. If you want to use Long in code,i think you need to convert it in the sql because they are dismatch.

Jay Gong Over a year ago

@fascynacja You're welcome! Any concern,pls let me know.

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related