How to store and query JSON in SQLite using a TEXT column with examples

SQLite is a popular embedded database that is used in many applications. It is a lightweight, simple, and self-contained database engine that is easy to install and use. One of the useful features of SQLite is its ability to store and query JSON data.

To store JSON data in SQLite, you can use a TEXT column to store the JSON document as a string. I think this is better than using BLOB to store JSON because it’s a bit more intuitive.

For example, suppose you have a table called “users” with the following structure:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  data TEXT
);

Here, the “data” column is a TEXT column that will be used to store the JSON data.

Inserting JSON into TEXT

To insert a row into the “users” table with JSON data, you can use the following SQL statement:

INSERT INTO users (name, data) VALUES ('John', '{"age": 30, "country": "USA"}');

This will insert a row with the name “John” and the JSON data {"age": 30, "country": "USA"} in the “data” column. Unlike with a BLOB column we don’t really need to pre-process the data in a programming language.

Querying JSON Data Natively

To query the JSON data, you can use the JSON functions provided by SQLite. For example, to get the value of the “age” field in the JSON data, you can use the following SQL statement:

SELECT name, JSON_EXTRACT(data, '$.age') AS age FROM users;

This will return the name and the value of the “age” field for all rows in the “users” table.

You can also use the JSON_QUERY function to get a specific field or a set of fields from the JSON data. For example, to get the name and the values of the “age” and “country” fields, you can use the following SQL statement:

SELECT name, JSON_QUERY(data, '$.age, $.country') AS data FROM users;

This will return the name and the values of the “age” and “country” fields as a JSON object for all rows in the “users” table.

Updating JSON Values

You can also use the JSON_MODIFY function to update the values of the fields in the JSON data. For example, to update the value of the “age” field to 35, you can use the following SQL statement:

UPDATE users SET data = JSON_MODIFY(data, '$.age', 35) WHERE id = 1;

This will update the value of the “age” field to 35 for the row with the id 1 in the “users” table.

Using ‘LIKE’ With JSON Data

In addition to the JSON functions, you can also use the LIKE operator to query the JSON data. For example, to get the rows with the “country” field set to “USA”, you can use the following SQL statement:

SELECT * FROM users WHERE data LIKE '%"country":"USA"%';

This will return all rows with the “country” field set to “USA” in the “users” table.

SQLite JSON in TEXT Wrap Up

To sum up, SQLite allows you to store and query JSON data using a TEXT column and a set of JSON functions. You can use these functions to extract, query, and modify the JSON data in your SQLite database.