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!