title: Problem with using of FOR JSON AUTO in SQL Server
body: <p>I am using <code>FOR JSON AUTO</code> in SQL server database, to convert my query's result to the JSON format.
  in my query, I joined order table to two other tables.</p>
  <pre><code>SELECT
      orders.[Code], orders.[Total], orders.[Discount], 
      customer.[Name], customer.[PhoneNumber], 
      store.[Name], store.[Address]
  FROM
      Orders orders 
  INNER JOIN
      Customers customer ON (orders.[CustomerID] = customer.[ID]) 
  INNER JOIN
      Stores store ON (orders.[StoreID] = store.[ID])
  FOR JSON AUTO 
  </code></pre>
  <p>Result:</p>
  <pre class="lang-json prettyprint-override"><code>[
    {
      &quot;Code&quot;: &quot;1528&quot;,
      &quot;Total&quot;: 5000,
      &quot;Discount&quot;: 20,
      &quot;customer&quot;: [
        {
          &quot;Name&quot;: &quot;Alex&quot;,
          &quot;PhoneNumber&quot;: &quot;(548) 123-5555&quot;,
          &quot;store&quot;: [
            {
              &quot;Name&quot;: &quot;Apple&quot;,
              &quot;Address&quot;: &quot;E. Santa rd&quot;
            }
          ]
        }
      ]
    },
    {
      &quot;Code&quot;: &quot;1687&quot;,
      &quot;Total&quot;: 3000,
      &quot;Discount&quot;: 10,
      &quot;customer&quot;: [
        {
          &quot;Name&quot;: &quot;John&quot;,
          &quot;PhoneNumber&quot;: &quot;(226) 354-7896&quot;,
          &quot;store&quot;: [
            {
              &quot;Name&quot;: &quot;Sony&quot;,
              &quot;Address&quot;: &quot;W. Atlantic ave&quot;
            }
          ]
        }
      ]
    }
  ]
  </code></pre>
  <p>But it's not correct, because in this scenario customer and store are sibling and they have same parent, and both of them joined with the order table directly, correct JSON must be such as this:</p>
  <pre class="lang-json prettyprint-override"><code>[
      {
          &quot;Code&quot;: &quot;1528&quot;,
          &quot;Total&quot;: 5000,
          &quot;Discount&quot;: 20,
          &quot;customer&quot;: [
              {
                  &quot;Name&quot;: &quot;Alex&quot;,
                  &quot;PhoneNumber&quot;: &quot;(548) 123-5555&quot;
              }
          ],
          &quot;store&quot;: [
              {
                  &quot;Name&quot;: &quot;Apple&quot;,
                  &quot;Address&quot;: &quot;E. Santa rd&quot;
              }
          ]
      },
      {
          &quot;Code&quot;: &quot;1687&quot;,
          &quot;Total&quot;: 3000,
          &quot;Discount&quot;: 10,
          &quot;customer&quot;: [
              {
                  &quot;Name&quot;: &quot;John&quot;,
                  &quot;PhoneNumber&quot;: &quot;(226) 354-7896&quot;
              }
          ],
          &quot;store&quot;: [
              {
                  &quot;Name&quot;: &quot;Sony&quot;,
                  &quot;Address&quot;: &quot;W. Atlantic ave&quot;
              }
          ]
      }
  ]
  </code></pre>
  <p>how can I do that? Are there any option for this in SQL? (I don't want to use inner select.)</p>