title: Cosmos DB Query Projection
body: <p>I have Cosmos Document of below format:</p>
  
  <p>(<strong>Note</strong>: Example is just for illustration, the actual document has different properties)</p>
  
  <pre><code>{
          "OrderNumer":"12345",
          "Version":"5",
          "OrderDetails": [ 
              {
                  "id":1,
                  "OrderItem": {
                      "Name": "ABC",
                      "Description": "ABC Description",
                       .
                       .
                       .
                  },
                  "PaymentDetails": {
                  },
                  .
                  .
                  .
              },
              {
                  "id":2,
                  "OrderItem": {
                      "Name": "PQR",
                      "Description": "PQR Description",
                       .
                       .
                       .
                  },
                  "PaymentDetails": {
                  },
                  .
                  .
                  .
              },
          ],
          "OrderDate": "12-01-2020",
          "CustomerDetails": {
           }
          .
          .
          .
  }
  </code></pre>
  
  <p><code>OrderNumber</code> is a partition key.</p>
  
  <p>I'm trying to project the above document through Cosmos Query to get the entire <code>OrderDetails</code> JSON object and a few properties from the parent into a single JSON with a filter on <code>OrderDetails</code>-> <code>id</code>.</p>
  
  <p>The expected projection should be:</p>
  
  <pre><code>{
      "Order":
      [
          {
             "OrderNumer":"12345",
              "Version":"5",
              "id":1,
              "OrderItem": {
                  "Name": "ABC",
                  "Description": "ABC Description",
                   .
                   .
                   .
              },
  
              "PaymentDetails": {
              },
              .
              .
              .
          },
          {
              "OrderNumer":"12345",
              "Version":"5",
              "id":2,
              "OrderItem": {
                  "Name": "PQR",
                  "Description": "PQR Description",
                   .
                   .
                   .
              },
              "PaymentDetails": {
              },
              .
              .
              .
          },
      ]
  }
  </code></pre>
  
  <p>As shown in the above JSON, I'm projecting <code>OrderNumber</code> and <code>Version</code> properties within the <code>OrderDetails</code> as <code>Order</code>.</p>
  
  <p><code>OrderDetails</code> is a massive JSON with a dynamic schema that makes it difficult for us to project its properties individually.</p>
  
  <p>I have tried a few options to project this, but the closest I could reach was below query:</p>
  
  <pre><code>SELECT c.OrderNumber, c.Version, o as Order
  FROM c
  JOIN o in c.OrderDetails WHERE
  c.OrderNumber= '1235' AND ARRAY_CONTAINS([1,2], o.id)
  </code></pre>
  
  <p>However, the above query does not give desired result as it keeps the <code>OrderNumber</code> and <code>Version</code> in as separate property to <code>Order</code>.</p>
  
  <p>Is there any way I can achieve this?</p>