Добро пожаловать на третью статью в нашей серии о том, как использовать новые функции JSON в SQL Server. В предыдущих статьях мы обсудили разбор JSON и создание объектов JSON. Сегодня мы рассмотрим, как обновлять, добавлять и удалять элементы в объекте JSON с помощью функции JSON_MODIFY() в SQL Server 2016.

Давайте начнем с того, как обновлять существующие значения в строке JSON. Функция JSON_MODIFY() работает аналогично функции REPLACE() в SQL. Она принимает три аргумента: данные, которые нужно изменить, свойство, которое нужно заменить (с использованием синтаксиса XPath) и новое значение. Вот пример:

DECLARE @garage nvarchar(1000) = N'{
  "Cars": [
    {
      "Make": "Volkswagen",
      "Model": {
        "Base": "Golf",
        "Trim": "GL"
      },
      "Year": 2003,
      "PurchaseDate": "2006-10-05T00:00:00.000Z"
    },
    {
      "Make": "Subaru",
      "Model": {
        "Base": "Impreza",
        "Trim": "Premium"
      },
      "Year": 2016,
      "PurchaseDate": "2015-08-18T00:00:00.000Z"
    }
  ]
}'

-- Обновление значения Trim первой машины
SET @garage = JSON_MODIFY(@garage, '$.Cars[0].Model.Trim', 'GLI')

SELECT @garage

В этом примере мы обновили значение Trim первой машины с “GL” на “GLI”. Функция JSON_MODIFY() заменила существующее значение новым.

Добавление новых значений в существующий объект JSON также просто с помощью JSON_MODIFY(). Вы можете использовать ключевое слово ‘append’, чтобы добавить новое свойство или элемент в объект JSON. Вот пример:

DECLARE @garage nvarchar(1000) = N'{
  "Cars": [
    {
      "Make": "Volkswagen",
      "Model": {
        "Base": "Golf",
        "Trim": "GLI"
      },
      "Year": 2003,
      "PurchaseDate": "2006-10-05T00:00:00.000Z"
    },
    {
      "Make": "Subaru",
      "Model": {
        "Base": "Impreza",
        "Trim": "Premium"
      },
      "Year": 2016,
      "PurchaseDate": "2015-08-18T00:00:00.000Z"
    }
  ]
}'

-- Добавление нового свойства SellDate к первой машине
SET @garage = JSON_MODIFY(@garage, 'append lax $.Cars[0].SellDate', '2017-02-17T00:00:00.000Z')

-- Добавление новой машины в гараж
SET @garage = JSON_MODIFY(@garage, 'append $.Cars', JSON_QUERY('{
  "Make": "Volkswagen",
  "Model": {
    "Base": "Golf",
    "Trim": "GTI"
  },
  "Year": 2017,
  "PurchaseDate": "2017-02-19T00:00:00.000Z"
}'))

SELECT @garage

В этом примере мы добавили новое свойство SellDate к первой машине и новую машину в гараж. Ключевое слово ‘append’ позволяет нам добавлять новые свойства или элементы в объект JSON.

Теперь перейдем к удалению элементов из объекта JSON. Чтобы удалить свойство или элемент, мы можем использовать функцию JSON_MODIFY() с теми же аргументами, что и в примере с изменением, но передавать NULL в качестве нового значения. Вот пример:

DECLARE @garage nvarchar(1000) = N'{
  "Cars": [
    {
      "Make": "Volkswagen",
      "Model": {
        "Base": "Golf",
        "Trim": "GLI"
      },
      "Year": 2003,
      "PurchaseDate": "2006-10-05T00:00:00.000Z",
      "SellDate": "2017-02-17T00:00:00.000Z"
    },
    {
      "Make": "Subaru",
      "Model": {
        "Base": "Impreza",
        "Trim": "Premium"
      },
      "Year": 2016,
      "PurchaseDate": "2015-08-18T00:00:00.000Z"
    },
    {
      "Make": "Volkswagen",
      "Model": {
        "Base": "Golf",
        "Trim": "GTI"
      },
      "Year": 2017,
      "PurchaseDate": "2017-02-19T00:00:00.000Z"
    }
  ]
}'

-- Удаление свойства PurchaseDate из первой машины
SET @garage = JSON_MODIFY(@garage, '$.Cars[0].PurchaseDate', NULL)

SELECT @garage

В этом примере мы удалили свойство PurchaseDate из первой машины, установив его в NULL. Функция JSON_MODIFY() полностью удалила свойство из объекта JSON.

Однако есть ограничение с функцией JSON_MODIFY() при удалении элементов массива. Вместо удаления значения из массива и сдвига остальных элементов, она просто заменяет значение массива на NULL. Чтобы преодолеть это ограничение, нам нужно преобразовать объект JSON в набор строк, выбрать все, кроме элемента, который мы хотим удалить, агрегировать строки в строку и затем создать объект JSON заново. Этот процесс может быть довольно громоздким. Вот пример:

DECLARE @garage nvarchar(1000) = N'{
  "Cars": [
    {
      "Make": "Volkswagen",
      "Model": {
        "Base": "Golf",
        "Trim": "GLI"
      },
      "Year": 2003,
      "SellDate": "2017-02-17T00:00:00.000Z"
    },
    {
      "Make": "Subaru",
      "Model": {
        "Base": "Impreza",
        "Trim": "Premium"
      },
      "Year": 2016,
      "PurchaseDate": "2015-08-18T00:00:00.000Z"
    },
    {
      "Make": "Volkswagen",
      "Model": {
        "Base": "Golf",
        "Trim": "GTI"
      },
      "Year": 2017,
      "PurchaseDate": "2017-02-19T00:00:00.000Z"
    }
  ]
}'

-- Удаление первой машины из массива
SELECT JSON_QUERY('{
  "Cars": [' + STUFF((
    SELECT ',' + value
    FROM OPENJSON(@garage, '$.Cars')
    WHERE [key] <> 0
    FOR XML PATH('')
  ), 1, 1, '') + ']}')

В этом примере мы удалили первую машину из массива, преобразовав объект JSON в набор строк, выбрав все, кроме первой строки, агрегировав строки в строку и затем создав объект JSON заново. Этот процесс не идеален и может быть довольно громоздким.

В заключение, функция JSON_MODIFY() в SQL Server 2016 предоставляет удобный способ изменять