sqliteHow can I use SQLite to store and query JSON data?
SQLite can be used to store and query JSON data with the JSON1 extension. The extension provides a set of SQL functions for storing and querying JSON data.
For example, the following code block uses the json_insert()
function to insert a JSON document into a table:
CREATE TABLE test_table (id INTEGER PRIMARY KEY, doc JSON);
INSERT INTO test_table (doc) VALUES (json_insert('{"name": "John Doe"}'));
The following code block uses the json_extract()
function to query the JSON document stored in the table:
SELECT json_extract(doc, '$.name') FROM test_table;
Output example
John Doe
The JSON1 extension provides a range of functions for manipulating and querying JSON data, including json_extract()
, json_insert()
, json_replace()
, json_set()
, and json_remove()
. For more information, see the SQLite JSON1 documentation.
More of Sqlite
- How can SQLite and ZFS be used together for software development?
- How do I use SQLite with Zephyr?
- How do I use the SQLite ZIP VFS to compress a database?
- How do I import data from a SQLite zip file?
- How do I use SQLite UNION to combine multiple SELECT queries?
- How do I use the SQLite zfill function?
- How do I generate a UUID in SQLite?
- How do I extract the year from a datetime value in SQLite?
- How do I generate XML output from a SQLite database?
- How to use a SQLite hash to securely store data?
See more codes...