spring data couchbase N1ql Query parameter

Question

I am Using Spring-data-Couchbase 2.1.5. I want to run the following N1QL query on the docs give below. Can anyone suggest how to do it??

1) N1QL Query

SELECT pkgPrcStsCd, count(*) AS count
FROM `default`
WHERE ANY sort IN busOp SATISFIES sort.opPlnDt = now_str('1111-11-11') END
GROUP BY (pkgPrcStsCd)

2) Docs in the Couchbase is as

{
  "docType": "busOpPrcShu",
  "docId": "busOpPrcShu-US-NJTIN-PKG-1Z2FW2350209207151-171901",
  "docVer": 1,
  "className": "com.operations.package",
  "shuTckNr": "32547354dhfgds",
  "ogzCountryCode": "US",
  "ogzNumber": "12345",
  "pkgXcpRsnCd": "78",
  "pkgPrcStsCd": "01",
  "prcStsUdtTs": "2017-05-19 15:50:30",
  "evtMosRenSleIr": "true",
  "evtTypCd": "02",
  "evtCgyCd": "01",
  "evtTypUdtTs": "2017-05-19T11:45:30Z",
  "eqpNr": "04856374",
  "busOp": [
    {
      "docId": "gkfhgjh",
      "opPlnDt": "2017-05-22",
      "wrkCpt": [
        {
          "docId": "fhkdshfsj"
        }
      ]
    }
  ]
}

Simon Baslé Simon Baslé · Accepted Answer · 2017-05-23T09:32:34

That query doesn't seem like it can map to an entity type, so this is out of the scope of Spring Data. Spring Data Couchbase would usually apply extra steps when querying for entities, like choosing the concrete class via the _class attribute or decoding the document's metadata for the key...

However the CouchbaseTemplate has a findByN1qlFragment method that doesn't attempt these extra steps but just uses Jackson to unmarshall to the type you provided. So you could have a simple class with 2 fields (one for the pkgPrcStsCd, one for the count) and use that method.

The query must be built by hand using the Couchbase SDK, either as plain String or using the Select DSL.

spring data couchbase N1ql Query parameter

1 Answers