[QUERY] Can I define a custom query that returns a single or Optional entity object?

[zhoujia1974]

Query/Question I want to define a custom query using @Query annotation that I know will return either one document or none. I tried to define the Repository interface like below.

@Query( "SELECT * FROM c WHERE c.partitionKey = @partitionKey AND c.someField = @someField") Optional<SomeEntityClass> find( @Param("partitionKey") String partitionKey, @Param("someField") String someField);

But I got the following exception when I run a test. Caused by: java.lang.ClassCastException: class java.util.ArrayList cannot be cast to class xxx.SomeEntityClass (java.util.ArrayList is in module java.base of loader 'bootstrap'; xxx.SomeEntityClass is in unnamed module of loader 'app')

I also tried change the method to return SomeEntityClass instead of Optional<SomeEntityClass >, it gives the same error. If I change the return type to List<SomeEntityClass>, then it works fine.

I suspected the current azure spring data cosmos version only support to return a List instead of a single object or Optional. Is it the case?

Why is this not a Bug or a feature Request? Not sure if this is a bug or just a missing feature or something I did is wrong.

Setup (please complete the following information if applicable):

• OS: Windows 10
• IDE: Eclipse
• Library/Libraries: com.azure.spring:azure-spring-boot-starter-cosmos:3.6.0

[backwind1233]

Hi @kushagraThapar, can you help take care of this issue?

[kushagraThapar]

@zhoujia1974 - I think you can. Currently your query is select * which returns array of data. It can be one record, or multiple records. There is no way of knowing that upfront until the query executes. Try executing a value query, and it should return only a single entity. That should work.

Either something like this - https://github.com/Azure/azure-sdk-for-java/blob/main/sdk/cosmos/azure-spring-data-cosmos-test/src/test/java/com/azure/spring/data/cosmos/repository/repository/ContactRepository.java#L33

Or something like this - https://github.com/Azure/azure-sdk-for-java/blob/main/sdk/cosmos/azure-spring-data-cosmos-test/src/test/java/com/azure/spring/data/cosmos/repository/repository/ContactRepository.java#L31

[zhoujia1974]

@kushagraThapar I have two custom queries (see below examples) similar to your second linked code in my project and they both work fine. If I run the custom query in Azure CosmosDB Data Explorer, it actually still returns an array "[ 10 ]". It's just in the array it only returns a json value instead of json object. But the SDK in this case can based on the return type of my method to correctly convert the return to either a single value or a List. Why it doesn't do the same when the custom query returns an array of json objects?

@Query(value = "SELECT VALUE COUNT(1) from c where c.title = @title") long count(@Param("title") String title);

@Query(value = "SELECT VALUE c.name from c where c.title = @title") List<String> getNames(@Param("title") String title);

If I define the custom query using VALUE with all the fields in the document, something like below. The return is still a list of json objects. I don't see this is different than not using VALUE.

SELECT VALUE {"id": c.id, "type" : c.type, "name": c.name, } FROM c WHERE c.partitionKey = 'xxxx'

[ { "id": "some id", "name": "some name", "type": "some type" } ]

[trande4884]

At this link in the "Native SQL Select @Query with Index Parameters" section you can see a SELECT *... that returns an individual BOOK object. So I am assuming if multiple results are found this query is just returning the first record.

@kushagraThapar I will look into this unless you think this is bad practice for our API.

[kushagraThapar]

@trande4884 returning just the first object is not intuitive, also, based on the query execution, the returned list can have any ordering, which will cause the query to return different results every time the query is executed in case it has more than one return values. @zhoujia1974 - I don't think it is possible to return any other type than list for any queries for cosmos db. For optional return types, we can figure it out based on the return type, however, for the above reason it won't be ideal to do so.

[kushagraThapar]

Closing this issue because of inactivity. @zhoujia1974 please feel free to open it if your problem still persists.