CODEWITHSUNDEEP
nosqlcouchbasesql++
A.C.
A.C.

Reputation: 53

Couchbase N1QL query to find document that has a field value containing the ID of another document that exists

I have a Couchbase bucket "businesses" of documents, e.g.: business listings, like listing_HomeDepot, listing_Lowes, listing_DansHardware.

Each listing_* doc has a field businessInfo that contains part of the key to another document in the same bucket. E.g., listing_DansHardware has "businessInfo": "3a4tg", and there should be a document with key info_3a4tg. The document info_3a4tg has an "id" field with "3a4tg" in it.

Let's say Dans Hardware closed down and the info_3a4tg document got deleted. I want to select all businesses that are in the bucket, but filter out any businesses with orphaned business info references.

I tried: select * from businesses b1 where b1.category='hardware' and exists (select * from businesses b2 use keys 'info_'||b1.businessInfo)

but that didn't work. I got some "Document key must be string" error. Any tips, please?

Upvotes: 1

Views: 2222

Answers (1)

vsr
vsr

Reputation: 7414

|| operation can be done on string only. Check if you have businessInfo has string on all the documents. OR use IS_STR(b1.businessInfo) predicate

SELECT b1.*
FROM businesses AS b1
WHERE b1.category = 'hardware'
      AND IS_STR(b1.businessInfo)
      AND EXISTS (SELECT RAW 1 
                  FROM businesses b2 USE KEYS "info_" || b1.businessInfo);

OR

SELECT b1.*
FROM businesses AS b1
JOIN businesses AS b2 ON KEYS "info_" || b1.businessInfo
WHERE b1.category = 'hardware';

Upvotes: 1

Related Questions