LET CLAUSE in CB Query

Couchbase Server
1

Hi All,
In one of the CB queries, I’m using LET clause, from that I can fetch category name but not category quantity. Please help me out to resolve this issue

Query

SELECT distinct container.name, meta(container).id, container.category, container.serialNumber,
ARRAY pd FOR pd IN cats WHEN container.category = pd.catName END
FROM cbBucket AS container
let cats = (SELECT category.quantity as catQty, category.name as catName FROM cbBucket AS category
WHERE meta(category).id like ‘category_%’ AND POSITION(meta(category).id, ‘AuditLog’) == -1
AND ANY x IN category.subcontainers[0].parts[*].partNumber SATISFIES x IN [‘90000030’] END
)
WHERE meta(container).id like 'container%’ AND POSITION(meta(container).id, ‘AuditLog’) == -1
AND container.currentLocation.displayName = ‘CONTAINER LOCATION 10001’
AND (container.category in
(SELECT RAW category.name FROM cbBucket AS category
WHERE meta(category).id like 'category%’ AND POSITION(meta(category).id, ‘_AuditLog’) == -1
AND ANY x IN category.subcontainers[0].parts.partNumber SATISFIES x IN [‘90000030’] END
)
)
ORDER BY container.category DESC, container.name ASC
OFFSET 0 LIMIT 5000

Result

[
{
“$1”: [
{
“catName”: “Raj Category201118”
}
],
“category”: “Raj Category201118”,
“id”: “container_df353423dfg45233dfgd”,
“name”: “Raj Container211118”,
“serialNumber”: “Raj-211118”
}
]

2

The query seems right. Check the name of the field and it is exist. You can try the LET clause query standalone see if you getting results. If field doesn’t exist or misspelled (also case sensitive) it will not appear.

SELECT category.quantity AS catQty, category.name AS catName
            FROM cbBucket AS category
            WHERE META(category).id LIKE "category_%" AND
                  POSITION(META(category).id, "AuditLog") == -1 AND
                  ANY x IN category.subcontainers[0].parts[*].partNumber SATISFIES x IN ["90000030"] END
                  AND category.name = "Raj Category201118";

Also do you want only check in 0th subcontainers.