title: CosmosDB SQL query that matches multiple values in an array
body: <p>I am working with Cosmos DB and I want to write a SQL query that will match multiple values in an array. To elaborate, imagine you have the following collection:</p>
  
  <pre><code>[
      {
          "id": "31d4c08b-ee59-4ede-b801-3cacaea38808",
          "name": "Oliver Queen",
          "occupations": [
              {
                  "job_title": "Billionaire",
                  "job_satisfaction": "pretty good"
              },
              {
                  "job_title": "Green Arrow",
                  "job_satisfaction": "meh"
              }
          ]
      },
      {
          "id": "689bdc38-9849-4a11-b856-53f8628b76c9",
          "name": "Bruce Wayne",
          "occupations": [
              {
                  "job_title": "Billionaire",
                  "job_satisfaction": "pretty good"
              },
              {
                  "job_title": "Batman",
                  "job_satisfaction": "I'm Batman"
              }
          ]
      },
      {
          "id": "d1d3609a-0067-47e4-b7ff-afc7ee1a0147",
          "name": "Clarke Kent",
          "occupations": [
              {
                  "job_title": "Reporter",
                  "job_satisfaction": "average"
              },
              {
                  "job_title": "Superman",
                  "job_satisfaction": "not as good as Batman"
              }
          ]
      }
  ]
  </code></pre>
  
  <p>I want to write a query that will return all entries that have an occupation with the job_title of "Billionaire" and "Batman". Just to be clear the results must have BOTH job_titles. So in the above collection it should only return Bruce Wayne.</p>
  
  <p>So far I have tried:</p>
  
  <pre><code>SELECT c.id, c.name, c.occupations FROM c
  WHERE ARRAY_CONTAINS(c.occupations, {'job_title': 'Billionaire' })
  AND ARRAY_CONTAINS(c.occupations, {'job_title': 'Batman' })
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT c.id, c.name, c.occupations FROM c
  WHERE c.occupations.job_title = 'Batman' 
  AND c.occupations.job_title = 'Billionaire'
  </code></pre>
  
  <p>Both of which returned empty results.</p>
  
  <p>Thanks in advance</p>