Home > OS >  Format SQL output to custom JSON
Format SQL output to custom JSON

Time:11-04

I have this table which is very simple with this data

CREATE TABLE #Prices 
(
    ProductId int,
    SizeId int,
    Price int,
    Date date
)

INSERT INTO #Prices 
VALUES (1, 1, 100, '2020-01-01'),
       (1, 1, 120, '2020-02-01'),
       (1, 1, 130, '2020-03-01'),
       (1, 2, 100, '2020-01-01'),
       (1, 2, 100, '2020-02-01'),
       (2, 1, 100, '2020-01-01'),
       (2, 1, 120, '2020-02-01'),
       (2, 1, 130, '2020-03-01'),
       (2, 2, 100, '2020-01-01'),
       (2, 2, 100, '2020-02-01')

I would like to format the output to be something like this:

{
    "Products": [
        {
            "Product": 2,
            "UnitSizes": [
                {
                    "SizeId": 1,
                    "PerDate": [
                        {
                            "Date": "2020-01-02",
                            "Price": 870.0
                        },
                        {
                            "Date": "2021-04-29",
                            "Price": 900.0
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate": [
                        {
                            "Date": "2020-01-02",
                            "Price": 435.0
                        },
                        {
                            "Date": "2021-04-29",
                            "Price": 450.0
                        }
                    ]
                }
            ]
        },
        {
            "Product": 4,
            "UnitSizes": [
                {
                    "SizeId": 1,
                    "PerDate": [
                        {
                            "Date": "2020-01-02",
                            "Price": 900.0
                        }
                    ]
                }
            ]
        }
    ]
}

I almost have it but I don't know how to format to get the array inside 'PerDate'. This is what I have

SELECT 
    ProductId AS [Product], 
    SizeId AS 'Sizes.SizeId', 
    date AS 'Sizes.PerDate.Date', 
    price AS 'Sizes.PerDate.Price'
FROM   
    #Prices
ORDER BY  
    ProductId, [Sizes.SizeId], Date
FOR JSON PATH, ROOT('Products')

I have tried with FOR JSON AUTO and nothing, I've tried with JSON_QUERY() but I was not able to achieve the result I want.

Every help will be very appreciated.

Thanks

CodePudding user response:

This is one way of doing it

DROP TABLE IF EXISTS #Prices

CREATE TABLE #Prices
(
    ProductId INT,
    SizeId    INT,
    Price     INT,
    Date      DATE
)

-- SQL Prompt formatting off
INSERT INTO #Prices 
VALUES (1, 1, 100, '2020-01-01'),
       (1, 1, 120, '2020-02-01'),
       (1, 1, 130, '2020-03-01'),
       (1, 2, 100, '2020-01-01'),
       (1, 2, 100, '2020-02-01'),
       (2, 1, 100, '2020-01-01'),
       (2, 1, 120, '2020-02-01'),
       (2, 1, 130, '2020-03-01'),
       (2, 2, 100, '2020-01-01'),
       (2, 2, 100, '2020-02-01')
-- SQL Prompt formatting on

SELECT   m.ProductId AS Product,
         (
             SELECT   s.SizeId,
                      (
                          SELECT   p.Date,
                                   p.Price
                          FROM     #Prices AS p
                          WHERE    p.ProductId = s.ProductId
                                   AND p.SizeId = s.SizeId
                          ORDER BY p.Date
                          FOR JSON PATH
                      ) AS PerDate
             FROM     #Prices AS s
             ORDER BY s.SizeId
             FOR JSON PATH
         ) AS UnitSizes
FROM     #Prices AS m
GROUP BY m.ProductId
ORDER BY m.ProductId
FOR JSON PATH, ROOT('Products')

Output:

{
    "Products":
    [
        {
            "Product": 1,
            "UnitSizes":
            [
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                }
            ]
        },
        {
            "Product": 2,
            "UnitSizes":
            [
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 1,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 120
                        },
                        {
                            "Date": "2020-03-01",
                            "Price": 130
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                },
                {
                    "SizeId": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                }
            ]
        }
    ]
}

CodePudding user response:

Unfortunately, SQL Server does not have the JSON_AGG function, which means you would normally need to use a number of correlated subqueries and keep on rescanning the base table.

However, we can simulate it by using STRING_AGG against single JSON objects generated in an APPLY. This means that we only scan the base table once.

Use of JSON_QUERY with no path prevents double-escaping

WITH PerDate AS (
    SELECT
      p.ProductId,
      p.SizeId,
      PerDate = '['   STRING_AGG(j.PerDate, ',') WITHIN GROUP (ORDER BY p.Date)   ']'
    FROM  #Prices AS p
    CROSS APPLY (  -- This produces multiple rows of single JSON objects
        SELECT p.Date, p.Price
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) j(PerDate)
    GROUP BY
      p.ProductId,
      p.SizeId
),
UnitSizes AS (
    SELECT
      p.ProductId,
      UnitSizes = '['   STRING_AGG(j.UnitSizes, ',') WITHIN GROUP (ORDER BY p.SizeId)   ']'
    FROM PerDate p
    CROSS APPLY (
        SELECT p.SizeId, PerDate = JSON_QUERY(p.PerDate)
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) j(UnitSizes)
    GROUP BY
      p.ProductId
)
SELECT
  Product = p.ProductId,
  UnitSizes = JSON_QUERY(p.UnitSizes)
FROM UnitSizes p
ORDER BY p.ProductId
FOR JSON PATH, ROOT('Products');

db<>fiddle

  • Related