CODEWITHSUNDEEP
sqljsonsql-serverjsonpathfor-json
Saiteja Sedate
Saiteja Sedate

Reputation: 55

Nested JSON path

I have a table in the following format -

orderNumber  |  orderName
-------------+------------
1            |  Pizza
1            |  Pasta

Using SQL query, I am expecting output in the following format -

{
    "data": [{
        "orderNumber":1,
        "orders": [
            {"orderName": "Pizza"},
            {"orderName": "Pasta"}
        ]
    }]
}

But my actual output is in the following format -

{
    "data": [
        {
            "orderNumber": 1,
            "orders" [
                {"orderName": "Pizza"}
            ]
        },
        {
            "orderNumber": 1,
            "orders": [
                {"orderName": "Pasta"}
            ]
        }
    ]
}

SQL query which I am using is -

SELECT
    (SELECT orderNumber as orderNumber,
        (SELECT orderName
         FROM sample_table b
         WHERE a.orderNumber = b.orderNumber
         FOR JSON PATH) AS orders
     FROM sample_table a FOR JSON PATH, ROOT('data')
    ) AS orderdata

Can anyone help me out?

Upvotes: 1

Views: 305

Answers (2)

pradeep sharma
pradeep sharma

Reputation: 1

--You Can use distinct query to do so.

create table #temp
(orderNumber varchar(20),
orderName varchar(20))

insert into #temp 
values('1','Pizza'),
('1','Pasta'),
('2','KFC'),('2','MACD')

select distinct(orderNumber),
   (select orderName 
    from #temp b 
    where a.orderNumber = b.orderNumber 
    for json path) as orders 
from #temp a 
for json path, root ('data')

Upvotes: 0

Serg
Serg

Reputation: 22811

You want a grouping query

select 
   orderNumber,
   (select orderName 
    from sample_table b 
    where a.orderNumber = b.orderNumber 
    for json path) as orders 
from sample_table a 
group by orderNumber
for json path, root ('data')

returns

{
    "data": [
        {
            "orderNumber": 1,
            "orders": [
                {
                    "orderName": "Pizza"
                },
                {
                    "orderName": "Pasta"
                }
            ]
        }
    ]
}

Upvotes: 1

Related Questions