6 ms·
I’ll try to avoid a flame war, but since you’re using python, SQLAlchemy allows for composing sql strings.
by aidos 8y ago
I’ll try to avoid a flame war, but since you’re using python, SQLAlchemy allows for composing sql strings.
- raverbashing 8y agoYes, an ORM, not SQL itself.
- yawaramin 8y agoYes? What's the qualitative difference between using a best-of-breed SQL ORM and the Mongo API?
- zrail 8y agoSQLAlchemy is not an ORM. There’s a companion ORM project if you want it, but it’s not necessary.
- walrus1066 8y agoYou can compose queries, using the queryAPI in SQLAlchemy, without touching the ORM.
- quietbritishjim 8y agoFor it to replace MongoDB's aggregation pipeline, it would need to play nicely with JSONB. Does it do that? This is the thing I'm really missing. For example, if documents in the JSONB column all look roughly like this: { "someArrayField": [ { "key": "steve", "value": 7 }, { "key": "bob", "value": 15 }, ], "someOtherField": [ "whatever" ] } * Can I count the number of entries in someArrayField, summed across all records? * Can I get the per-record mean of the "value" sub-field, summed across all records? * Can I filter by records that have a "someArrayField" entry where "key" is "steve" and "value" is at least 10 (the above record should NOT match)?
- luhn 8y agoYes, you can! The jsonb_array_elements function is roughly similar to Mongo’s $unwind pipeline op. It explodes a JSON array into a set of rows. From there it’s pretty simple aggregates to achieve what you’re looking for. I was evaluating Mongo a couple months back to solve roughly the same problems. Eventually discovered Postgres already had what I was looking for.
- inferiorhuman 8y agoMore the point Postgres has an actual array data type (and has for a while). You don't need to shove everything into a JSON/JSONB blob unless you absolutely cannot have any sort of schema.
- zaarn 8y agoNot only arrays, you can, with some limitations, create proper types with field names, if your ORM supports that you should use that over JSONB if it fits.
- inferiorhuman 8y agoSee also: PostGIS.
- quietbritishjim 8y agoAllow me to restate this question: > Does it do that? It was supposed to be clear from the context that this meant: > Does building queries programmatically with SQLAlchemy do that? Maybe I'm misreading your comment, but you seem to just be talking about writing queries directly in SQL. If not, could you give an example/link of how to programmically build a query in SQLAlchemy that dynamically makes use of jsonb_array_elements? It would be hugely useful if I could do that.
- simonw 8y agoThere are some old examples of how to use jsonb_array_elemens in SQLAlchemy here: https://github.com/sqlalchemy/sqlalchemy/issues/3566#issuecomment-441931331 https://github.com/sqlalchemy/sqlalchemy/issues/3566#issueco...
- ris 8y agoIt absolutely can, but in my experience, 99% of the time, choosing to make a data field JSON/JSONB ends up being a mistake.