Get a sample of each doctype from Couchbase

Question

I am working with a Couchbase bucket where all documents are saved with a DocType attribute identifying what type of data is contained. I want to perform a query that will give me a single result from each DocType, to use as a sample of all document types.

I can get each DocType using SELECT DISTINCT(DocType) FROM some-database. And I can get a sample by doing SELECT * FROM some-database WHERE DocType='User' LIMIT 1. But I don't know how to combine these things to make a single query return a single example from each matching DocType.

Answer 1

You can use GROUP query.

SELECT MAX(d).*
FROM default AS d
WHERE d.DocType IS NOT NULL
GROUP BY d.DocType;

OR First get one document key for different DocType using covered index and then get those documents.

CREATE INDEX ix1 ON default(DocType);

SELECT d1.* 
FROM default d1 USE KEYS (
         SELECT RAW MAX(META(d).id)
         FROM default AS d
         WHERE d.DocType IS NOT NULL
         GROUP BY d.DocType);