CODEWITHSUNDEEP
azureazure-cosmosdb
Todd Sussman
Todd Sussman

Reputation: 1

Selecting from an Array in DocumentDB

I have a document that looks like this in DocumentDB

{
  "profile": {
    "personName": {
      "namePrefix": XXX,
      "givenName": "XXX",
      "middleName": XXX,
      "surname": "XXX",
      "nameSuffix": XXX,
      "nameTitle": XXX
    },
    "telephones": {
      "telephone": [
        {
          "telephoneType": "Home",
          "isDefault": true,
          "countryAccessCode": 1,
          "areaCityCode": XXX,
          "phoneNumber": "XXX"
        },
        {
          "telephoneType": "Work",
          "isDefault": false,
          "countryAccessCode": 1,
          "areaCityCode": XXX,
          "phoneNumber": "XXX"
        }
      ]
    },
  }
  "id": "05d236f0-2970-4f04-9785-1c62c5ddbae5"

I am trying to work with Azure Search and flatten my data for the index. I would only like to include the telephone that is marked as isDefault = true.

I tried the following as well as trying ARRAY_CONTAINS but neither returned the expected results.

SELECT p.id, p.profile.personName.givenName, p.profile.personName.surname,   p.profile.telephones.telephone.countryAccessCode, p.profile.telephones.telephone.areaCityCode, p.profile.telephones.telephone.phoneNumber FROM Profiles p JOIN t IN p.profile.telephones.telephone WHERE t.isDefault = true

Upvotes: 0

Views: 1365

Answers (1)

Andrew Liu
Andrew Liu

Reputation: 8119

You can use the alias in your JOIN to pull out the array element.

For example:

SELECT p.id,
       p.profile.personName.givenName,
       p.profile.personName.surname,
       t.countryAccessCode,
       t.areaCityCode,
       t.phoneNumber
FROM Profiles p
JOIN t IN p.profile.telephones.telephone
WHERE t.isDefault = true

Upvotes: 2

Related Questions