SQLite json_set() Function

Summary: in this tutorial, you will learn how to use the SQLite json_set() function to set or replace JSON values in a JSON document.

Introduction to the SQLite json_set() function

The json_set() function works like the json_replace() function except that the json_set() function does create a JSON value if it does not exist.

Here’s the syntax of the json_set() function:

json_set(json_data, path1, value1, path2, value2, ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_data is the JSON data you want to modify. It can be a literal JSON or a table column that stores JSON data.
• path1, value1, path2, value2 path2, … are a pair of JSON paths and new values to update.

The json_set() function returns the new JSON data after updating the value1, value2, … at the corresponding path1, path2, …

Notice that the number of arguments of the json_set is always odd. The first argument is the input JSON data. Subsequent arguments are pairs of JSON paths and values to update.

The json_set() function creates the JSON element if a path does not exist. This is the main difference between the json_replace() and json_set() functions:

The json_set() sequentially updates values from left to right. It means that the json_set() function uses the result of the previous update for the next one.

SQLite json_set() function example

First, create a table called events:

CREATE TABLE IF NOT EXISTS events(
    id INTEGER PRIMARY KEY, 
    data TEXT NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the events table:

INSERT INTO events(data)
VALUES(
'{
  "title": "Tech Conference 2024",
  "description": "A conference showcasing the latest trends in technology.",
  "date": "2024-04-30",
  "time": "09:00 AM",
  "location": {
    "venue": "Convention Center",
    "city": "New York",
    "country": "USA"
  },
  "organizers": [
    {
      "name": "Tech Events Company",
      "contact": "[email protected]"
    },
    {
      "name": "New York Tech Association",
      "contact": "[email protected]"
    }
  ],
  
  "topics": [
    "Artificial Intelligence",
    "Blockchain",
    "Internet of Things",
    "Cybersecurity"
  ]
}');Code language: SQL (Structured Query Language) (sql)

Third, use the json_set() function to update the date of the event with id 1:

UPDATE events
SET
  data = json_set(data, '$.date', '2024-12-05')
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Verify the update:

SELECT
  data ->> 'title' event_title,
  data ->> 'date'  event_date
FROM
  events
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

event_title           event_date
--------------------  ----------
Tech Conference 2024  2024-12-05Code language: SQL (Structured Query Language) (sql)

The output shows that the event_date has been updated to 05 December 2024 successfully.

Fourth, use the json_set() function to update the time and venue:

UPDATE events
SET
  data = json_set(
    data,
    '$.time',
    '08:30 AM',
    '$.location.venue',
    'Jacob K. Javits Convention Center'
  )
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Verify the update:

SELECT
  data ->> 'time' event_time,
  data ->> '$.location.venue'  venue
FROM
  events
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

event_time  venue
----------  ---------------------------------
08:30 AM    Jacob K. Javits Convention CenterCode language: SQL (Structured Query Language) (sql)

Fifth, set the price of the event id 1 to 199.99 :

UPDATE events
SET
  data = json_set(data, '$.price', '199.99')
WHERE
  id = 1;Code language: JavaScript (javascript)

Since the price does not exist, the json_set() function creates a new property price with a value of 199.99:

SELECT
  data ->> 'title' event_title,
  data ->> '$.price'  price
FROM
  events
WHERE
  id = 1;Code language: JavaScript (javascript)

Output:

event_title           price
--------------------  ------
Tech Conference 2024  199.99Code language: CSS (css)

Summary

• Use the json_set() function to update one or more values in JSON data and create the JSON element if it does not exist.