title: Cosmos DB SQL Query for nested objects
body: <p>I have accounts collection in Cosmos DB. I tried different queries but failed what will be equivalent SQL Query to fetch only accounts which has selected subscription.</p>
  
  <p><strong>I tried this Query but failed</strong></p>
  
  <pre><code>SELECT     *
  FROM account a
  JOIN s IN c.subscriptions
  WHERE s.id = "e5969a3c-2729-cb3c-a01b-2e62e0473646"
  </code></pre>
  
  <p><strong>Account Collection Records</strong></p>
  
  <pre><code>[
    {
      "id": "8c549b95-480e-47f9-acd6-13339179399f",
      "odoo_id": "UpdatedDAta",
      "entity_name": "Lakes High School123",
      "entity_type": "family | teacher | school | district",
      "contacts": [
        {
          "name": "Mr. Garcia1",
          "email": "Garcia@junk.com"
        },
        {
          "name": "Mr. Garcia3",
          "email": "Garcia@junk.com"
        }
      ],
      "subscriptions": [
        {
          "id": null,
          "type": "group | profile",
          "group_name": "Year 4",
          "teachers": [
            "Ms Jones"
          ],
          "start_date": "25/7/2018",
          "end_date": "24/7/2019",
          "seats": 4,
          "group_key": "red-limping-pigeon"
        },
        {
          "id": "e5969a3c-2729-cb3c-a01b-2e62e0473646",
          "type": "group | profile",
          "group_name": "Year 4",
          "teachers": [
            "Ms Jones",
            "Waqar"
          ],
          "start_date": "25/7/2018",
          "end_date": "24/7/2021",
          "seats": 4,
          "group_key": "red-limping-pigeon"
        }
      ],
      "_rid": "bjcNANQrW3oGAAAAAAAAAA==",
      "_self": "dbs/bjcNAA==/colls/bjcNANQrW3o=/docs/bjcNANQrW3oGAAAAAAAAAA==/",
      "_etag": "\"01001c87-0000-0000-0000-5b7966850000\"",
      "_attachments": "attachments/",
      "_ts": 1534682757
    }
  ]
  </code></pre>