How to modify JSON in SQL Server

1. Use the JSON_MODIFY() function in an UPDATE statement to alter specific properties inside the JSON data.
2. Replace the entire JSON content with a standard UPDATE statement.

This article will cover how to insert, update, and delete JSON data using the JSON_MODIFY() function, as well as how to manage JSON arrays.

Inserting JSON

Let’s begin by inserting JSON data into a table. The subsequent sections of this article will focus on modifying this JSON data.

Consider this starting JSON data. We’ll insert it into a table using a regular INSERT statement:

DECLARE @teamJson nvarchar(max) = '{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech"
  },
  "Player2": {
    "Name": "Travis Kelce",
    "YearsOfExperience": 7,
    "College": "Cincinnati"
  },
  "Player3": {
    "Name": "Tyreek Hill",
    "YearsOfExperience": 6,
    "College": "West Alabama"
  }
}'

INSERT INTO NFLTeams
(TeamName, Players)
VALUES
('Kansas City Chiefs', @teamJson)

Adding a JSON Property

To add a new property to the JSON data, use the JSON_MODIFY function alongside JSON_QUERY as shown below:

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.Kicker', JSON_QUERY('{
    "Name": "Harrison Butker",
    "YearsOfExperience": 6,
    "College": "Georgia Tech"
  }'))
WHERE TeamName = 'Kansas City Chiefs'

This adds the Kicker property to the JSON:

{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech"
  },
  "Player2": {
    "Name": "Travis Kelce",
    "YearsOfExperience": 7,
    "College": "Cincinnati"
  },
  "Player3": {
    "Name": "Tyreek Hill",
    "YearsOfExperience": 6,
    "College": "West Alabama"
  },
  "Kicker": {
    "Name": "Harrison Butker",
    "YearsOfExperience": 6,
    "College": "Georgia Tech"
  }
}

Why Use JSON_QUERY with JSON_MODIFY?

Without using JSON_QUERY, inserting the string directly as JSON would cause it to be stored as a literal string with escape characters and newlines, like so:

"Kicker":"{\n    \"Name\": \"Harrison Butker\",\n    \"YearsOfExperience\": 6,\n    \"College\": \"Georgia Tech\"\n}"

Using JSON_QUERY ensures that the data is inserted as valid JSON.

Updating a JSON Property

To modify an existing property, such as changing a player’s information, we can use JSON_MODIFY() and JSON_QUERY() just like adding a new property.

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.Player2', JSON_QUERY('{
    "Name": "George Kittle",
    "YearsOfExperience": 5,
    "College": "Iowa"
  }'))
WHERE TeamName = 'Kansas City Chiefs'

This will change the Player2 property to the new player:

{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech"
  },
  "Player2": {
    "Name": "George Kittle",
    "YearsOfExperience": 5,
    "College": "Iowa"
  },
  "Player3": {
    "Name": "Tyreek Hill",
    "YearsOfExperience": 6,
    "College": "West Alabama"
  },
  "Kicker": {
    "Name": "Harrison Butker",
    "YearsOfExperience": 6,
    "College": "Georgia Tech"
  }
}

Adding/Updating a Primitive JSON Property

To add or update a primitive property (such as an integer or string), simply use JSON_MODIFY():

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.Player1.DraftPosition', 10)
WHERE TeamName = 'Kansas City Chiefs'

This will add a DraftPosition property to Player1:

{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech",
    "DraftPosition": 10
  },
  "Player2": {
    "Name": "George Kittle",
    "YearsOfExperience": 5,
    "College": "Iowa"
  },
  "Player3": {
    "Name": "Tyreek Hill",
    "YearsOfExperience": 6,
    "College": "West Alabama"
  },
  "Kicker": {
    "Name": "Harrison Butker",
    "YearsOfExperience": 6,
    "College": "Georgia Tech"
  }
}

Deleting a JSON Property

To delete a property, set its value to NULL:

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.Player1.DraftPosition', NULL)
WHERE TeamName = 'Kansas City Chiefs'

This will remove the DraftPosition property from Player1:

{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech"
  },
  "Player2": {
    "Name": "George Kittle",
    "YearsOfExperience": 5,
    "College": "Iowa"
  },
  "Player3": {
    "Name": "Tyreek Hill",
    "YearsOfExperience": 6,
    "College": "West Alabama"
  },
  "Kicker": {
    "Name": "Harrison Butker",
    "YearsOfExperience": 6,
    "College": "Georgia Tech"
  }
}

Handling JSON Arrays

JSON arrays require some special handling. Let’s look at how to insert, update, and read from arrays in JSON data.

Inserting an Empty Array

To insert an empty array, use JSON_MODIFY() with JSON_QUERY('[]'):

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.Player1.GameStats', JSON_QUERY('[]'))
WHERE TeamName = 'Kansas City Chiefs'

This creates a GameStats property with an empty array:

{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech",
    "GameStats": []
  }
}

Appending a Value to an Array

To add a value to an array, use JSON_MODIFY() with the append keyword:

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, 'append $.Player1.GameStats', 
JSON_QUERY('{"CmpPct":70.4, "YDS":350, "TD":4, "INT":0}'))
WHERE TeamName = 'Kansas City Chiefs'

This adds a new object to the GameStats array:

{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech",
    "GameStats": [
      {
        "CmpPct": 70.4,
        "YDS": 350,
        "TD": 4,
        "INT": 0
      }
    ]
  }
}

Modifying a Value in an Array

To modify a value in an array, use the array index in the JSON path:

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.Player1.GameStats[0].CmpPct', 72.5)
WHERE TeamName = 'Kansas City Chiefs'

This updates the CmpPct value in the GameStats array:

{
  "Player1": {
    "Name": "Patrick Mahomes",
    "YearsOfExperience": 5,
    "College": "Texas Tech",
    "GameStats": [
      {
        "CmpPct": 72.5,
        "YDS": 350,
        "TD": 4,
        "INT": 0
      }
    ]
  }
}

Reading a Value from an Array

To read a value from an array, specify the index:

SELECT JSON_QUERY(Players, '$.Player1.GameStats[0]') FROM NFLTeams
WHERE TeamName = 'Kansas City Chiefs'

This will return the following JSON object:

{
  "CmpPct": 72.5,
  "YDS": 350,
  "TD": 4,
  "INT": 0
}

By using these methods, you can easily manage and modify JSON data in SQL Server, handling both simple properties and complex JSON arrays with ease.