I have 1 order number which contains 4 skus, each sku is linked to 3 categories:
I need to write a SQL statement to convert this into JSON format which involves subquery and multiple elements in an array. Basically I need it to look like this:
{
"order_number": "WEB000000000",
"order_items": [
{
"sku": 111111111,
"categories": ["Checked Shirts", "Mens", "Shirts"]
},
{
"sku": 333333333,
"categories": ["Accessories & Shoes", "Mens Accessories", "Socks"]
},
{
"sku": 666666666,
"categories": ["Checked Shirts", "Mens", "Shirts"]
},
{
"sku": 999999999,
"categories": ["Nightwear", "Slippers", "Womens"]
}
]
}
Here's what I have so far but I just can't get it quite right:
DROP TABLE IF EXISTS ##Data;
CREATE TABLE ##Data
(
order_number varchar(100),
sku bigint,
categories varchar(100)
);
INSERT INTO ##Data
select 'WEB000000000', 111111111, 'Mens'
union all select 'WEB000000000', 111111111, 'Shirts'
union all select 'WEB000000000', 111111111, 'Checked Shirts'
union all select 'WEB000000000', 333333333, 'Accessories & Shoes'
union all select 'WEB000000000', 333333333, 'Mens Accessories'
union all select 'WEB000000000', 333333333, 'Socks'
union all select 'WEB000000000', 666666666, 'Mens'
union all select 'WEB000000000', 666666666, 'Shirts'
union all select 'WEB000000000', 666666666, 'Checked Shirts'
union all select 'WEB000000000', 999999999, 'Womens'
union all select 'WEB000000000', 999999999, 'Nightwear'
union all select 'WEB000000000', 999999999, 'Slippers'
SELECT * FROM ##Data;
select OUTER1.[order_number] as [order_number],
(select OSL.[order_number],
(select [sku],
(select [categories]
from ##Data skus
where order_item.[order_number] = skus.[order_number]
and order_item.[sku] = skus.[sku]
GROUP BY [categories]
FOR JSON PATH) as categories
from ##Data order_item
where order_item.[order_number] = OSL.[order_number]
GROUP BY [order_number], [sku]
FOR JSON PATH) as order_items
from ##Data OSL
where OSL.[order_number]=OUTER1.[order_number]
group by OSL.[order_number]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS JSON
from ##Data OUTER1
group by OUTER1.[order_number]
drop table ##Data
