Sqlite

How to use JSON object in sqlite

prerequest: sqlite3.9 and above

-- construct table with json
CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    data TEXT
);
INSERT INTO my_table (data) VALUES ('{"name": "Alice", "age": 25, "skills": ["Python", "SQL"]}');

-- extract value from json object
SELECT json_extract(data, '$.name') AS name FROM my_table;

-- setup value for json object
SET data = json_set(data, '$.age', 26)


-- insert new field
UPDATE my_table
SET data = json_insert(data, '$.city', 'New York')
WHERE id = 1;

-- replace field value
UPDATE my_table
SET data = json_replace(data, '$.age', 30)
WHERE id = 1;


-- remove a field
UPDATE my_table
SET data = json_remove(data, '$.city')
WHERE id = 1;

-- get the length of an array
SELECT json_array_length(json_extract(data, '$.skills')) AS skills_count FROM my_table;

-- create index for json
CREATE INDEX idx_name ON my_table (json_extract(data, '$.name'));