How can i access (query) Object in Array of another Object Array

SQL++
7

Ok i tried your code and the Append works as expected, the folowing which was supposed to set the default to no for a given email in array does not.

here is the code i ran for N1QL

UPDATE default AS d
SET e.default = "no"
FOR p IN d.person
FOR e IN p.email
WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END ) AND e.default = "yes" END;

here is the sample doc i tried it against
{
“person”: [
{
“dob”: “07/12/1978”,
“email”: [
{
“default”: “yes”,
“email_address”: "mymail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe12”
}
],
“first_name”: “Frank”,
“gender”: “m”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850”,
“last_name”: “Smith”,
“middle_name”: “J”,
“suffix”: “II”,
“title”: “Dr.”
},
{
“dob”: “07/12/1978”,
“email”: [
{
“default”: “yes”,
“email_address”: "noemail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe12”
}
],
“first_name”: “Susi”,
“gender”: “f”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4851”,
“last_name”: “Smith”,
“middle_name”: “M”,
“suffix”: “I”,
“title”: “”
}
],
“type”: “lead”
}

8

AND e.default =“yes” in wrong place (out of scope). It needs inside END

UPDATE default AS d
SET e.default = "no"
FOR e IN p.email
FOR p IN d.person
WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END;
9

i tried that but it still does not update the default key in the email. I even went this far as changing the where clause to

WHERE ANY p IN d.person SATISFIES p.id = “cc54bfc9-21de-4729-8ebb-b5ffcd6b4851” END;

in hope that would update all e.default for the given user to no but that did not work either. What are we missing here ? Also to minimize scan time how can i also add the doc id as a condition ?

10

FOR clause in SET needs to be intercahnged

UPDATE default AS d 
SET e.default = "no"
FOR e IN p.email
FOR p IN d.person
WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END;
1 Like
11

Thanks this works, final question how would I add to this query if I want to also filter by docid ?

12

If you already know docid replace docid in USE KEYS

UPDATE default AS d  USE KEYS ["docid"]
SET e.default = "no"
        FOR e IN p.email
                FOR p IN d.person
                     WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END;

If you don’t know it and want to search and update all qualified documents add condition to WHERE clause. example docids starts with “person::”

 UPDATE default AS d  
  SET e.default = "no"
            FOR e IN p.email
                    FOR p IN d.person
                         WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" 
                                      AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
    WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
        (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END
         META(d).id LIKE "person::%";
13

when i think i finally figured it out i find out that my json doc will not work because of limitations in the SDK. The SDK does not allow access based on a key in path but needs to get the full path. That said i will have to change my
doc to store emails to something like this where the id is the element name
and i can create a path like email.4416e4cc-89df-4537-b86f-a417614ebe12.default.

how would this impact my N1QL query if i wanted to list all email_address in query since ?
I am just trying to see if i can find a midle ground which allows me to use both SDK and N1QL
based on each limitations

This s what the doc would have to look like to meet the SDK subdoc requirments
{
“person”: [
{
“cc54bfc9-21de-4729-8ebb-b5ffcd6b4850”: {
“dob”: “07/12/1978”,
“email”: [
{
“4416e4cc-89df-4537-b86f-a417614ebe12”: {
“default”: “yes”,
“email_address”: "mymail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe12”
}
}
],
“first_name”: “Frank”,
“gender”: “m”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850”,
“last_name”: “Smith”,
“middle_name”: “J”,
“suffix”: “II”,
“title”: “Dr.”
}
},
{
“cc54bfc9-21de-4729-8ebb-b5ffcd6b4852”: {
“dob”: “07/12/1978”,
“email”: [
{
“4416e4cc-89df-4537-b86f-a417614ebe13”: {
“default”: “soso”,
“email_address”: "noemail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe13”
}
}
],
“first_name”: “Sam”,
“gender”: “f”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4852”,
“last_name”: “Smith”,
“middle_name”: “M”,
“suffix”: “I”,
“title”: “”
}
}
],
“type”: “lead”
}

14 
INSERT INTO default VALUES ("p2",{ "person": [ { "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850": { "dob": "07/12/1978", "email": [ { "4416e4cc-89df-4537-b86f-a417614ebe12": { "default": "yes", "email_address": "mymail@email.com", "id": "4416e4cc-89df-4537-b86f-a417614ebe12" } } ], "first_name": "Frank", "gender": "m", "id": "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850", "last_name": "Smith", "middle_name": "J", "suffix": "II", "title": "Dr." } }, { "cc54bfc9-21de-4729-8ebb-b5ffcd6b4852": { "dob": "07/12/1978", "email": [ { "4416e4cc-89df-4537-b86f-a417614ebe13": { "default": "soso", "email_address": "noemail@email.com", "id": "4416e4cc-89df-4537-b86f-a417614ebe13" } } ], "first_name": "Sam", "gender": "f", "id": "cc54bfc9-21de-4729-8ebb-b5ffcd6b4852", "last_name": "Smith", "middle_name": "M", "suffix": "I", "title": "" } } ], "type": "lead" });

UPDATE default AS d  USE KEYS ["p2"]
SET e.["4416e4cc-89df-4537-b86f-a417614ebe12"].default = "no"
        FOR e IN  p.["cc54bfc9-21de-4729-8ebb-b5ffcd6b4850"].email
                FOR p IN d.person END
WHERE ANY p IN d.person SATISFIES (ANY e IN p.["cc54bfc9-21de-4729-8ebb-b5ffcd6b4850"].email SATISFIES e.["4416e4cc-89df-4537-b86f-a417614ebe12"].default = "yes" END) END;
15

thanks for all the help but this is getting worse by the minute. Maybe the Doc based approach is not ready for what i am trying to do. A simple query in SQL like
update email_tbl set default = ‘no’ where person_id = xxx and email_id = yyy turns into a piece of art. Before this design change in Doc due to sdk limitations i was able to UNNEST person and Email and was able to get a list of email that does no longer work either , how would one go about that as the person and email are still arrays but now have a key value as key

16

You can also do this way.

   UPDATE default AS d  USE KEYS ["p2"]
    SET e.`4416e4cc-89df-4537-b86f-a417614ebe12`.default = "no"
            FOR e IN  p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4850`.email
                    FOR p IN d.person END
    WHERE ANY p IN d.person SATISFIES (ANY e IN p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4850`.email
                                       SATISFIES e.`4416e4cc-89df-4537-b86f-a417614ebe12`.default = "yes" END)
    END;

OR

Always update document even though no change.

 UPDATE default AS d  USE KEYS ["p2"]
        SET e.`4416e4cc-89df-4537-b86f-a417614ebe12`.default = "no"
                FOR e IN  p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4850`.email
                        FOR p IN d.person END;
17

Thanks but I guess we had a misunderstanding , i get the update part as I will have the key value for p and e abt that point but my question was more how would I get all key values and associated email address as at that point I do not know the person Id or it’s associated email id. That’s why I mentioned the basic unnest no longer works.

18

posted over here Subdocument operations

SELECT  pp.name AS pid , ep.name AS eid, ep.val.*
    FROM default AS d
    UNNEST d.person AS p
     UNNEST OBJECT_PAIRS(p) AS pp
    UNNEST pp.val.email AS e
    UNNEST  OBJECT_PAIRS(e) AS ep;
19

Thanks I saw it. Is there any plans to enhance the kv sdk to allow to update a array based on a value in it ? And if I retrieve a array via subdoc operation in sdk is there a way to get the actual array index for the returned object ?
Based on what i read the sdk kv aproach is way faster as it does not use index but N1QL is so much more flexible ?

20

If you know document key SDK operation is much faster. If you need flexible and don’t know document key N1QL is better. I don’t know about roadmap for support.

21

Another option is use WITHIN.

SELECT  e.*
FROM default AS d USE KEYS "p2"
UNNEST (ARRAY v FOR v WITHIN d WHEN v.email_address IS NOT MISSING END) AS e;

Adding “type”: “email” in email objects and “type”:“people” in people object gives more flexibility.

SELECT  e.*
FROM default AS d USE KEYS "p2"
UNNEST (ARRAY v FOR v WITHIN d WHEN v.type = "email" END) AS e;

 SELECT  p.*, e.*
 FROM default AS d USE KEYS "p2"
 UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = "people" END) AS p
 UNNEST (ARRAY v FOR v WITHIN p WHEN v.type = "email" END) AS e;
22

Ok i made some changes to my doc as per your suggestion and the following query returns me the phone nbr’s for a given person in a lead doc

SELECT e.*
FROM default AS d USE KEYS "leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3"
UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = "person" END) AS p
UNNEST (ARRAY v FOR v WITHIN p WHEN v.type = "phone" END) AS e
where p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850"

the only question is now how do i get access to the prefixed id as the SDK requires to do subdoc operations. In my case the nbr is stored like this in doc

      {
        "1234": {
          "type": "phone",
          "id": 2,
          "country": "+1",
          "phone_number": 2135554444,
          "ext": 455,
          "nbrtype": "office",
          "sms": "no",
          "default": "yes"
        }}

but the query returns only

         {
          "type": "phone",
          "id": 2,
          "country": "+1",
          "phone_number": 2135554444,
          "ext": 455,
          "nbrtype": "office",
          "sms": "no",
          "default": "yes"
        }

so how do i get access to the 1234 in my query either to get value or as a search criteria ? i dont know the value
which is a key field but changes for every record. If there is no way to access it i will have to store it also in the actual object. I am planning to use N1QL to query the bucket and return info to user and then use the subdoc
function in SDK to update contact objects in doc. But for that i need the 1234 as it is part of the path. Actually i also would need the key for the person as the full path would be something like this

bucket.mutateIn('leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3').remove('person[{personkey}].phone[{phonekey}]')
23

I thought you are going to use id as field name. If not it is difficult to get with WITHIN you need to use previous OBJECT_PAIRS() query.

Also following is more efficient

UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = “person” AND v1.id = “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850” END) AS p

24

Ok i tried

SELECT e.*FROM default AS d USE KEYS "leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3"
UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = "person" WHEN v1.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" END) AS p
UNNEST (ARRAY v FOR v WITHIN p WHEN v.type = "phone" END) AS e

which does not work as it throws the following error

  {
  "code": 3000,
  "msg": "syntax error - at WHEN",
  "query_from_user": "SELECT e.*\r\nFROM default AS d USE KEYS \"leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3\"\r\nUNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = \"person\" WHEN v1.id = \"cc54bfc9-21de-4729-8ebb-b5ffcd6b4850\" END) AS p\r\nUNNEST (ARRAY v FOR v WITHIN p WHEN v.type = \"phone\" END) AS e"
  }

So to make all this work efficiently i will store the key of the object also inside the object as well as tag them with a type to make filtering easyer

25

There are two WHEN replace second one with AND

26

Thanks i did quick copy paste and didn’t see that. But yes it works as hoped…