Query/Question

How to query a document in a Spring Data repository based on a partial, parameterized JSON matching?

I have the following data model for a user:

{
    "id": "<UUID>",
    "userApiAuthentications": [
        {
            "userApiKey": "YjYIep4BCGIlD1CJG4g6kpOXM4gMKPbC5cRiDdHv8QTvQIDOdbM0RGy3BbAaiy0p4XpbvndLuIPPaTTmLccvPqFtsBQE3P89Vlr4",
            "authenticationAssuranceLevel": "Regular"
        },
        {
            "userApiKey": "sqfXLVirCAWnbhKe7o59I2HDyjbQbI8DlINfNfSQmSiGrRx541phL9TbzARh8zWKQP1k9FyHJ6S2iWnTkiKeAzGfZSBY8dEhhiJ5",
            "authenticationAssuranceLevel": "PasswordElevated"
        },
        {
            "userApiKey": "bKMyzzpIno4PbKCeeH8oQKtjHmlNg61SWDOcQVJKUwtTLbJUdkEiO40jfbmaobtt7iQdHQDXUXOae9plEJ6nJ4xZ4gdw7sa2sEhF",
            "authenticationAssuranceLevel": "Regular"
        },
        …a few more of these, it's likely max 5-10 / User
    ],
    "firstName": "…",
    "lastName": "…",
    "emailAddress": "…",
    …
}

I would like to query a User document based on its userApiKey (which is unique within the container). On the Portal, the following query in the Data Explorer runs well and returns the user (the whole above object) possessing the given API key.

SELECT * FROM users WHERE ARRAY_CONTAINS(users.userApiAuthentications, { "userApiKey": "bKMyzzpIno4PbKCeeH8oQKtjHmlNg61SWDOcQVJKUwtTLbJUdkEiO40jfbmaobtt7iQdHQDXUXOae9plEJ6nJ4xZ4gdw7sa2sEhF" }, true)

I want to do the same query from a Spring Data CosmosRepository. My repository looks like this:

@Repository
public interface UserRepository extends CosmosRepository<User, String> {
    @Query(value = "SELECT * FROM users WHERE ARRAY_CONTAINS(users.userApiAuthentications, { \"userApiKey\": \"@userApiKey\" }, true)")
    Optional<User> findByUserApiKey(@Param(value = "userApiKey") String userApiKey);
}

The findByUserApiKey(String userApiKey) method uses the same query as the one ran directly against the DB on the portal (successfully), but querying via the repository always throws a "not found" exception.

I tried the above query with all kinds of different escaping, and without escaping – neither did work.
I tried the above query with the parameter not being the API key, but the partial JSON document to be matched (@Query(value = "SELECT * FROM users WHERE ARRAY_CONTAINS(users.userApiAuthentications, @userApiKeyPartialDocument, true)"), where @userApiKeyPartialDocument gets the value { "userApiKey": "bKMyzzpIno4PbKCeeH8oQKtjHmlNg61SWDOcQVJKUwtTLbJUdkEiO40jfbmaobtt7iQdHQDXUXOae9plEJ6nJ4xZ4gdw7sa2sEhF" }) – did not work.

• The question is: what am I doing wrong? How to query Cosmos with Spring Data matching on a partial, parameterized JSON?
• Can I log somewhere the final SQL query ran against the database assembled by Spring Data? Maybe the parameterization works differently for partial JSON matches?

I know that this could easily be done by a self-join, but then I would not be able to use SELECT *. Also, I'm curious about what am I missing in this.

Why is this not a Bug or a feature Request?
Until it's not clear whether I did something wrong, it should not be a bug.

Setup (please complete the following information if applicable):

• OS: macOS Catalina 10.15.7
• IDE : IntelliJ IDEA 2020.2
• Version of the Library used: com.azure.azure-spring-data-cosmos 3.0.0

Information Checklist
Kindly make sure that you have added all the following information above and checkoff the required fields otherwise we will treat the issuer as an incomplete report

• Query Added
• Setup information Added