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

ivoecpereira · September 12, 2016, 4:04pm

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.

vsr1 · September 12, 2016, 5:54pm

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;

ivoecpereira · September 12, 2016, 6:04pm

Hello @vsr1,

Thanks for replying.

However that doesn’t seem to work.

vsr1 · September 12, 2016, 6:09pm

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;

vsr1 · September 12, 2016, 6:40pm

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"]));

ivoecpereira · September 12, 2016, 8:10pm

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

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!

vsr1 · September 12, 2016, 9:12pm

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"]));

ivoecpereira · September 13, 2016, 9:45am

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.

vsr1 · September 13, 2016, 12:58pm

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;

vsr1 · September 13, 2016, 1:03pm

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"]));

ivoecpereira · September 14, 2016, 8:55am

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!

vsr1 · September 14, 2016, 12:59pm

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.*, …

ivoecpereira · September 15, 2016, 9:19pm

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!

Topic		Replies	Views
How to query documents which are arrays SQL++ n1ql	1	786	March 7, 2018
Query single value array SQL++ arrays	2	1562	February 21, 2020
N1QL retreive document with reference to other documents SQL++ query	4	2009	March 18, 2018
N1QL - How do you compare 2 document in N1QL and show only difference on all the attributes including array with simple N1QL statement Couchbase Server query , n1ql	2	998	September 22, 2020
Query on how to retrieve documents which satisfies a condition in nested array and matching value outside nested array Couchbase Server query , n1ql , index	5	401	February 21, 2024

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

Related topics