Querying JSON with SQLite
I really like the fact that a lot of the database engines are allowing for native JSON querying, not least with SQLite, as I'm using it for various projects at the moment.
However, I can never seem to remember the incantation for how to actually perform the JSON query.
Let's say that we have the following schema and data:
CREATE TABLE data (
id integer PRIMARY KEY,
json TEXT,
array TEXT
);
INSERT INTO data (json, array) VALUES('
{
"name": "go",
"depTypes": [
"require",
"indirect"
]
}',
'[ "require", "indirect" ]'
);
INSERT INTO data (json, array) VALUES('
{
"name": "ruby",
"depTypes": [
"dev"
]
}',
'[ "dev" ]'
);
If we wanted to parse our array type, we could query:
select data.id, arr.value from data, json_each(data.array) as arr;
And if we wanted to query a JSON field within json, we could query:
select id, json_extract(data.json, '$.name') name from data;
And to query the depTypes array within json:
select data.id, depType.value from data, json_each(json_extract(data.json, '$.depTypes')) depType;
