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

Question

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"
    }

I don't really follow what you're trying to do. Are there multiple events with the same GUID for `event`? When you use `GROUP BY`, that means you're aggregating *multiple* records (it's not filtering). So there isn't necessarily a *single* document to retrieve. Maybe you can post what you want the results of your query to look like and we can go from there? — Matthew Groves, Oct 12 '21 at 18:11
@MatthewGroves Yes the GUID is same for the event. And, I have updated the question wrt the results of the query — Aishu, Oct 13 '21 at 06:51

vsr · Answer 1 · 2021-10-13T13:50:15.670

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;

score 0 · Answer 2 · answered Oct 13 '21 at 17:50

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.

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

2 Answers2