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?

score 2 · Accepted Answer · answered Mar 31 '20 at 02:25

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 '20 at 02:25

Jay Gong

23,163
2
27
32

1

this works perfectly. On the top of that question, If I had a list of type Long? List ids = ... then is there a way to retrieve the data without transforming longs into strings? – fascynacja Mar 31 '20 at 07:03
@fascynacja no matter it is long or string,it should be corresponding. – Jay Gong Mar 31 '20 at 07:09
it did not work for me. Could you try to run your example but instead List use List ? – fascynacja Mar 31 '20 at 07:40
@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 Mar 31 '20 at 07:41
@fascynacja If you do not want to use string, have to use long.Then you could leave the id, create id column by yourself. Such as userId,deviceId.... – Jay Gong Mar 31 '20 at 07:42
for now I transformed List into List. I will think about the solutions you mentioned, thank you. – fascynacja Mar 31 '20 at 07:46
1

@fascynacja You're welcome! Any concern,pls let me know. – Jay Gong Mar 31 '20 at 07:50
error comes as this constructor is not defined. Can you help me with the code, including maven dependencies. – Anuj Sharma May 13 '20 at 06:45

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

1 Answers1