Subdocument operations

Node.js SDK
1

OK here is my sample scenario, i have Docs which store Leads, each lead can have more then 1 Person object and each person object can have 1 or more phone or email objects. So my thing is i want to be able to do the folowing after i created the initial lead is to update person or add new phone or email to given person. i dont want to use the index in array to upsert but use an id in my object.

here is a sample doc
{
“type”: “Lead”,
“person”: [
{
“id”: 1,
“first_name”: “Tom”,
“middle_name”: “M”,
“last_name”: “Miller”,
“title”: “Dr.”,
“suffix”: “II”,
“gender”: “m”,
“dob”: “07/12/1978”,
“email”: [
{
“id”: 1,
“email_address”: "tom@email.com",
“default”: “yes”
},
{
“id”: 2,
“email_address”: "tom@email.com"
}
],
“phone”: [
{
“id”: 1,
“country”: “+1”,
“phone_number”: 2135551212,
“ext”: “”,
“type”: “mobile”,
“sms”: “yes”,
“default”: “yes”
},
{
“id”: 2,
“country”: “+1”,
“phone_number”: 2135554444,
“ext”: 455,
“type”: “office”,
“sms”: “no”,
“default”: “yes”
}
]
}
],
“home_address”: {
“street_address”: “28812 Aloma Ave”,
“city”: “Laguna Niguel”,
“state”: “CA”,
“zip”: 92677,
“country”: “US”
},
“search_info”: {
“min_beds”: 3,
“min_bath”: 2.5,
“min_garage”: 2,
“garage_type”: “any”,
“pool”: “yes”,
“min_sqft”: 2500,
“max_sqft”: 3500,
“min_lot_size”: 10000,
“max_lot_size”: 15000,
“max_storey”: 1,
“notes”: “Wants a fixer upper”
}

}

my question is how would i access the path of dob for the person with id =1 or how would i access the email object for person id=1 and email id =2. I have not found any good info on that and wonder if this is even possible.

2

The Sub-doc API only allows you to lookup array elements by the element number, but if you re-structure your data model to use a dictionary you can then lookup by the dictionary path.

For example, change your model to something like:

{
  "type": "Lead",
  "people": {
    "1": {
      "first_name": "Tom",
      "middle_name": "M",
      "last_name": "Miller",
      "title": "Dr.",
      "suffix": "II",
      "gender": "m",
      "dob": "07/12/1978",
      "emails": {
        "1": {
          "email_address": "tom@email.com",
          "default": "yes"
        },
        "2": {
          "email_address": "tom@email.com"
        }
      },
      "phones": {
        "1": {
          "country": "+1",
          "phone_number": 2135551212,
          "ext": "",
          "type": "mobile",
          "sms": "yes",
          "default": "yes"
        },
        "2": {
          "country": "+1",
          "phone_number": 2135554444,
          "ext": 455,
          "type": "office",
          "sms": "no",
          "default": "yes"
        }
      }
    }
  },
  "home_address": {
    "street_address": "28812 Aloma Ave",
    "city": "Laguna Niguel",
    "state": "CA",
    "zip": 92677,
    "country": "US"
  },
  "search_info": {
    "min_beds": 3,
    "min_bath": 2.5,
    "min_garage": 2,
    "garage_type": "any",
    "pool": "yes",
    "min_sqft": 2500,
    "max_sqft": 3500,
    "min_lot_size": 10000,
    "max_lot_size": 15000,
    "max_storey": 1,
    "notes": "Wants a fixer upper"
  }
}

Note that now you’re using dictonaries for elements which have an identifier, you can use a subdoc path like: people.1.emails to lookup the emails element for person “1”.

1 Like
3

Thanks i get that concept but i am not sure if this will not break my N1QL queries as i no longer could run
something like this

select e.id,e.default,e.email_address from default AS d
unnest d.person p
unnest p.email e
WHERE p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851"
4

email object fields are dynamic and you want retrieve them you need to convert into OBJECT_VALUES()
OBJECT_VALUES(x), OBJECT_NAMES(x), OBJECT_PAIRS(x) converts x into array.

SELECT OBJECT_VALUES(e)[0].*
FROM default AS d
UNNEST d.person AS p
UNNEST p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4851`.email AS e;

OR

 SELECT e1.*
    FROM default AS d
    UNNEST d.person AS p
    UNNEST p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4851`.email AS e
    UNNEST  OBJECT_VALUES(e) AS e1;

OR

 SELECT ep.name, ep.val.*
    FROM default AS d
    UNNEST d.person AS p
    UNNEST p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4851`.email AS e
    UNNEST  OBJECT_PAIRS(e) AS ep;
5

Thanks I guess I will play around and see if that concept works for me. It just seems to be to much of a complication to be able to address the update via sdk for arrays as a subdoc operation. I was playing around with subdocs today getting the whole array, then searching the returned array for my key and then based on the array index do and subdoc update. Seems cleaner then this object mess to me. I guess I will have to ultimately decide if I want a clean N1QL or sdk kv.

6

Sure. Other options is store as separate document with parent id as filed and when needed use Joins like you do in relational database.