Query nested array with Spring Data does not work

Java SDK
, ,
1

Hello,

Setup:
Couchbase Server v.7.0.3, Spring Boot 2.6.6 with spring-data-couchbase:4.3.4 dependency

This is my data structure:

{
    "_class": "myApp.model.Customer",
    "_id": "customer/1111111111",
    "_key": "1111111111",
    "_rev": "_d0PzZqu---",
    "activeFrom": "2022-03-07T12:58:12.3796699",
    "customerID": "1111111111",
    "name": "Max Mustermann",
    "addresses": [
      {
        "priority": 1,
        "city": "Darmstadt",
        "street" : "Oldenburgerstr. 1",
        "addressInput": {
          "zipCodes": [
            "DE_26133"
          ]
        }
      },
      {
      "priority": 2,
      "city": "Frankfurt",
      "street" : "Hanauerlandstr. 10",
      "addressInput": {
        "zipCodes": [
          "DE_64807"
        ]
      }
    }
    ]
  }

I don’t know how to query in Spring Data with the @Query - annotation just to get the customer by a zipCode.

@Repository
public interface ShortHaulRouteCouchbaseRepository extends CouchbaseRepository<ShortHaulRoutingRuleSetCouch, String> {
@Query("#{#n1ql.selectEntity} WHERE addresses.addressInput.zipCodes=$zipCode AND activeFrom <= $currentDate AND (activeTo = NULL OR activeTo > $currentDate) ORDER BY activeFrom DESC")
Customer getActiveCustomer(@Param("currentDate") String currentDate, @Param("zipCode") String zipCode );
}

I tried with Spring Data’s helo soemthing like :

Customer = findByAddresses_AddressInput_ZipCodes(String zipCode);

Unfortunatly I recieve this:

java.lang.NullPointerException: null
at org.springframework.data.couchbase.core.ReactiveFindByQueryOperationSupport$ReactiveFindByQuerySupport.lambda$null$1(ReactiveFindByQueryOperationSupport.java:151) ~[spring-data-couchbase-4.1.5.jar:4.1.5]

I also tried with SpEl with the same result.

Kindly suggest where I am doing it incorrectly.
Kind Regards,
Argin

2

I am also looking the solution for the same

3

I don’t know about null pointer exception, But to query give results the WHERE clause must be some thing like below

WHERE (ANY ai IN addresses SATISFIES (ANY zip IN ai.addressInput.zipCodes SATISFIES zip = $zipCode END) END)
      AND activeFrom <= $currentDate
      AND IFMISSINGORNULL(activeTo,"9999-12-31") > $currentDate
4

so when I try this out:

@Query("#{#n1ql.selectEntity} WHERE (ANY ai IN addresses SATISFIES (ANY zip IN ai.addressInput.zipCodes SATISFIES zip = $zipCode END) END) AND activeFrom <= $currentDate AND IFMISSINGORNULL(activeTo,'9999-12-31') > $currentDate")
Customer getActiveCustomer(@Param("currentDate") String currentDate, @Param("zipCode") String zipCode );
}

the response is:

com.couchbase.client.core.error.ParsingFailureException: Parsing of the input failed
	at com.couchbase.client.core.io.netty.query.QueryChunkResponseParser.errorsToThrowable(QueryChunkResponseParser.java:135)
	at java.base/java.util.Optional.map(Optional.java:260)
	at com.couchbase.client.core.io.netty.query.QueryChunkResponseParser.error(QueryChunkResponseParser.java:122)
	at com.couchbase.client.core.io.netty.chunk.ChunkedMessageHandler.lambda$maybeCompleteResponseWithFailure$1(ChunkedMessageHandler.java:281)
	at java.base/java.util.Optional.orElseGet(Optional.java:364)
	at com.couchbase.client.core.io.netty.chunk.ChunkedMessageHandler.maybeCompleteResponseWithFailure(ChunkedMessageHandler.java:280)
	at com.couchbase.client.core.io.netty.chunk.ChunkedMessageHandler.channelRead(ChunkedMessageHandler.java:212)
	at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
5

missing WHERE keyword

6

That was a copy and paste mistake. I have edited my post and added the WHERE keyword

7

This doesn’t look like 4.3.4.

151 cas = row.getLong(TemplateUtils.SELECT_CAS);

A null pointer at line 151 in ReactiveFindByQueryOperationSupport indicates that nothing was projected as SELECT_CAS (__cas). since n1ql.selectEntity does indeed project something as __cas, it appears that the @Query shown is not the one causing this exception.

String entity = "META(" + i(b) + ").id AS " + SELECT_ID + ", META(" + i(b) + ").cas AS " + SELECT_CAS;

8

The expanded query text will be part of the error message.
Run in the Query tab of the web-console to find out what your syntax error is.

9

I am using the latest dpendencies. This was a mistake I will delete that line.
Finally I could run the query but unfortunatly I revieve null results. But this is of the test data I guess.
I will prepare the data and run the query again and let’s see if I can get the expected result.

Thank you for your support @mreiche and @vsr1

1 Like
10 
SELECT *
FROM mybucket 
WHERE (ANY ai IN addresses SATISFIES (ANY zip IN ai.addressInput.zipCodes SATISFIES zip = $zipCode END) END)
      AND activeFrom <= $currentDate
      AND IFMISSINGORNULL(activeTo,"9999-12-31") > $currentDate
11

When specifying conditions on more than one field nested in an array of documents, you can specify the query such that either a single document meets these condition or any combination of documents (including a single document) in the array meets the conditions.

12

krnt.run
myindigocard app