title: How to use Join operation/ Subquery in CosmosDB
body: <p>I have a container in CosmosDB. It contains around 30 variables. CabId is one of the column/variable in that. I want to select duplicate values of CabId and delete those items completely for duplicate CabIds. PartitionKey is another variable in my item.
  One item will look like below.</p>
  <pre class="lang-json prettyprint-override"><code>{
       &quot;CabId&quot;: &quot;8dc2d0f4-d787-4e98-94d0-665a69bfbbf5&quot;,
       &quot;OS_FlightRing&quot;: &quot;*****&quot;,
       &quot;CPU_FamilyName&quot;: &quot;*****&quot;,
       &quot;Incident_FailureBucketId&quot;: &quot;*****&quot;,
       &quot;Geo_ISOCountryShortCode&quot;: &quot;cn&quot;,
       &quot;OS_Version&quot;: &quot;10.0.19042.985&quot;,
       &quot;PartitionKey&quot;: &quot;29997810-1199392862021-10-20&quot;
  }
  </code></pre>
  <p>I am using query like this for getting the individual items with duplicate CabId. But I am getting syntax error.</p>
  <pre class="lang-sql prettyprint-override"><code>Select * from e JOIN(
   SELECT * FROM( 
    (SELECT c.CabId,Count(c.CabId) AS CabCount
     FROM c
     where c.PartitionKey = &quot;29997810-1238691972021-10-25&quot;
     GROUP BY c.CabId)
   d WHERE d.CabCount &gt; 1))
  on d.CabId = e.CabId
  </code></pre>