N1QL search by id in subqueries

SQL++
,
1

Hello,
I am running Couchbase Server version 4.5.0-2601 Community Edition.
I have two buckets, ‘users’ and ‘items’, the latter having a field ‘user_id’ which is pointing to users.id.

If I run the query

select count(*) from users u where meta(u).id = '123456'

I get the desired result

[
  {
    "$1": 1
  }
]

and if I run the query

select count(*) from items i where i.user_id='123456'

I get the desired result

[
  {
    "$1": 7
  }
]

So, there are 7 items for that particular user. Therefore, if I run this query

select u.* from users u where exists (select user_id from items use keys meta(u).id)

I expect to get at least one result from the users table, the one for the user with documentId = 123456. Instead, I get 0 results:

{
  "results": [],
  "metrics": {
    "elapsedTime": "1m51.332833098s",
    "executionTime": "1m51.332770429s",
    "resultCount": 0,
    "resultSize": 0
  }
}

What am I doing wrong?

2

what do you want to get ?
do you want to JOIN users bucket with items bucket?
if this, you can use JOIN

or please show what do you want to query.

USE KEYS META(u).id means doc’s key in items is equal with doc’s key in users.

3

I want to get all the users that have at least an item.

(Actually, this is the simplified version: my final goal is to delete all users that do not have an item, doing something like
delete from users where id not in (select user_id from broadcasts),
which is the SQL version)

why “doc’s key in items”? I’m using meta(u).id, not meta(items).id

4

this mean META(items).id == META(u).id

How about this N1QL

DELETE FROM users WHERE META().id NOT IN (SELECT RAW user_id FROM broadcasts])

before DELETE, you can SELECT to verify, for example

SELECT * FROM users WHERE META().id NOT IN (SELECT RAW user_id FROM broadcasts])
1 Like
5

Yep, that was it! RAW was the magic word. Also, using meta() without anything in the brackets. Couldn’t find it anywhere.

Thank you!