Why USE KEYS error shown when using DB name

[selfy]

The following query works just fine

select * from ( ["A","C"]) AS d
	UNNEST (select * from ( [{"type":"C","name":"C"},{"type":"C","name":"D"}]) v  where v.type=d limit 1) l
	

But when I replace the data source in the subquery with actual database/bucket name,

select * from ( ["A","C"]) AS d
	UNNEST (select * from ( `my-db`) v  where v.type=d limit 1) l
	

i get an “USE KEYS” error. Why and how can I work this out ?

[
  {
    "code": 5370,
    "msg": "Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM my-db.",
    "query": "select * from ( [\"A\",\"C\"]) AS d\n\tUNNEST (select * from `my-db` v  where v.type=d limit 1) l"
  },
  {
    "code": 5010,
    "msg": "Error evaluating UNNEST path. - cause: FROM in correlated subquery must have USE KEYS clause: FROM my-db."
  }
]

[dh]

This restriction was lifted in https://issues.couchbase.com/browse/MB-30813 which was released in version 7. So presumably you’re still on version 6 ?

As the error states, you have to use a USE KEYS clause to specify the keys for the correlated subquery in this case and you haven’t had to with the constants as they’re constants - the restriction applies to keyspaces only.

Assuming your intention is just to get one document of each type, then this statement could be written without the correlated sub-query:

SELECT v.type d, ARRAY_AGG({"v":v})[0] l                                                                                            
FROM `my-db` v
WHERE v.type IN ["IntegrationSyncTrackerPerDataType","C"]
GROUP BY v.type
;

with:

CREATE INDEX ix1 ON `my-db`(type);

(or equivalent).

I’ve decorated the output so the same structure is achieved; if all you’re actually after is the document, then more simply:

SELECT ARRAY_AGG(v)[0] v                                                                                                            
FROM `my-db` v
WHERE v.type IN ["IntegrationSyncTrackerPerDataType","C"]
GROUP BY v.type
;

will do.

(Without a strict order it is possible for the specific returned document for each “type” to vary as the data varies or index layout changes - e.g. if you have a partitioned index.)

HTH.

[selfy]

Yes, I am on version 6 of Couchbase.

I am trying to get n records for each “type”. That was what I was intending to achieve using the limit clause. Is this possible with the suggested solution ?

[dh]

Yes, ARRAY_AGG() produces an array so you could use the [0:n] syntax to return the first n elements from the array, e.g. to return (up to) 3 per type:

SELECT u.* FROM (
SELECT CASE WHEN 3 < ARRAY_LENGTH(t.v) THEN t.v[0:3] ELSE t.v END v
FROM
(
SELECT ARRAY_AGG(v) v
FROM default v
WHERE v.type IN ["IntegrationSyncTrackerPerDataType","C"]
GROUP BY v.type
) t) t2 UNNEST t2.v u
;

This approach is a little convoluted as it requires the conditional array length, and then UNNESTing of the array itself to flatten the result.

Edit: an alternative:

SELECT v.*                                                                                                                          
FROM (
SELECT a[0:ARRAY_MIN([3,ARRAY_LENGTH(a)])] v
FROM default v
WHERE v.type IN ["IntegrationSyncTrackerPerDataType","C"]
GROUP BY v.type
LETTING a = ARRAY_AGG(v)
) t UNNEST t.v v
;

[selfy]

When I remove the constants and use a subquery like below, it times out.

SELECT v.type d, ARRAY_AGG({"v":v})[1:5] l                                                                                            
FROM `my-db` v
WHERE v.type IN (select distinct type from `my-db`)
GROUP BY v.type
;

I am guessing its an indexing issue? Have a new post on this here.

[dh]

Yes. Without the filters the index you defined will not be used. To use an index there must be a filter. You could try:

SELECT v.type d, ARRAY_AGG({"v":v})[1:5] l                                                                                            
FROM `my-db` v
WHERE v.type IN (select distinct type from `my-db` WHERE type IS VALUED)
GROUP BY v.type
;

But then this makes little sense as it could just be written as:

SELECT v.type d, ARRAY_AGG({"v":v})[1:5] l                                                                                            
FROM `my-db` v
WHERE v.type IS VALUED
GROUP BY v.type
;

i.e. there is no need for the IN-clause that doesn’t conditionally select a sub-set of type values.

This will still be expensive overall though as you’re gathering all records from the database.

[vsr1]

If you have millions of document this too expensive.
Number of types might be small.
So use covered and get types and then use non-covered query get n documents for each type.

CREATE INDEX adv_type ON `my-db`(`type`);

If one document for GROUP

WITH docs AS (SELECT RAW MAX(META(d).id) 
                          FROM `my-db` AS d
                          WHERE d.type IS VALUED
                          GROUP BY d.type)
SELECT d1.* 
FROM `my-db` AS d1 USE KEYS docs;

n documents drive from Application

Get types

SELECT RAW d.type
FROM `my-db` AS d
WHERE d.type IS VALUED
GROUP BY d.type;

Each type issue different query

     SELECT d.*
      FROM `my-db` AS d
      WHERE d.type = $type
      LIMIT 5;

WITH 7.0

SELECT t.type,
       (SELECT RAW d1
        FROM `my-db` AS d1
        WHERE d1.type = t.ype
        LIMIT 5) AS docs
FROM (SELECT RAW d.type
      FROM `my-db` AS d
      WHERE d.type IS VALUED
      GROUP BY d.type) AS t;