CODEWITHSUNDEEP
sql-serverjson
Francesco
Francesco

Reputation: 147

SQL Server 2016 "FOR JSON" formatting nested JSON

I'm trying to aggregate some nested JSON data using the FOR JSON clause supported by the latest version of SQL Server.

Let's say I write a function that uses the clause to export a piece of JSON data. Since no JSON type exists, this function returns an NVARCHAR(MAX) value.

Now suppose I wanted to use this piece of JSON as a value for a field in another function that returns JSON data through the FOR JSON clause.

Unfortunately I see no way to do this, other than storing it as a string. This effectively breaks nesting of JSON data.

For example, take this function that exports an array of JSON objects such as (directly from the Microsoft examples):

CREATE FUNCTION GetSalesOrderDetails(@salesOrderId int)  
RETURNS NVARCHAR(MAX)  
AS  
BEGIN  
  RETURN (SELECT UnitPrice, OrderQty  
          FROM Sales.SalesOrderDetail  
          WHERE SalesOrderID = @salesOrderId  
          FOR JSON AUTO)
END

Now if you tried to use this function inside another FOR JSON clause SELECT:

SELECT H.*, dbo.GetSalesOrderDetails(H.SalesOrderId) AS Details  
FROM Sales.SalesOrderHeader H
FOR JSON AUTO

Would produce something like:

[
  {
    "Id": 12,
    "Details": "[{\"UnitPrice\":12, \"OrderQty\":1}]"
  }
]

As you can see, the result of this query couldn't be parsed in one go, but would require a second parsing of the Details field.

Is there a way to tell the FOR JSON clause to interpret a field as a JSON array or a JSON object?

Upvotes: 2

Views: 2476

Answers (1)

Mono
Mono

Reputation: 330

The built in function JSON_QUERY extracts an object or an array from a JSON string.

This query should work.

SELECT
    H.*, 
    JSON_QUERY( dbo.GetSalesOrderDetails(H.SalesOrderId) ) AS Details
FROM Sales.SalesOrderHeader H
FOR JSON AUTO

`

Upvotes: 3

Related Questions