I have an order table with products and order lines. Select Orders.Id, Customer.Id from Orders join Customer on Customer.Id = Orders.CustomerId where Order.Id = 1 for json auto This could return [{"Order": {"Id":1, "Customer": [{"Id": "10"}] } }]so far so good. If I want to extend the json with the OrderLines like Select Orders.Id, Customer.Id, OrderLines.Id from Orders join Customer on Customer.Id = Orders.CustomerId join OrderLines on OrderLines.OrderId = Orders.Id where Order.Id = 1 for json auto I get this result [{"Order": {"Id":1, "Customer": [{"Id": "10", "OrderLines": [{"Id":1},{"Id":2"}] }] } }]but I want {"Order": [{"Id":1, "Customer": {"Id": "10"}, "OrderLines": [{"Id":1},{"Id":2"}] }] Is there an easy way to do this? Or is the only way to do this using string concatenation? I know MS SQL has a solution for this. There it would look like: select Orders.Id ,Customer.Id as 'Customer.Id' ,(select OrderLines.Id from OrderLines where OrderLines.OrderId = Orders.Id for json path) as 'OrderLines' from Orders join Customer on Customer.Id = Orders.CustomerId join OrderLines on OrderLines.OrderId = Orders.Id where Order.Id = 1 for json path, root('Order') Does anyone know whether SAP is working on something like this? |