1

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?

A.C.
  • 53
  • 1
  • 6
  • These answers helped! So the strange thing is that even though the N1QL result's allRows contains all the documents I wanted, the warnings/errors contains a lot of the same "Document key must be string: " warnings/errors. I see this when I debug the N1QL result. So even though I get the docs i need, I'm concerned that a lot of inefficient things are happening in the background. I'll share the debug output in the next comment. There are 5679 of them, but I truncated them in the comment for brevity. – A.C. Jun 24 '21 at 20:38
  • N1qlQueryResult{ status='success', finalSuccess=true, parseSuccess=true, allRows=[ { .... }, .., , .., { .... } ], signature={ "": "" }, info=N1qlMetrics{ resultCount=100, errorCount=0, warningCount=5679, ... }, profileInfo={ }, errors=[ { "msg": "Document key must be string: ", "code": 0 }, ..., { "msg": "Document key must be string: ", "code": 0 } ], ... } – A.C. Jun 24 '21 at 20:38

1 Answers1

1

|| 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';
vsr
  • 7,149
  • 1
  • 11
  • 10
  • if businessInfo isn't there, that also causes this error. Another predicate that might help is `businessInfo IS NOT MISSING' – Matthew Groves Jun 24 '21 at 13:50