Dynamic values in JSON array query

Question

I have a common table expression query like this.

;WITH Orders AS (
                    SELECT  OrderStatus,
                            OrderID,
                            OrderedBy,
                            OrderItems
                    FROM    Orders
                    WHERE   StudentGuid = @StudentGuid
                  )

The desired JSON array output should look like this.

[
 {
  "status":"Accepted",
  "orders":[
     {
        "OrderID":"100-1000",
        "OrderedBy":"John Smith"
     },
     {
        "OrderID":"100-1001",
        "OrderedBy":"John Smith"
     }
  ]
},
{
  "status":"Rejected",
  "orders":[
     {
        "OrderID":"100-1002",
        "OrderedBy":"John Smith"
     }
  ]
}
]

My current query is;

SELECT CAST((
                SELECT * 
                FROM (
                        SELECT (SELECT 'Accepted') AS [OrderStatus],
                               (SELECT CAST((
                                            SELECT OrderID,
                                                   OrderedBy,
                                            FROM    Orders
                                            WHERE   [OrderStatus] = 'Accepted'
                                            FOR JSON PATH
                                            ) AS NVARCHAR(MAX))) AS Orders
                        UNION       
                        SELECT (SELECT 'Rejected') AS [OrderStatus],
                               (SELECT CAST((
                                            SELECT OrderID,
                                                   OrderedBy,
                                            FROM    Orders
                                            WHERE   [OrderStatus] = 'Rejected'
                                            FOR JSON PATH
                                            ) AS NVARCHAR(MAX))) AS Orders
                ) AS Temp FOR JSON PATH ) AS NVARCHAR(MAX))

The problem with my query is it is not dynamic (as status are hard coded) and "orders node" in the final outcome fail to parse by JSON Formatters due to "unions."

How can I remove the hardcoding and make it dynamic with the CTE?

Thanks!

Prosper · Accepted Answer · 2020-07-14 10:37:48Z

1

Looks like this can be done using subqueries.

    SELECT DISTINCT [status],
                    (SELECT orderID, 
                            orderedBy
                    FROM    Orders AS B
                    WHERE   [Status] = A.[Status]
                                FOR JSON PATH) AS Orders
    FROM Orders AS A

answered Jul 14, 2020 at 10:37

Prosper

981 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic values in JSON array query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related