How to do `jsonb_array_elements` in where condition in django models

I have a model:

class Profile(models.Model):
    ...
    criteria = models.JSONField()

The criteria could look like:

{
  "product_uuids": [uuid1, uuid2, ...]
}

I need to run a query:

SELECT *
FROM profilemodel
WHERE deleted_at IS NULL and
      (criteria -> 'product_uuids')::text !='null' AND
      NOT EXISTS(
          select product_uuid::varchar
          from jsonb_array_elements(criteria -> 'product_uuids') as product_uuid
          where product_uuid::varchar not in (
              select my_product::varchar 
              from jsonb_array_elements('["uuid1", "uuid2", ...]'::jsonb) as my_product)
        )

The query wants to get all profiles that don't have any product uuid that's not in the expected list ["uuid1", "uuid2", ...].

I tried to annotate the jsonb_array_elements(criteria -> 'product_uuids') on the profile model, but it's not using the outer layer's model, the query was not generated as expected.

Would love to get some suggestions here. Thanks!

Please try this FYR

Profile.objects.filter(criteria__product_uuids = '..')
Back to Top