JSON with Sqlite
sqlite.org
JSON with Sqlite
1–10 of 74 posts
Re: JSON with Sqlite
#2Is loading extensions at runtime new? Coulda sworn I used to have to recompile for this
Re: JSON with Sqlite
#3Is loading extensions at runtime new? Coulda sworn I used to have to recompile for this
Pretty sure it's been around for at least 10 years or so. (But it's not a widely used feature, I think.)
Re: JSON with Sqlite
#4What are some useful use-cases for this?
I can already use JSON in Sqlite by doing parsing in the client outside of the Sqlite API. Any examples where I'd want to use this instead? I'm guessing for where clauses in queries, perhaps?
Can I create an index on a field within a JSON tuple?
Re: JSON with Sqlite
#5What are some useful use-cases for this? I can already use JSON in Sqlite by doing parsing in the client outside of the Sqlite API. Any examples where I'd want to use this instead? I'm guessing for where clauses in queries, perhaps? Can I create an index on a field within a JSON tuple?
Since you can make effectively arbitrary index expressions[1], I'd expect the answer to be "yes". The article doesn't explicitly state if they're deterministic or not, but it seems like some of them could / should be.
Re: JSON with Sqlite
#6Is loading extensions at runtime new? Coulda sworn I used to have to recompile for this
It seems valuable enough to have in the standard build. That and the CSV extension as well.
Re: JSON with Sqlite
#7Re: JSON with Sqlite
#8What are some useful use-cases for this? I can already use JSON in Sqlite by doing parsing in the client outside of the Sqlite API. Any examples where I'd want to use this instead? I'm guessing for where clauses in queries, perhaps? Can I create an index on a field within a JSON tuple?
Since you can make effectively arbitrary index expressions[1], I'd expect the answer to be "yes". The article doesn't explicitly state if they're deterministic or not, but it seems like some of them could / should be. [1]: https://www.sqlite.org/expridx.html
I gave it a shot and it appears to work as expected.
sqlite> create table a (id int primary key, j json);
sqlite> insert into a values (1, '{"hello":"world"}');
sqlite> create index idx_a on a (json_extract(j, '$.hello'));
sqlite> explain query plan select * from a where json_extract(j, '$.hello') = 'world';
QUERY PLAN
`--SEARCH TABLE a USING INDEX idx_a (=?)
sqlite> explain query plan select * from a where json_extract(j, '$.foo') = 'world';
QUERY PLAN
`--SCAN TABLE aRe: JSON with Sqlite
#9Re: JSON with Sqlite
#10What are some useful use-cases for this? I can already use JSON in Sqlite by doing parsing in the client outside of the Sqlite API. Any examples where I'd want to use this instead? I'm guessing for where clauses in queries, perhaps? Can I create an index on a field within a JSON tuple?
[deleted]