SQL-сервер ДЛЯ JSON

В этом руководстве вы узнаете, как использовать предложение SQL Server FOR JSON для форматирования результатов запроса в виде текста JSON.

Введение в предложение SQL Server FOR JSON

Предложение FOR JSON позволяет форматировать результат запроса как текст JSON.

Предложение FOR JSON может быть полезным, когда вы хотите извлечь данные из SQL Server и сериализовать их в формате JSON для использования приложениями или веб-службами.

Вот базовый синтаксис предложения FOR JSON:

SELECT select_list
FROM table_name
FOR JSON AUTO | PATH, extra_option;

В этом синтаксисе:

• Сначала укажите предложение FOR JSON в конце запроса.
• Во-вторых, укажите формат вывода JSON в формате JSON. FOR JSON AUTO автоматически форматирует JSON на основе набора запросов, тогда как FOR JSON PATH позволяет вам иметь полный контроль над форматом вывода JSON.

Следующие дополнительные параметры позволяют управлять выводом документа JSON:

• ROOT: Добавить один элемент верхнего уровня в вывод JSON. Если вы пропустите это, вывод JSON не будет иметь корневого элемента.
• INCLUDE_NULL_VALUES: Функция не включает NULL в выходной JSON. Если вы хотите преобразовать значения NULL в нулевые и включить их в выходной JSON, вы можете использовать опцию INCLUDE_NULL_VALUES.
• WITHOUT_ARRAY_WRAPPER: Используйте эту опцию для форматирования однострочного результата как объекта. В противном случае функция поместит объект JSON в массив JSON.

FOR JSON является аналогом функции OPENJSON(), которая преобразует документ JSON в строки и столбцы.

Примеры предложений SQL Server FOR JSON

Давайте рассмотрим несколько примеров использования предложения FOR JSON.

Мы будем использовать таблицы продуктов и категорий из примера базы данных :

1) Базовый пример предложения SQL Server FOR JSON

В следующем примере извлекаются 5 самых дорогих продуктов и форматируется результирующий набор как JSON с использованием предложения FOR JSON:

SELECT 
  TOP 5 product_id, 
  product_name, 
  list_price 
FROM 
  production.products 
ORDER BY 
  list_price DESC 
FOR JSON AUTO;

Выход:

[
  {
    "product_id": 155,
    "product_name": "Trek Domane SLR 9 Disc - 2018",
    "list_price": 11999.99
  },
  {
    "product_id": 149,
    "product_name": "Trek Domane SLR 8 Disc - 2018",
    "list_price": 7499.99
  },
  {
    "product_id": 51,
    "product_name": "Trek Silque SLR 8 Women's - 2017",
    "list_price": 6499.99
  },
  {
    "product_id": 156,
    "product_name": "Trek Domane SL Frameset - 2018",
    "list_price": 6499.99
  },
  {
    "product_id": 157,
    "product_name": "Trek Domane SL Frameset Women's - 2018",
    "list_price": 6499.99
  }
]

2) Использование предложения FOR JSON с указанным корневым документом

В следующем примере извлекаются 5 самых дорогих продуктов и форматируется результирующий набор с корневым документом в виде продуктов:

SELECT 
  TOP 5 product_id, 
  product_name, 
  list_price 
FROM 
  production.products 
ORDER BY 
  list_price DESC 
FOR JSON AUTO, ROOT('products');

Выход:

{
  "products": [
    {
      "product_id": 155,
      "product_name": "Trek Domane SLR 9 Disc - 2018",
      "list_price": 11999.99
    },
    {
      "product_id": 149,
      "product_name": "Trek Domane SLR 8 Disc - 2018",
      "list_price": 7499.99
    },
    {
      "product_id": 51,
      "product_name": "Trek Silque SLR 8 Women's - 2017",
      "list_price": 6499.99
    },
    {
      "product_id": 156,
      "product_name": "Trek Domane SL Frameset - 2018",
      "list_price": 6499.99
    },
    {
      "product_id": 157,
      "product_name": "Trek Domane SL Frameset Women's - 2018",
      "list_price": 6499.99
    }
  ]
}

3) Использование предложения FOR JSON с агрегатными функциями

В следующем примере извлекается категория и количество товаров в каждой категории, возвращая набор результатов в виде текста JSON:

SELECT 
  c.category_name, 
  COUNT(*) product_count 
FROM 
  production.categories c 
  INNER JOIN production.products p on c.category_id = p.category_id 
GROUP BY 
  c.category_name 
ORDER BY 
  product_count FOR JSON AUTO;

Выход:

[
  {
    "category_name": "Cyclocross Bicycles",
    "product_count": 10
  },
  {
    "category_name": "Electric Bikes",
    "product_count": 24
  },
  {
    "category_name": "Comfort Bicycles",
    "product_count": 30
  },
  {
    "category_name": "Children Bicycles",
    "product_count": 59
  },
  {
    "category_name": "Mountain Bikes",
    "product_count": 60
  },
  {
    "category_name": "Road Bikes",
    "product_count": 60
  },
  {
    "category_name": "Cruisers Bicycles",
    "product_count": 78
  }
]

4) Использование предложения FOR JSON с результатом в одну строку

В следующем примере извлекается продукт с идентификатором 1 из таблицы production.products и форматируется набор результатов как JSON с помощью предложения FOR JSON AUTO:

SELECT 
  product_id, 
  product_name, 
  list_price 
FROM 
  production.products 
WHERE 
   product_id = 1
FOR JSON AUTO;

Выход:

[
  {
    "product_id": 1,
    "product_name": "Trek 820 - 2016",
    "list_price": 379.99
  }
]

Выходной JSON представляет собой массив, состоящий из одного объекта JSON.

Если вы используете опцию WITHOUT_ARRAY_WRAPPER, предложение FOR JSON вернет вместо этого один объект JSON. Например:

SELECT 
  product_id, 
  product_name, 
  list_price 
FROM 
  production.products 
WHERE 
   product_id = 1
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

Выход:

{
  "product_id": 1,
  "product_name": "Trek 820 - 2016",
  "list_price": 379.99
}

5) Использование предложения FOR JSON для создания вложенных объектов

В следующем примере используется предложение FOR JSON для создания JSON, включающего заказы и позиции заказов клиента с идентификатором 1:

SELECT 
    o.order_id,
    o.order_status,
    o.order_date,
   (
        SELECT 
            oi.item_id,
            oi.product_id,
            oi.quantity,
            oi.list_price,
            oi.discount
        FROM 
            sales.order_items oi
        WHERE 
            oi.order_id = o.order_id
        FOR JSON PATH
    ) AS items
FROM 
    sales.orders o
WHERE 
    o.customer_id = 1
FOR JSON PATH;

Выход:

[
  {
    "order_id": 599,
    "order_status": 4,
    "order_date": "2016-12-09",
    "items": [
      {
        "item_id": 1,
        "product_id": 9,
        "quantity": 2,
        "list_price": 2999.99,
        "discount": 0.05
      },
      {
        "item_id": 2,
        "product_id": 22,
        "quantity": 2,
        "list_price": 269.99,
        "discount": 0.2
      },
      {
        "item_id": 3,
        "product_id": 23,
        "quantity": 1,
        "list_price": 299.99,
        "discount": 0.07
      },
      {
        "item_id": 4,
        "product_id": 10,
        "quantity": 2,
        "list_price": 1549,
        "discount": 0.1
      }
    ]
  },
  {
    "order_id": 1555,
    "order_status": 1,
    "order_date": "2018-04-18",
    "items": [
      {
        "item_id": 1,
        "product_id": 24,
        "quantity": 2,
        "list_price": 549.99,
        "discount": 0.1
      },
      {
        "item_id": 2,
        "product_id": 156,
        "quantity": 1,
        "list_price": 6499.99,
        "discount": 0.1
      },
      {
        "item_id": 3,
        "product_id": 126,
        "quantity": 1,
        "list_price": 469.99,
        "discount": 0.1
      },
      {
        "item_id": 4,
        "product_id": 128,
        "quantity": 2,
        "list_price": 1899,
        "discount": 0.05
      },
      {
        "item_id": 5,
        "product_id": 174,
        "quantity": 1,
        "list_price": 3199.99,
        "discount": 0.2
      }
    ]
  },
  {
    "order_id": 1613,
    "order_status": 3,
    "order_date": "2018-11-18",
    "items": [
      {
        "item_id": 1,
        "product_id": 153,
        "quantity": 1,
        "list_price": 4999.99,
        "discount": 0.07
      },
      {
        "item_id": 2,
        "product_id": 283,
        "quantity": 2,
        "list_price": 319.99,
        "discount": 0.05
      }
    ]
  }
]

Краткое содержание

• Используйте предложение FOR JSON для форматирования результата запроса в документ JSON.