CODEWITHSUNDEEP
joincouchbasegreatest-n-per-groupsql++
Momin Fakhruddin
Momin Fakhruddin

Reputation: 101

N1QL query to join document, with the latest child (on basis of creationDateTime) in couchbase

N1QL query to get the latest document having same other property in a join result of two different documents in couchbase

I have a bucket called "prices", with two different types of documents "intent" and "request"

  "intent"  (id = "intent1")
  {
  "locationDSL": "some_location"
  "product": "some_product1"
  }
  "intent"  (id = "intent2")
  {
  "locationDSL": "some_location2"
  "product": "some_product2"
  }
  "request"  (id = "request1")
  {
  "intentId": "intent1",
  "createdDateTime": "2019-04-01",
  "status" : "success"
  }
  "request"  (id = "request2")
  {
  "intentId": "intent1",
  "createdDateTime": "2019-05-01"
   "status" : "failed"
  }
  "request"  (id = "request3")
  {
  "intentId": "intent2",
  "createdDateTime": "2019-06-01",
 "status" : "failed"
  }
  "request"  (id = "request4")
  {
  "intentId": "intent2",
  "createdDateTime": "2019-07-01",
  "status" : "success"
  }

so I have 2 requests("request1" and "request2") for "intent1" and 2 requests("request3" and "request4") for intent2,

I need to join "intent1" and "intent2" with latest requests (request having latest createdDateTime) i.e "request2" and "request4" respectively, and also able filter on some fields of latest child document of matching "intentId", so if query for "status" = "success", then it should only return (intentId2 , request4) and not (intent1,request1), since in the latest child "request2" doesn't match with the condition

i am able to join the documents , but the join is not with latest requests but all requests matching intent.id.

This question is similar to [Filter documents using n1ql ,but I need a join of all the fields in two documents, rather than single field or attributes

Upvotes: 0

Views: 116

Answers (1)

vsr
vsr

Reputation: 7414

Based on your query try the following. The subquery uses covering index and only fetch the latest document in parent query

CREATE INDEX ix1 ON pricescmd(intentId, status, createdDateTime DESC);
SELECT intents, request
FROM (SELECT mx.*
      FROM pricescmd
      WHERE intentId IS NOT NULL 
      GROUP BY intentId, status
      mx = MAX([createdDateTime, {META().id, intentId, status} ])[1] )  AS d
LET request = (SELECT RAW request FROM pricescmd AS request USE KEYS d.id)[0],
    intents = (SELECT RAW intent FROM pricescmd AS intent USE KEYS d.intentId)[0];

Upvotes: 3

Related Questions