CODEWITHSUNDEEP
jsoncouchbasesql++
Aishu
Aishu

Reputation: 1330

Want to get the entire document in couchbase after applying group by clause

I have multiple json document like this

{
  "event": "eb1518fb-81d1-49aa-9854-405267a7828f",
  "identityId": "5eb44b44-d5ce-4473-815c-591a4ff1bea3",
  "bookingCode": "ABCDE",
  "status": "SUCCESS",
  "clientId": "DESKTOP",
  "created": "2021-04-15T04:16:35+00:00",
  "locationType": "STORE"
},
{
  "event": "eb1518fb-81d1-49aa-9854-405267a7828f",
  "identityId": "5eb44b44-d5ce-4473-815c-591a4ff1bea3",
  "bookingCode": "ABCDE",
  "status": "FAILURE",
  "clientId": "MOBILE",
  "created": "2021-04-15T04:16:35+00:00",
  "locationType": "STORE"
},
{
  "event": "eb1518fb-81d1-49aa-9854-405267a7828f",
  "identityId": "q1244b44-d5ce-4473-815c-591a4ff1bea3",
  "bookingCode": "BCD",
  "status": "SUCCESS",
  "clientId": "MOBILE,
  "created": "2021-04-15T04:16:35+00:00",
  "locationType": "STORE"
},
...

I want to apply group by identity id and booking code to get the unique records and need to get the entire JSON document after filtering, something like this

select *, count(*) from eventbooking where event = "eb1518fb-81d1-49aa-9854-405267a7828f"
group by bookingCode, identityId

This is giving me an error Expression self must depend only on group keys or aggregates Is there any way that i can get the entire json document after filtering with group by?

I'm expecting an output similar to this.

{
      "event": "eb1518fb-81d1-49aa-9854-405267a7828f",
      "identityId": "5eb44b44-d5ce-4473-815c-591a4ff1bea3",
      "bookingCode": "ABCDE",
      "status": "FAILURE",
      "clientId": "MOBILE",
      "created": "2021-04-15T04:16:35+00:00",
      "locationType": "STORE"
    },
    {
      "event": "eb1518fb-81d1-49aa-9854-405267a7828f",
      "identityId": "q1244b44-d5ce-4473-815c-591a4ff1bea3",
      "bookingCode": "BCD",
      "status": "SUCCESS",
      "clientId": "MOBILE,
      "created": "2021-04-15T04:16:35+00:00",
      "locationType": "STORE"
    }

Upvotes: 1

Views: 152

Answers (2)

G. Blake Meike
G. Blake Meike

Reputation: 6715

A Query will never return a document. It always returns a Map<KeyString, Value> of the projection in your SELECT clause (in this case, "*", all the values).

If you need the document (in order to modify it), you can query for the Meta.Id for documents matching your WHERE clause and then iterate over them, getting, altering and saving the document.

Upvotes: 0

vsr
vsr

Reputation: 7414

Query uses GROUP BY or Aggregates then projection can only contain

  • Group keys
  • Aggregates
  • Expression on GROUP keys or Aggregates
  • Constants

Assume each group has less documents you can aggregate all of them as ARRAY. If need different format, get via array subscript or use UNNEST in parent query. If it has lot use covered query and ARRAY_AGG document keys then parent query get those document (streamed the documents).

SELECT COUNT(1) AS cnt,
       ARRAY_AGG(e) AS docs
FROM eventbooking AS e
WHERE e.event = "eb1518fb-81d1-49aa-9854-405267a7828f"
GROUP BY e.bookingCode, e.identityId;

If you need latest created document in the group only.

SELECT COUNT(1) AS cnt,
       MAX([e.created,e])[1].* 
FROM eventbooking AS e
WHERE e.event = "eb1518fb-81d1-49aa-9854-405267a7828f"
GROUP BY e.bookingCode, e.identityId;

Upvotes: 1

Related Questions