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

I've added my simple attempt to the question.

J. Minjire
– J. Minjire

2020-09-22 09:28:30 +00:00
Commented Sep 22, 2020 at 9:28 — J. Minjire
– J. Minjire, Commented Sep 22, 2020 at 9:28

critical_error · Accepted Answer · 2020-09-23 02:03:57Z

3

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
        }]
    }]
}]

answered Sep 23, 2020 at 2:03

critical_error

6,8263 gold badges16 silver badges17 bronze badges

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

2 Comments

J. Minjire Over a year ago

Perfect. This solves my issue as is. I had figured I may have to do a distinct select first on Type but thought there must be a better way to do the outer list, guess I was not that far off. So this has to be repeated for any outer nesting that needs to be done? e.g if I wanted the json to first be by State, City then Type, etc, I'd have to do the same as above?

critical_error Over a year ago

You should be able to include your State and City with the DISTINCT for type and then modify the WHERE of the OUTER APPLY to something lke WHERE x.[Type] = t.[Type] AND x.State = t.State AND x.City = t.City

gofr1 · Accepted Answer · 2020-09-23 19:19:35Z

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
                    }
                ]
            }
        ]
    }
]

This also works thanks. Unfortunately my data has already been staged from the main database, so have to work with it as is.

Collectives™ on Stack Overflow

Convert flat SQL rows into nested JSON array using FOR JSON

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related