3

I would like to make a queryset filter to identify all model instance that have a given number of keys in a jsonfield of the model.

I have tried to create a custom lookup (#1) to extra the keys in the json field,

and would like to aggregate those into an array and use the __len lookup of the array to make my filter. Unfortunately, I am stuck at the aggregation that doesn't seem to work (#2).

1

    class JsonKeys(Transform):
        output_field = TextField()
        lookup_name = 'keys'
        function = 'jsonb_object_keys'

2

    qs = qs.annotate(keysArray=ArrayAgg("myJsonField__keys"))

The error that I get is: HINT: You might be able to move the set-returning function into a LATERAL FROM item.

Anyone has already tried to perform this task ?

Community
  • 1
  • 1
user2357068
  • 111
  • 1
  • 4
  • Unless Django is sharing write access to this DB table with other code, you could define the `save` method on the model to count the keys and store the count either in the JSON or in a model IntegerField, before handing over to the superclass. Then just load and save every object to ensure that the count is always present and correct. – nigel222 Aug 13 '19 at 12:10
  • you're not sharing the actual error, just part of (the HINT). Can you share the full error? – dirkgroten Aug 13 '19 at 13:17
  • can you try giving your lookup another name than 'keys'? I think it's clashing with the built-in 'keys' lookup for `HStore` objects. – dirkgroten Aug 13 '19 at 13:21
  • This https://stackoverflow.com/a/53894414/10534470 might be helpful. – eshaan7 Apr 05 '21 at 10:35

0 Answers0