Re: Using JSONB with nested key-value structures, and skipping/wildcarding the key

[Shaheed Haque <shaheedhaque@xxxxxxxxx>]

Hi,

On Mon, 1 Jun 2020 at 23:50, Alban Hertroys <haramrae@xxxxxxxxx> wrote:

> On 1 Jun 2020, at 20:18, Shaheed Haque <shaheedhaque@xxxxxxxxx> 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.