> On 1 Jun 2020, at 20:18, Shaheed Haque <shaheedhaque@gmail.com> wrote: > > Hi, > > I'm using Django's ORM to access Postgres12. My "MyModel" table has a JSONB column called 'snapshot'. In Python terms, each row's 'snapshot' looks like this: > > ====================== > snapshot = { > 'pay_definition' : { > '1234': {..., 'name': 'foo', ...}, > '99': {..., 'name': 'bar', ...}, > } > ====================== > > I'd like to find all unique values of 'name' in all rows of MyModel. I have this working using native JSON functions from the ORM like this: > > ===================== > class PayDef(Func): > function='to_jsonb' > template="%(function)s(row_to_json(jsonb_each(%(expressions)s->'pay_definition'))->'value'->'name')" > > MyModel.objects.annotate(paydef=PayDef(F('snapshot'))).order_by().distinct('paydef').values_list('paydef', flat=True) > ===================== > > So, skipping the ordering/distinct/ORM parts, the core looks like this: > > to_jsonb(row_to_json(jsonb_each('snapshot'->'pay_definition'))->'value'->'name’)
I do something like this to get a set of sub-paths in a JSONB field (no idea how to write that in Django):
select snapshot->’pay_definition’->k.value->’name’ from MyModel join lateral jsonb_object_keys(snapshot->’pay_definition’) k(value) on true
I was unaware of the LATERAL keyword, so thanks. After a bit of Googling however, it seems that it is tricky/impossible to use from the ORM (barring a full scale escape to a "raw" query). One question: as a novice here, I think I understand the right hand side of your JOIN "... k(value)" is shorthand for:
... AS table_name(column_name)
except that I don't see any clues in the docs that jsonb_object_keys() is a "table function". Can you kindly clarify?
I don’t know how that compares performance-wise to using jsonb_each, but perhaps worth a try. Obviously, the way it’s written above it doesn’t return distinct values of ’name’ yet, but that’s fairly easy to remedy.
Indeed; this is what I managed to get to:
SELECT DISTINCT snapshot -> 'pay_definition' -> k.value -> 'name' AS name FROM paiyroll_payrun JOIN LATERAL jsonb_object_keys(snapshot -> 'pay_definition') AS k(value) ON true ORDER BY name;
At any rate, I'll have to ponder the "raw" route absent some way to "JOIN LATERAL".
Thanks, Shaheed
Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
