Flat file NoSQL solution [closed]

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":"a@b.com"}');sqlite> SELECT * FROM test WHERE json_extract(data, '$.balance') > 1500;data--------------------------------------------------{"name":"alice","balance":2000,"email":"a@b.com"}

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': 'a@b.com'})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': 'a@b.com'}

• 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