sql server for json

Question

I want something like this

{      "customer":[  
      {  
         "custid":1,
         "companyname":"Customer NRZBB",
         "order":[  
            {  
               "orderid":10643,
               "orderdate":"2015-08-25"
            },
            {  
               "orderid":10692,
               "orderdate":"2015-10-03"
            },
            {  
               "orderid":10702,
               "orderdate":"2015-10-13"
            }
         ]
      },
      {  
         "custid":2,
         "companyname":"Customer MLTDN",
         "order":[  
            {  
               "orderid":10308,
               "orderdate":"2014-09-18"
            },
            {  
               "orderid":10625,
               "orderdate":"2015-08-08"
            }
         ]
      }    ] }

but when I use for json path like below:

select
  customer.custid as [customer.custid], 
  customer.companyname as [customer.companyname],
  orders.orderid as [customer.order.orderid],
  orders.orderdate as [customer.order.orderdate] 
from
  sales.customers as customer 
    inner join sales.orders as orders on customer.custid = orders.custid
order by customer.custid,orders.orderid 
for json path

I got this:

 [      {  
       "customer":{  
          "custid":1,
          "companyname":"Customer NRZBB",
          "order":{  
             "orderid":10643,
             "orderdate":"2015-08-25"
          }
       }    },    {  
       "customer":{  
          "custid":1,
          "companyname":"Customer NRZBB",
          "order":{  
             "orderid":10692,
             "orderdate":"2015-10-03"
          }
       }    },    {  
       "customer":{  
          "custid":1,
          "companyname":"Customer NRZBB",
          "order":{  
             "orderid":10702,
             "orderdate":"2015-10-13"
          }
       }    }, {  
       "customer":{  
          "custid":2,
          "companyname":"Customer MLTDN",
          "order":{  
             "orderid":10308,
             "orderdate":"2014-09-18"
          }
       }    },    {  
       "customer":{  
          "custid":2,
          "companyname":"Customer MLTDN",
          "order":{  
             "orderid":10625,
             "orderdate":"2015-08-08"
          }
       }    },  } ]

It does not group customer information, then nest order information under that customer. Instead it is repeating the customer information for each order. The method that got me the first result is through for json auto, but I am wondering if I can get the same result from for json path.

select  
 customer.custid,   
  customer.companyname,
  orders.orderid,
  orders.orderdate 
from
  sales.customers as customer 
    inner join sales.orders as orders on customer.custid = orders.custid
order by customer.custid,orders.orderid 
for json auto

If so, how should I write the query?

Answer 1

Control the nesting using a correlated subquery:

select
customer.custid as [customer.custid], 
customer.companyname as [customer.companyname],
(
    SELECT orderid, orderdate 
    FROM sales.orders as o 
    WHERE o.custid = customer.custid 
    for json path
) AS [customer.order]
from
sales.customers as customer 
order by
    customer.custid
for json path;