Need Help with query

Node.js SDK
1

I have 2 document types in the same bucket, and I wish to show the list of assets, but get count of applications which are associate with the same asset.

Asset Document

{
    "assetId": "ASSET1324", // is not document id, not document key
   ... more stuff...
}

Application Document

{
    metaData": {
        "assetId": "ASSET1234",
    }
    "stage": "ACTIVE", // or "HISTORICAL" or "CONFIG"
    ... more stuff...
}

Query1: runs slow . (~700-900ms) in dev, runs slower in prod

SELECT query1.*, (
       SELECT count(*) as count
         FROM myBucket AS countSubQuery
     USE KEYS (SELECT RAW meta().id FROM myBucket AS useKeysQuery WHERE type = 'ApplicationDocument' AND stage != 'HISTORICAL')
        WHERE type = 'ApplicationDocument'
          AND countSubQuery.metaData.assetId = query1.assetId
)[0].count as appCount
      FROM myBucket as query1
     WHERE type = 'AssetDocument'
  ORDER BY updateTs
     LIMIT 10

Query2: Fast (50-70ms), but throws error (but i see results in couchbase admin page)

SELECT query1.*, (
       SELECT RAW count(*) as count
         FROM myBucket AS countSubQuery
     USE KEYS (SELECT RAW meta().id FROM myBucket AS useKeysQuery WHERE type = 'ApplicationDocument' AND stage != 'HISTORICAL' AND metaData.assetId = query1.assetId)
        WHERE type = 'ApplicationDocument'
)[0] as appCount
      FROM myBucket as query1
     WHERE type = 'AssetDocument'
  ORDER BY updateTs
     LIMIT 10

The second Query error throws this error:

Error: Error evaluating projection. - cause: 
FROM in correlated subquery must have USE KEYS clause: FROM myBucket

I have tried adding an index, but i got no clue on how to configure indexes…

  CREATE INDEX idx_some_name
    ON authBlueSLDB(metaData.assetId) 
 WHERE type = 'ApplicationDocument'
  WITH { "num_replica": 1 };

Thanks!!

2

You can’t do count and whole document same time. You have to do two separate queries and combine them in application.

CREATE INDEX ix1 ON myBucket (updateTs) WHERE type = 'AssetDocument';
SELECT q1.*
FROM myBucket AS q1
WHERE type = 'AssetDocument' AND updateTs IS NOT NULL
ORDER BY updateTs
 LIMIT 10;

collect all assetids as list pass that to following query (adhoc=true)

CREATE INDEX ix2 ON myBucket (metaData.assetId, stage) WHERE type = 'ApplicationDocument';
    SELECT count(1) AS cnt, q2.metaData.assetId
    FROM myBucket AS q2 
    WHERE q2.type = 'ApplicationDocument' AND  q2.metaData.assetId IN $assetids AND q2.stage != 'HISTORICAL'
GROUP BY q2.metaData.assetId;

Now combine them in application.

OR

If your assetId are reasonable number you can try this.

CREATE INDEX ix1 ON myBucket (updateTs) WHERE type = 'AssetDocument';
CREATE INDEX ix2 ON myBucket (metaData.assetId, stage) WHERE type = 'ApplicationDocument';
SELECT q1.*, (FIRST v.cnt FOR v IN (SELECT count(1) AS cnt, q2.metaData.assetId
                                    FROM myBucket AS q2
                                    WHERE q2.type = 'ApplicationDocument'
                                          AND q2.metaData.assetId IS NOT NULL
                                           AND q2.stage != 'HISTORICAL'
                                   GROUP BY q2.metaData.assetId)
              WHEN v.assetId = q1.assetId END) AS count
FROM myBucket AS q1
WHERE type = 'AssetDocument' AND updateTs IS NOT NULL
ORDER BY updateTs
LIMIT 10;
3

Thank you for the quick reply.

I created the indexes, and i tried running the query, but getting this error

[
  {
    "code": 5010,
    "msg": "Error evaluating projection. - cause: Expression must be a group key or aggregate: 
((`q2`.`metaData`).`assetd`)"
  }
]

I tried adding a clause on the inner query

GROUP BY q2.metaData.assetId

Now, the query returns data, however I am getting incorrect count on some records (while others are correct). I have checked that all asset docs have assetIds, and I have also checked that all application docs have metaData.assetId

4

Sorry, disregard, the query works correctly after adding the GROUP BY! Apologies!

1 Like