SQLite json_replace() Function

Summary: in this tutorial, you will learn how to use the SQLite json_replace() function to update one or more values in JSON data.

Introduction to SQLite json_replace() Function

In SQLite, the json_replace() function replaces one or more values in JSON data based on specified paths.

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

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

In this syntax:

• json_data is the JSON data that you want to update. It can be a literal JSON or a table column that stores JSON data.
• path1, path2, … are the JSON path that locates the elements in json_data to update.
• value1, value2, … are the new values for updating.

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

It’s important to note that the json_replace() function will not create the element if a path does not exist. This is the main difference between the json_replace() and json_set() functions.

The json_set() function overwrites the value if the path exists or creates a new value if the path does not exist.

The json_replace() updates values sequentially from left to right. It means that the json_repalce() function will use the result of the previous update for the next one.

SQLite json_replace() function example

First, create a table called events:

CREATE TABLE 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_replace() function to update the date of the event with id 1:

UPDATE events
SET
  data = json_replace (data, '$.date', '2024-05-10')
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-05-10Code language: SQL (Structured Query Language) (sql)

The output shows that the event_date has been updated successfully.

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

UPDATE events
SET
  data = json_replace (
    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)

Summary

• Use the json_repalce() function to update one or more values in JSON data.