I’ll try to avoid a flame war, but since you’re using python, SQLAlchemy allows for composing sql strings.
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 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)?
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.
> 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.
SQLAlchemy’s Postgres JSONB type allows subscription, so you can do Model.col[‘arrayfield’]. You can also manually invoke the operator with Model.col.op(‘->’)(‘arrayfield’).
So you should be able to do something like:
func.sum(func.jsonb_array_elements(Model.col.op(‘->’)(‘arrayfield’)).op(‘->’)(‘val’))
(Writing on my mobile without reference, so may not be fully accurate)