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 and json_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