Convert flat SQL rows into nested JSON array using FOR JSON

Question

So, I have a simple view that looks like this:

Name    | Type        | Product     | QuantitySold
------------------------------------------------------
Walmart | Big Store   | Gummy Bears | 10
Walmart | Big Store   | Toothbrush  | 6
Target  | Small Store | Toothbrush  | 2

Without using nested queries, using sql's FOR JSON clause, can this be easily converted to this json.

[
  {
    "Type": "Big Store",
    "Stores": [
      {
        "Name": "Walmart",
        "Products": [
          {
            "Name": "Gummy Bears",
            "QuantitySold": 10
          },
          {
            "Name": "Toothbrush",
            "QuantitySold": 6
          }
        ]
      }
      ]
  },
  {
    "Type": "Smaller Store",
    "Stores": [
      {
        "Name": "Target",
        "Products": [
          {
            "Name": "Toothbrush",
            "QuantitySold": 2
          }
        ]
      }
      ]
  }
]

Essentially Group by Type, Store then, line items. My attempt so far below. Not sure how to properly group the rows.

SELECT Type, (
    SELECT Store,
        (SELECT Product,QuantitySold from MyTable m3 where m3.id=m2.id for json path) as Products
    FROM MyTable m2 where m1.ID = m2.ID for json path) as Stores
) as Types FROM MyTable m1

Answer 1

You can try something like this:

DECLARE @Data TABLE (
    Name VARCHAR(20), Type VARCHAR(20), Product VARCHAR(20), QuantitySold INT
);

INSERT INTO @Data ( Name, Type, Product, QuantitySold ) VALUES
    ( 'Walmart', 'Big Store', 'Gummy Bears', 10 ),
    ( 'Walmart', 'Big Store', 'Toothbrush', 6 ),
    ( 'Target', 'Small Store', 'Toothbrush', 2 );

SELECT DISTINCT
    t.[Type],
    Stores
FROM @Data AS t
OUTER APPLY (
    
    SELECT (
        SELECT DISTINCT [Name], Products FROM @Data x
        OUTER APPLY (
            SELECT (
                SELECT Product AS [Name], QuantitySold FROM @Data n WHERE n.[Name] = x.[Name]
                    FOR JSON PATH
            ) AS Products
        ) AS p
        WHERE x.[Type] = t.[Type]
        FOR JSON PATH
    ) AS Stores

) AS Stores
ORDER BY [Type]
FOR JSON PATH;

Returns

[{
    "Type": "Big Store",
    "Stores": [{
        "Name": "Walmart",
        "Products": [{
            "Name": "Gummy Bears",
            "QuantitySold": 10
        }, {
            "Name": "Toothbrush",
            "QuantitySold": 6
        }]
    }]
}, {
    "Type": "Small Store",
    "Stores": [{
        "Name": "Target",
        "Products": [{
            "Name": "Toothbrush",
            "QuantitySold": 2
        }]
    }]
}]

Answer 2

If you had normalized data structure you could use a another approach.

--Let's assume that Types are stored like this
DECLARE @Types TABLE (
    id int,
    Type nvarchar(20)
);

INSERT INTO @Types VALUES (1, N'Big Store'), (2, N'Small Store');

--Stores in separate table
DECLARE @Stores TABLE (
    id int,
    Name nvarchar(10),
    TypeId int
);

INSERT INTO @Stores VALUES (1, N'Walmart', 1), (2, N'Target', 2), 
(3, N'Tesco', 2); -- I added one more just for fun

--Products table
DECLARE @Products TABLE (
    id int,
    Name nvarchar(20)
);

INSERT INTO @Products VALUES (1, N'Gummy Bears'), (2, N'Toothbrush'), 
(3, N'Milk'), (4, N'Ball') -- Added some here

-- And here comes the sales
DECLARE @Sales TABLE (
    StoreId int,
    ProductId int,
    QuantitySold int
);

INSERT INTO @Sales VALUES (1, 1, 10), (1, 2, 6), (2, 2, 2), 
(3, 4, 15), (3, 3, 7); -- I added few more

Now we can join the tables a get result that you need

SELECT Type = Type.Type,
       Name = [Stores].Name,
       Name = Products.Product,
       QuantitySold = Products.QuantitySold
FROM (
    SELECT s.StoreId,
           p.Name Product,
           s.QuantitySold
    FROM @Sales s
    INNER JOIN @Products p 
        ON p.id = s.ProductId
) Products
INNER JOIN @Stores Stores
    ON Stores.Id = Products.StoreId
INNER JOIN @Types [Type]
    ON Stores.TypeId = [Type].id
ORDER BY Type.Type, [Stores].Name
FOR JSON AUTO;

Output:

[
    {
        "Type": "Big Store",
        "Stores": [
            {
                "Name": "Walmart",
                "Products": [
                    {
                        "Name": "Gummy Bears",
                        "QuantitySold": 10
                    },
                    {
                        "Name": "Toothbrush",
                        "QuantitySold": 6
                    }
                ]
            }
        ]
    },
    {
        "Type": "Small Store",
        "Stores": [
            {
                "Name": "Target",
                "Products": [
                    {
                        "Name": "Toothbrush",
                        "QuantitySold": 2
                    }
                ]
            },
            {
                "Name": "Tesco",
                "Products": [
                    {
                        "Name": "Ball",
                        "QuantitySold": 15
                    },
                    {
                        "Name": "Milk",
                        "QuantitySold": 7
                    }
                ]
            }
        ]
    }
]