Choosing an Object in an Array by searching on object attributes

SQL++
1 
Hi Folks,

Couchbase n00b here. 

I have a employee JSON document as shown below.

{
  "name": "John Smith",
  "addresses": [
    {
      "street": "220, Main Street",
      "city": "SFO",
      "contact": {
        "phone1": "123-123-2345",
        "phone2": "124-322-2356"
      }
    },
    {
      "street": "134, First Street",
      "city": "SJ",
      "contact": {
        "phone1": "408-123-2345"
      }
    }
  ]
}

What would the best way to find out "The contact information for the employee in SFO"?  
Note that I do not know the index of the SFO address in the addresses array.

I'm looking to get the sub-document via psuedo code like this. 

Give me the sub-document at `$.addresses["city":"SFO"].contact`.

This should return :-

{
    "phone1": "123-123-2345",
    "phone2": "124-322-2356"
}

Appreciate any pointers in this regard.

Thanks.
2

Hi, welcome to Couchbase :slightly_smiling:

SELECT b.name, a.contact
FROM mybucket b UNNEST b.addresses a
WHERE a.city = "SFO";
3

Thanks Gerald. Appreciate the help.

1 Like