Блог

Главная - Вопросы по программированию - Форматируйте выходные данные SQL в пользовательский JSON

Форматируйте выходные данные SQL в пользовательский JSON

#sql #json #sql-server

Вопрос:

У меня есть эта таблица, которая очень проста с этими данными

 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() но мне не удалось достичь желаемого результата.

Мы будем очень признательны за любую помощь.

Спасибо

Ответ №1:

К сожалению, SQL Server не имеет этой JSON_AGG функции, что означает, что обычно вам потребуется использовать несколько связанных подзапросов и продолжать повторное сканирование базовой таблицы.

Однако мы можем смоделировать это, используя STRING_AGG для отдельных объектов JSON, сгенерированных в an APPLY . Это означает, что мы сканируем базовую таблицу только один раз.

Использование JSON_QUERY без пути предотвращает двойной побег

 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<>скрипка

Ответ №2:

Это один из способов сделать это

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

Комментарии:

1. Похоже, вам не хватает WHERE m.ProductId = s.ProductId подзапроса первого уровня, и это означает, что вы продолжаете получать все результаты снова и снова

2. Спасибо. Спасибо, что заметили это. Я был так взволнован ответом, когда правильно подобрал формат. Исправил это.

Метки: