jsonsql-serverfor-json

Convert flat SQL rows into nested JSON array using FOR JSON


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

Solution

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