Flat file NoSQL solution
It's possible via using the JSON1 extension to query JSON data stored in a column, yes:
sqlite> CREATE TABLE test(data TEXT);
sqlite> INSERT INTO test VALUES ('{"name":"john doe","balance":1000,"data":[1,73.23,18]}');
sqlite> INSERT INTO test VALUES ('{"name":"alice","balance":2000,"email":"[email protected]"}');
sqlite> SELECT * FROM test WHERE json_extract(data, '$.balance') > 1500;
data
--------------------------------------------------
{"name":"alice","balance":2000,"email":"[email protected]"}
If you're going to be querying the same field a lot, you can make it more efficient by adding an index on the expression:
CREATE INDEX test_idx_balance ON test(json_extract(data, '$.balance'));
will use that index on the above query instead of scanning every single row.
SQLite
JSON1
extension andjson_extract
(see accepted answer). Example:import sqlite3, json # tested with precompiled Windows binaries from https://www.sqlite.org/download.html (sqlite3.dll copied in C:\Python37\DLLs) class sqlitenosql: def __init__(self, f): self.db = sqlite3.connect(f) self.db.execute('CREATE TABLE test(data TEXT);') def close(self): self.db.commit() self.db.close() def addrow(self, d): self.db.execute("INSERT INTO test VALUES (?);", (json.dumps(d),)) def find(self, query): for k, v in query.items(): if isinstance(v, str): query[k] = f"'{v}'" q = ' AND '.join(f" json_extract(data, '$.{k}') = {v}" for k, v in query.items()) for r in self.db.execute(f"SELECT * FROM test WHERE {q}"): yield r[0] db = sqlitenosql(':memory:') db.addrow({'name': 'john', 'balance': 1000, 'data': [1, 73.23, 18], 'abc': 'hello'}) db.addrow({'name': 'alice', 'balance': 2000, 'email': '[email protected]'}) db.addrow({'name': 'bob', 'balance': 1000}) db.addrow({'name': 'richard', 'balance': 1000, 'abc': 'hello'}) for r in db.find({'balance': 1000, 'abc': 'hello'}): print(r) # {"name": "john", "balance": 1000, "data": [1, 73.23, 18], "abc": "hello"} # {"name": "richard", "balance": 1000, "abc": "hello"} db.close()
sqlitedict as mentioned in Key: value store in Python for possibly 100 GB of data, without client/server and Use SQLite as a key:value store with:
key = an ID
value = the dict we want to store, e.g.
{'name': 'alice', 'balance': 2000, 'email': '[email protected]'}
Further reading about use of SQLite with JSON: https://community.esri.com/groups/appstudio/blog/2018/08/21/working-with-json-in-sqlite-databases
TinyDB
TinyDB looks like a good solution:
>>> from tinydb import TinyDB, Query
>>> db = TinyDB('path/to/db.json')
>>> User = Query()
>>> db.insert({'name': 'John', 'age': 22})
>>> db.search(User.name == 'John')
[{'name': 'John', 'age': 22}]
However, the documentation mentions that it's not the right tool if we need:
- access from multiple processes or threads,
- creating indexes for tables,
- an HTTP server,
- managing relationships between tables or similar,
- ACID guarantees
So it's a half solution :)
Oher solutions
Seems interesting too : WhiteDB