Escaped for JSON nested nodes using union command

Question

In a stored procedure I have a for json node (boxes):

select
(
    select
        os.Name,
        os.Address,
        ss.carrierCode,
        (
            select 
                ob.envelopeCode, 
                ob.boxNumber,
                ob.weight,
                ob.width,
                ob.length,
                ob.height  
            from OrdersBoxes ob
            ...
            where os.OID=ob.OID 
            ...
            for json path 
        ) boxes,
        ....
        for json path
) orderDetails

In this way I correctly get:

"boxes":[{
            "envelopeCode":"E70345D2AB90A879D4F53506FB465086",
            "boxNumber":1,
            "weight":3000,
            "width":300,
            "length":300,
            "height":100
        }]

Now I need to get details from 2 tables, therefore I will use union command, wrap the 2 select in another select the query to avoid following error:

The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.

And add JSON_QUERY to avoid to get escaped nested node:

select
(
    select 
        * 
    from 
    (
        select
            os.Name,
            os.Address,
            ss.carrierCode,
            JSON_QUERY((
                select 
                    ob.envelopeCode, 
                    ob.boxNumber,
                    ob.weight,
                    ob.width,
                    ob.length,
                    ob.height  
                from OrdersBoxes ob
                ...
                where os.OID=ob.OID 
                ...
                for json path 
            )) boxes,
            ....
            from table1
            where....
        
        union
        
        select
            os.Name,
            os.Address,
            ss.carrierCode,
            JSON_QUERY((
                select 
                    ob.envelopeCode, 
                    ob.boxNumber,
                    ob.weight,
                    ob.width,
                    ob.length,
                    ob.height  
                from OrdersBoxes ob
                ...
                where os.OID=ob.OID 
                ...
                for json path 
            )) boxes,
            ....
            from table2
            where....
    ) jj
    for json path
) orderDetails

That works, but boxes node is returned escaped:

"boxes":"[{\"envelopeCode\":\"E70345D2AB90A879D4F53506FB465086\",\"boxNumber\":1,\"weight\":3000,\"width\":300,\"length\":300,\"height\":100}]"

I tried also this Solution but it works well only if returning data from 1 table:

since it returns objects {} to get an array need to change first line from

select STRING_AGG (order_details,',') ods from (

to

select concat('[',STRING_AGG (order_details,','),']') ods from (

and it seems me not very "elegant" although it works.

Can someone suggest a better way to get all data correctly formatted (thus unescaped boxes node)?

Answer 1

The documentation about JSON_QUERY() explains: ... JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON doesn't escape special characters in the JSON_QUERY return value. If you're returning results with FOR JSON, and you're including data that's already in JSON format (in a column or as the result of an expression), wrap the JSON data with JSON_QUERY without the path parameter.. So, if I understand the schema correctly, you need to use JSON_QUERY() differently:

Tables:

SELECT *
INTO table1
FROM (VALUES
   (1, 'Name1', 'Address1')
) v (oid, name, address)
SELECT *
INTO table2
FROM (VALUES
   (2, 'Name2', 'Address2')
) v (oid, name, address)
SELECT *
INTO OrdersBoxes 
FROM (VALUES
   (1, 'E70345D2AB90A879D4F53506FB465086', 1, 3000, 300, 300, 100),
   (2, 'e70345D2AB90A879D4F53506FB465086', 2, 3000, 300, 300, 100)
) v (oid, envelopeCode, boxNumber, weight, width, length, height)

Statement:

select Name, Address, JSON_QUERY(boxes) AS Boxes
from (
   select
      os.Name,
      os.Address,
      (
      select ob.envelopeCode, ob.boxNumber, ob.weight, ob.width, ob.length, ob.height  
      from OrdersBoxes ob
      where os.OID = ob.OID 
      for json path 
      ) boxes
   from table1 os
   union all
   select
      os.Name,
      os.Address,
      (
      select ob.envelopeCode, ob.boxNumber, ob.weight, ob.width, ob.length, ob.height   
      from OrdersBoxes ob
      where os.OID = ob.OID 
      for json path 
      ) boxes
   from table2 os
) j
for json path

As an additional option, you may try to use FOR JSON AUTO (the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables):

SELECT 
   cte.Name, cte.Address, 
   boxes.envelopeCode, boxes.boxNumber, boxes.weight, boxes.width, boxes.length, boxes.height
FROM (
   SELECT oid, name, address FROM table1
   UNION ALL
   SELECT oid, name, address FROM table2
) cte
JOIN OrdersBoxes boxes ON cte.oid = boxes.oid
FOR JSON AUTO

Result:

[
   {
   "Name":"Name1",
   "Address":"Address1",
   "boxes":[{"envelopeCode":"E70345D2AB90A879D4F53506FB465086","boxNumber":1,"weight":3000,"width":300,"length":300,"height":100}]
   },
   {
   "Name":"Name2",
   "Address":"Address2",
   "boxes":[{"envelopeCode":"e70345D2AB90A879D4F53506FB465086","boxNumber":2,"weight":3000,"width":300,"length":300,"height":100}]
   }
]