sql - 将 SQL 输出格式化为自定义 JSON

Question

我有这张表，这个数据非常简单

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')

我想将输出格式化为如下所示：

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

我几乎拥有它，但我不知道如何格式化以获取“PerDate”中的数组。这就是我所拥有的

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')

我试过了FOR JSON AUTO，什么都没试过，我试过了，JSON_QUERY()但我没能达到我想要的结果。

每一个帮助将不胜感激。

谢谢

标签： sqljsonsql-server

Answer 1

这是一种方法

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.SizeId = s.SizeId
                          GROUP BY p.Date,
                                   p.Price
                          ORDER BY p.Date
                          FOR JSON PATH
                      ) AS PerDate
             FROM     #Prices AS s
             WHERE    s.ProductId = m.ProductId
             GROUP BY s.SizeId
             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')

输出：

    {
    "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": 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": 2,
                    "PerDate":
                    [
                        {
                            "Date": "2020-01-01",
                            "Price": 100
                        },
                        {
                            "Date": "2020-02-01",
                            "Price": 100
                        }
                    ]
                }
            ]
        }
    ]
}