Couchbase N1QL Query getting distinct on the basis of particular fields

user_8835538 0
Viewed 221

I have a document structure which looks something like this:

{
...
     "groupedFieldKey": "groupedFieldVal",
     "otherFieldKey": "otherFieldVal",
     "filterFieldKey": "filterFieldVal"
...
}

I am trying to fetch all documents which are unique with respect to groupedFieldKey. I also want to fetch otherField from ANY of these documents. This otherFieldKey has minor changes from one document to another, but I am comfortable with getting ANY of these values.

SELECT DISTINCT groupedFieldKey, otherField
FROM bucket
WHERE filterFieldKey = "filterFieldVal";

This query fetches all the documents because of the minor variations.

SELECT groupedFieldKey, maxOtherFieldKey 
FROM bucket
WHERE filterFieldKey = "filterFieldVal"
GROUP BY groupFieldKey
LETTING maxOtherFieldKey= MAX(otherFieldKey);

This query works as expected, but is taking a long time due to the GROUP BY step. As this query is used to show products in UI, this is not a desired behaviour. I have tried applying indexes, but it has not given fast results.

Actual details of the records:

  • Number of records = 100,000
  • Size per record = Approx 10 KB
  • Time taken to load the first 10 records: 3s

Is there a better way to do this? A way of getting DISTINCT only on particular fields will be good.

EDIT 1: You can follow this discussion thread in Couchbase forum: https://forums.couchbase.com/t/getting-distinct-on-the-basis-of-a-field-with-other-fields/26458

n1ql couchbase nosql
1 Answers

GROUP must materialize all the documents. You can try covering index

CREATE INDEX ix1 ON bucket(filterFieldKey, groupFieldKey, otherFieldKey);

user_60805360
Related