Turn Rows into Multiple elements in JSON

Viewed 23

I have 1 order number which contains 4 skus, each sku is linked to 3 categories:

enter image description here

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

0 Answers
Related