Using JSONB with nested key-value structures, and skipping/wildcardingthe key - Mailing list pgsql-general

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:


My question is if this the best way to solve this problem? The way my current logic works, reading from inside out is, I think:

1. Pass in the 'snapshot'.
2. Since 'snapshot' is a JSON field, "->'pay_definition'" traverses this key.
3. To skip the unknown numeric keys, "jsonb_each()" turns each key, value pair into an inner row like ['1234', {...}].
4. To get to the value column of the inner row "row_to_json()->'value'".
5. To get the name field's value "->'name'".
6. A final call to "to_jsonb" in the PayDefs class. This bit is clearly Django-specific.

For example, I think the pair of calls row_to_json(jsonb_each()) is needed because there is no jsonb_object_values() to complement jsonb_object_keys(). Likewise, since all I care about is the string value of 'name', is there a way to get rid of the PayDefs class, and its invocation of to_jsonb (this is probably Django-specific)?

To provide context on what "better" might be:

• Snapshot JSONs might easily be 20MB in size.
• Each 'pay_definition' is probablyonly about 1kB in size, and there might be 50 of them in a snapshot.
• There might be 1000 MyModel instances in a given query.
• I'm using PostgreSQL 12

so my concern is not have the database server or Django perform extraneous work converting between strings and JSON for example.

Thanks, Shaheed

P.S. I posted a Django-centric version of this to the relevant mailing list but got no replies; nevertheless, apologies for the cross post.