SELECT load_extension('/path/to/json1/extension');
Once the extension is enabled, you can create a table that has a column of type JSON using the following SQL statement:
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data JSON
Inserting JSON Data
To insert a JSON value into this table, you can use the json_encode function provided by the JSON1 extension, like this:
INSERT INTO users (id, data) VALUES (1, json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
Querying JSON Data
To query this table, you can use the json_extract function, which allows you to extract values from the JSON column by specifying a JSON path. For example, to get the email address of the user with id 1, you can use the following query:
SELECT json_extract(data, '$.email') AS email
FROM users
WHERE id = 1;
Store JSON in BLOB
Another way to store and query JSON in SQLite is to use the BLOB type. In this case, you can create a table with a BLOB column like this:
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data BLOB
Inserting JSON into a BLOB Column
To insert a JSON value into this table, you can use the json_encode function to encode the value as a string, and then use the hex function to convert it to a BLOB, like this:
INSERT INTO users (id, data) VALUES (1, hex(json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
})));
Querying JSON in a BLOB Column
To query this table, you can use the JSON_EXTRACT function provided by the JSON1 extension, but you need to convert the BLOB value back to a string using the unhex function, like this:
SELECT json_extract(unhex(data), '$.email') AS email
FROM users
WHERE id = 1;
Summary
In conclusion, SQLite provides two ways to store and query JSON values: using the JSON1 extension and using the BLOB type. Both approaches have their own advantages and disadvantages, and which one you choose will depend on your specific needs.