How to exact match an array with the keys of a subdoc?

Hello,

I am using Couchbase Server CE 4.1.

I have the following document:

conversation::1

{
  "_id": "1",
  "_type": "conversation",
  "members": {
    "user_account::1": {
      "removed": false,
      "joined_at": 1472741410
    },
    "user_account::2": {
      "removed": false,
      "joined_at": 1472741410
    }
  },
  "started_at": 1472741410
}

Passing an array of [“user_account::1”,“user_account::2”], I want to get every conversation that exact matches the keys of the members subdocument in the doc.

No more, no less members. Exactly the same.

I have tried the following approach, as it would be something that “literally” made sense to me, but it didn’t work.

SELECT * FROM `messenger`
            WHERE _type = "conversation" AND
            EVERY ON KEYS account_id IN members SATISFIES account_id IN ["user_account::1", "user_account::2"] END

EDIT: After digging a bit, I’ve found out that the following does give me almost nearly results to what I want:

SELECT * FROM `messenger`
            WHERE _type = "conversation" AND
            OBJECT_NAMES(members) = ["user_account::1","user_account::3"]

However if I do change the order of the array being compared, it will not match. I have looked for an ARRAY_DIFF or ARRAY_INTERSECT, but it doesn’t look like N1QL does have that. Any ideas?


Any ideas on how could I get it working?

Thanks.

Hi @ivoecpereira
Could you try this.

SELECT * FROM default WHERE _type = "conversation" AND EVERY m IN OBJECT_NAMES(members) SATISFIES m IN ["user_account::1","user_account::3"] END;

Hello @vsr1,

Thanks for replying.

However that doesn’t seem to work.

Hi @ivoecpereira,

For sample document what is expected output.

insert into default values("c01",{ "_id": "1", "_type": "conversation", "members": { "user_account::1": { "removed": false, "joined_at": 1472741410 }, "user_account::2": { "removed": false, "joined_at": 1472741410 } }, "started_at": 1472741410 });
--0 documents return
SELECT * FROM default WHERE _type = "conversation" AND EVERY m IN OBJECT_NAMES(members) SATISFIES m IN  ["user_account::1","user_account::3"] END;
-- 1 document returned
SELECT * FROM default WHERE _type = "conversation" AND EVERY m IN OBJECT_NAMES(members) SATISFIES m IN  ["user_account::2","user_account::1"] END;
-- 1 document returned
SELECT * FROM default WHERE _type = "conversation" AND EVERY m IN OBJECT_NAMES(members) SATISFIES m IN  ["user_account::1","user_account::2"] END;

The array that you passing needs to be in document switch the values as follows.

SELECT * FROM default WHERE _type = "conversation" AND EVERY m IN ["user_account::1","user_account::2", "user_account::3"] SATISFIES m IN OBJECT_NAMES(members) END;

Also try this
SELECT * FROM default WHERE _type = "conversation" AND ARRAY_SORT(OBJECT_NAMES(members)) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::3","user_account::3"]));

This last attempt was exactly what I was looking for! I guess I still need more practice with these ARRAY functions :slight_smile:

Your other approaches did work too. However, this last one did really compare if the values matched the ones in members child, and that was what I was looking for.

The first attempt you sent worked too. I’ve misspelled the bucket name and tried it with the default one. Of course I haven’t got any results. Thanks.

Apart of that, how hard would it be to adding a “removed = false” to the condition for each member?

Thanks a lot @vsr1!

Hi @ivoecpereira

Adjust your data and try this.

SELECT default FROM default
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(members) WHEN  m.`value`.removed != false END
WHERE _type = "conversation" AND ARRAY_SORT(mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::1"]));

OR

SELECT default FROM default
LET mbrs = ARRAY n FOR n IN OBJECT_NAMES(members) WHEN  OBJECT_VALUES(members)[ARRAY_POS(OBJECT_NAMES(members),n)].removed != false END
WHERE _type = "conversation" AND ARRAY_SORT(mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::1"]));

Thanks a lot @vsr1.

It did work! Where can I find such queries so I can study them in detail?

Having a users bucket (apart of the messenger one that was being used in that query), how could I possibly join the result of mbrs with “users” bucket?

I’ve tried something like the following:

SELECT `messenger` FROM `messenger`
            JOIN users ON KEYS messenger.mbrs
            LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN  m.`value`.removed = false END
            WHERE messenger._type = "conversation" AND
            ARRAY_SORT(messenger.mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::5"])

I understand that possibly the variable wasn’t still declared at the time JOIN was made, but is it possible to JOIN later than variable declaration?

Thanks once more.

1 Like

Hi @ivoecpereira,

Variables are declared after join before applying predicate (you can see that through explain).
You can create subquery and Join or directly use your input which is same as ON KEYS

SELECT `messenger` FROM `messenger`
            JOIN users ON KEYS ARRAY_DISTINCT(["user_account::1","user_account::5"])
            LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN  m.`value`.removed = false END
            WHERE messenger._type = "conversation" AND
            ARRAY_SORT(mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::5"]));

OR

select q.m FROM (SELECT `messenger` m, mbrs FROM `messenger` 
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN  m.`value`.removed = false END
            WHERE messenger._type = "conversation" AND
            ARRAY_SORT(mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::5"])) as q users ON KEYS q.mbrs;

Also try just giving ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN m.value.removed = false END in ON KEYS.
As JOIN produces multiple records you may need aggregate them.
Note: mbrs is variable may not be part of messaenger object

SELECT q.messenger FROM (SELECT messenger, ARRAY_AGG(meta(users).id) mbrs FROM messenger
            JOIN users ON KEYS ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN  m.`value`.removed = false END
                                 WHERE messenger._type = "conversation" AND
                                 GROUP BY messenger) as q
                WHERE ARRAY_SORT(q.mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::5"]));

This one works great. Is it possible to NEST the members information inside the conversation? From what I’ve seen to do some NESTING, I would need to do the inverse query? Or am I missing it?

This one gives an error:

[
  {
    "code": 3000,
    "msg": "syntax error - at as",
    "query_from_user": "select q.m FROM (SELECT `messenger` m, mbrs FROM `messenger` \nLET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN  m.`value`.removed = false END\n            WHERE messenger._type = \"conversation\" AND\n            ARRAY_SORT(mbrs) = ARRAY_SORT(ARRAY_DISTINCT([\"user_account::1\",\"user_account::5\"])) as q users ON KEYS q.mbrs;"
  }
]

And this one gives an error at GROUP too:

Error:

[
{
“code”: 3000,
“msg”: “syntax error - at GROUP”,
“query_from_user”: “SELECT q.messenger FROM (SELECT messenger, ARRAY_AGG(meta(default).id) mbrs FROM messenger\n JOIN users ON KEYS ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN m.value.removed = false END\n WHERE messenger._type = "conversation" AND\n GROUP BY messenger) as q\n WHERE ARRAY_SORT(q.mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1"]));”
}
]

However the first query will do it perfectly I guess. However it would be great if I could NEST the results.

Thanks a lot for your patience and various examples!

Hi @ivoecpereira,
The following are correct queries( no syntax errors).
The members are already in messenger. If you need new ones in projection you can add it. You can nest what ever information you want.

SELECT messenger.*,mbrs FROM messenger
JOIN users ON KEYS ARRAY_DISTINCT(["user_account::1","user_account::5"])
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN m.value.removed = false END
WHERE messenger._type = "conversation" AND
ARRAY_SORT(mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::5"]));

If still need help post sample document and expected output.

select q.messenger FROM (SELECT messenger, mbrs FROM messenger
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN m.`value`.removed = false END
WHERE messenger._type = "conversation" AND
ARRAY_SORT(mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::5"]))) as q JOIN users ON KEYS q.mbrs;

SELECT q.messenger FROM (SELECT messenger, ARRAY_AGG(meta(users).id) mbrs FROM messenger
JOIN users ON KEYS ARRAY m.name FOR m IN OBJECT_PAIRS(messenger.members) WHEN  m.`value`.removed = false END
WHERE messenger._type = "conversation" GROUP BY messenger) as q
WHERE ARRAY_SORT(q.mbrs) = ARRAY_SORT(ARRAY_DISTINCT(["user_account::1","user_account::5"]));

If you need mbrs nested change above queries select q FROM (SELECT messenger.*, …

1 Like

Sorry for the late reply @vsr1, this week has been a chaos.

It is exactly that that I needed. I have some material there that I will use to study for other use cases.

Thanks a lot once again for your great contribution!

1 Like