Hacker News Reader

Comment by mlevental | Hacker News Reader

asked this on a sqllite thread before but: anyone know a single file nosqldb that has good python bindings. i know I could just write to json or something but I'd like something that supports queries. right now I'm using MongoDB and it's completely Overkill

View on HN

It depends on what you mean when you say you want it to "support queries", but then also ask for a "nosqldb". It really depends on whether you want a full query grammar (in that case, why not SQL?), or if a key/value store is enough.

I use lmdb pretty extensively. It is single-file (well, it has a lock file) key/value store, can be read-from/written-to by multiple processes, and embeds nicely. It has Python bindings, but I've only used it from C.

https://symas.com/lmdb/

i'm imagining something like the sqlite for mongo. ie you can do aggregations and nested k/v queries and filters and things like that.

SQLite has really good JSON support these days. Any reason not to use that?

as far as i could tell you have to jump through some hoops to use it (compile extension from source?). and i couldn't tell if it supported querying

The extension is included in the standard sqlite [amalgamation](https://www.sqlite.org/amalgamation.html), hence it's basically always available (ie you'd have to manually disable it) (ime).

It's worth noting that sqlite's typing is very flexible, so you probably want to add a `check (data is null or json_valid(data) = 1)` to ensure the data is _actually_ valid json.

You can then index based on json queries, eg `create index some_index on something((json_extract(data, '$.someField')))` and then do an indexed query, eg `select * from something where json_extract(data, '$.someField') = 'blah'`.

In our experience it's all working quite well :-)

(Groking json_each and json_tree took a few goes, but now is working well (eg we join onto json_each to allow a row "labels" containing a list of label (rather than a one-to-many table join for something_labels)).)

JSON on Sqlite works out of the box in ubuntu, but it doesn't in OpenBSD. Not sure about other environments.

It supports querying, see json_extract function.

https://www.sqlite.org/json1.html

I use it a lot to return JSON from normalized data, for apps to consume.

I'm unsure of what you mean by "single file", but rocksdb is embedded and has python bindings.

What kind of queries? There's LMDB, but it's mostly k/v: https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Databa...

Postgres supports querying json

Has anyone actually used json-based queries in PG in a nontrivial way, and had a good experience? I looked into it but the SQL syntax for doing json operations was crazy complicated, and I had trouble finding an ORM that did a good job of simplifying it.

Yeah, I don't mind it. Oftentimes when you want to manipulate JSON, you'll embed a raw snippet of PG-specific SQL into the query you're building with your ORM. I do wish the operators were a little more ergonomic though. One of these days I'll print out the list and stick it to my monitor.

What kind of non-trivial use cases do you have. I've found you generally just need to pick out a field using the JSON syntax, then you can use all of the normal SQL tools for fancier stuff...

If your queries are that complicated are you not better off normalising your data, then writing views to jsonify it?

For millions plus rows, the queries get crazy slow. If your jsonb field contains lot of data, JOINs on toast storage tables takes chunk of the query times.

My 2 cents: Best to keep data normalized and in separate columns. If you gotta keep data in jsonb, keep only the data that you don't need to query on. Anything you need to query, you better put it as a column. You'll thank me later.

Reply on news.ycombinator.com