Modifying JSON in SQL Server

by

There are two ways to modify JSON data in a SQL Server table:

  • Use the JSON_MODIFY() function in an UPDATE statement to change individual properties inside the JSON data.
  • Completely replace the JSON data with a regular UPDATE statement.

In this article, I’ll show how to insert, update, and delete JSON data by using the JSON_MODIFY() function. I’ll also show how to deal with JSON arrays.

Note: This is part 2 of the mini-series about the SQL Server JSON API. The first part was about querying JSON in SQL Server.

Insert JSON

First, I’ll start by inserting JSON in a table. The rest of the sections in this article will be modifying this JSON data.

The following shows my starting JSON data. I’m simply inserting it into a table with a regular INSERT statement:

DECLARE @playersJson nvarchar(max) = '{
  "QB1": {
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia"
  },
  "RB1": {
    "Name": "D''Andre Swift",
    "YearsOfExperience": 1,
    "College": "Georgia"
  },
  "WR1": {
    "Name": "Kenny Golladay",
    "YearsOfExperience": 4,
    "College": "Northern Illinois"
  },
  "TE1": {
    "Name": "T.J. Hockenson",
    "YearsOfExperience": 2,
    "College": "Iowa"
  }
}'

INSERT INTO NFLTeams
(TeamName, Players)
VALUES
('Detroit Lions',
@playersJson)
Code language: SQL (Structured Query Language) (sql)

Add a JSON property

To add a new property to the JSON, use JSON_MODIFY with JSON_QUERY like this:

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.Kicker', JSON_QUERY('{
    "Name": "Matt Prater",
    "YearsOfExperience": 14,
    "College": "Central Florida"
  }'))
WHERE TeamName = 'Detroit Lions'Code language: SQL (Structured Query Language) (sql)

This inserted the Kicker property to the data:

{
  "QB1": {
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia"
  },
  "RB1": {
    "Name": "D'Andre Swift",
    "YearsOfExperience": 1,
    "College": "Georgia"
  },
  "WR1": {
    "Name": "Kenny Golladay",
    "YearsOfExperience": 4,
    "College": "Northern Illinois"
  },
  "TE1": {
    "Name": "T.J. Hockenson",
    "YearsOfExperience": 2,
    "College": "Iowa"
  },
  "Kicker": {
    "Name": "Matt Prater",
    "YearsOfExperience": 14,
    "College": "Central Florida"
  }
}
Code language: JSON / JSON with Comments (json)

Why use JSON_QUERY with JSON_MODIFY?

If you don’t use JSON_QUERY, and try to insert the string as is, it puts in literal string escapes (\) and newlines (\n) like this:

"Kicker":"{\n    \"Name\": \"Matt Prater\",\n    \"YearsOfExperience\": 14,\n    \"College\": \"Central Florida\"\n  }"}
Code language: JSON / JSON with Comments (json)

Update a JSON property

Let’s say we want to change the RB1 property by swapping in a different player object. To update an existing property, it’s actually the same query as inserting a new one. You use JSON_MODIFY() + JSON_QUERY() for this.

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.RB1', JSON_QUERY('{
    "Name": "Adrian Peterson",
    "YearsOfExperience": 13,
    "College": "Oklahoma"
  }'))
WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This changed the RBA1 property to this different player object:

{
  "QB1": {
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia"
  },
  "RB1": {
    "Name": "Adrian Peterson",
    "YearsOfExperience": 13,
    "College": "Oklahoma"
  },
  "WR1": {
    "Name": "Kenny Golladay",
    "YearsOfExperience": 4,
    "College": "Northern Illinois"
  },
  "TE1": {
    "Name": "T.J. Hockenson",
    "YearsOfExperience": 2,
    "College": "Iowa"
  },
  "Kicker": {
    "Name": "Matt Prater",
    "YearsOfExperience": 14,
    "College": "Central Florida"
  }
}
Code language: JSON / JSON with Comments (json)

Add / update a primitive JSON property

JSON property values can either be objects or primitive values. To add or update a primitive property, use JSON_MODIFY() like this:

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.QB1.DraftPosition', 1)
WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This added the DraftPosition property to the QB1 object with a value of 1:

{
  "QB1": {
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia",
    "DraftPosition": 1
  },
  "RB1": {
    "Name": "Adrian Peterson",
    "YearsOfExperience": 13,
    "College": "Oklahoma"
  },
  "WR1": {
    "Name": "Kenny Golladay",
    "YearsOfExperience": 4,
    "College": "Northern Illinois"
  },
  "TE1": {
    "Name": "T.J. Hockenson",
    "YearsOfExperience": 2,
    "College": "Iowa"
  },
  "Kicker": {
    "Name": "Matt Prater",
    "YearsOfExperience": 14,
    "College": "Central Florida"
  }
}
Code language: JSON / JSON with Comments (json)

Deleting a JSON property

To delete a property, set the value to NULL. 

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.QB1.DraftPosition', NULL)
WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

When you set a property to NULL in lax mode, it deletes the property. All functions use lax mode by default (instead of strict mode).

This deleted the DraftPosition property:

{
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia"
}Code language: JSON / JSON with Comments (json)

Dealing with JSON Arrays

JSON arrays are a special case.

First, arrays are difficult to query, because to look at individual values, you have to specify the index, which requires you to know how many elements are in the array. If you find yourself needing to do advanced queries against arrays, you might want to restructure your data to make it easier to query. Consider this as just another part of database design.

Second, most likely, your client code will only need to deal with arrays as a whole, not individual values in the array. In other words, when you pull in data from the database, you’ll simply deserialize the whole array property into an array/list. Same thing with modifying the data (serialize the whole array, instead of doing queries to modify individual values inside it).

Finally, there’s not a simple, safe way to delete values from an array using the SQL Server JSON API. The simplest way is to completely replace the array with a new array. This won’t be difficult if your code is already deserializing/serializing the array as a whole, instead of trying to deal with individual values.

With all of that said, this section shows how to insert an empty array and how to insert / update / read individual values from the array.

Insert an empty array

To insert an empty array, use JSON_MODIFY() with JSON_QUERY(‘[]’) like this:

UPDATE NFLTeams
SET Players = JSON_MODIFY(Players, '$.QB1.GameStats', JSON_QUERY('[]'))
WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This created the GameStats property and set the value to an empty array:

{
  "QB1": {
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia",
    "GameStats": []
  },
  "RB1": {
    "Name": "Adrian Peterson",
    "YearsOfExperience": 13,
    "College": "Oklahoma"
  },
  "WR1": {
    "Name": "Kenny Golladay",
    "YearsOfExperience": 4,
    "College": "Northern Illinois"
  },
  "TE1": {
    "Name": "T.J. Hockenson",
    "YearsOfExperience": 2,
    "College": "Iowa"
  },
  "Kicker": {
    "Name": "Matt Prater",
    "YearsOfExperience": 14,
    "College": "Central Florida"
  }
}
Code language: JSON / JSON with Comments (json)

Append a new value into an array

To add to an array, use JSON_MODIFY() with JSON_QUERY() and specify append in the JSON path parameter.

UPDATE NFLTeams
SET Players = 
JSON_MODIFY(Players, 'append $.QB1.GameStats', 
JSON_QUERY('{"CmpPct":64.3, "YDS":402, "TD":3, "INT":1,"Rating":109.4}'))
WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This added an object into the GameStats array:

{
  "QB1": {
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia",
    "GameStats": [
      {
        "CmpPct": 64.3,
        "YDS": 402,
        "TD": 3,
        "INT": 1,
        "Rating": 109.4
      }
    ]
  },
  "RB1": {
    "Name": "Adrian Peterson",
    "YearsOfExperience": 13,
    "College": "Oklahoma"
  },
  "WR1": {
    "Name": "Kenny Golladay",
    "YearsOfExperience": 4,
    "College": "Northern Illinois"
  },
  "TE1": {
    "Name": "T.J. Hockenson",
    "YearsOfExperience": 2,
    "College": "Iowa"
  },
  "Kicker": {
    "Name": "Matt Prater",
    "YearsOfExperience": 14,
    "College": "Central Florida"
  }
}
Code language: JSON / JSON with Comments (json)

Modify a value in an array

To modify a value in an array, use JSON_MODIFY() and specify the array index. The following shows how to modify a property in an object at the 0th index in the array:

UPDATE NFLTeams
SET Players = 
JSON_MODIFY(Players, '$.QB1.GameStats[0].Rating', 109)
WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This changed the value of the Rating property inside the GameStats array:

{
  "Name": "Matthew Stafford",
  "YearsOfExperience": 12,
  "College": "Georgia",
  "GameStats": [
    {
      "CmpPct": 64.3,
      "YDS": 402,
      "TD": 3,
      "INT": 1,
      "Rating": 109
    }
  ]
}
Code language: JSON / JSON with Comments (json)

Reading a value from an array

To read a value from an array, you need to specify the index with JSON_QUERY() (if it’s an object) or JSON_VALUE() (if it’s a primitive value), like this:

SELECT JSON_QUERY(Players, '$.QB1.GameStats[0]') FROM NFLTeams
WHERE TeamName = 'Detroit Lions'
Code language: SQL (Structured Query Language) (sql)

This returns the following object:

{
  "CmpPct": 64.3,
  "YDS": 402,
  "TD": 3,
  "INT": 1,
  "Rating": 109
}
Code language: JSON / JSON with Comments (json)

Leave a Comment