Django ORM fails to generate valid sql for JSONb contains

lets start with the error first from my logs:

2025-10-21 19:18:11,380 ERROR api.services.observium_port_status_service Error getting port status from store: invalid input syntax for type json
LINE 1: ..." WHERE "api_jsonstatestore"."filter_key_json" @> '''{"type"...
                                                             ^
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: '...

and the query:

state = (
   JsonStateStore.objects.select_for_update()
   .filter(filter_key_json__contains={"type": "observium_port_status", "observium_port_id": observium_port_id})
   .first()
)

here is an example record

and the model:

class JsonStateStore(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    touched = models.DateTimeField(auto_now=True)
    filter_key_json = models.JSONField()
    data_json = models.JSONField()
    
    class Meta:
        verbose_name = "JSON State Store"
        verbose_name_plural = "JSON State Stores"

    def __str__(self):
        return f"JsonStateStore(key={self.filter_key_json}, created={self.created}, touched={self.touched})"

    def save(self, *args, **kwargs):
        self.touched = timezone.now()
        super().save(*args, **kwargs)

I am on django 4.2.2
my database backend is timescale.db.backends.postgis (github | pypi) and i am on version 0.2.13 of that package

I cannot identify any syntax error in the QuerySet call and I cannot figure out what is going wrong here

My current workaround is

lock_sql = (
   """
   SELECT id, created, touched, filter_key_json, data_json
   FROM api_jsonstatestore
   WHERE filter_key_json @> %s::jsonb
   ORDER BY id ASC
   LIMIT 1
   FOR UPDATE
   """
)
payload = {"type": "observium_port_status", "observium_port_id": observium_port_id}
records = list(JsonStateStore.objects.raw(lock_sql, [json.dumps(payload)]))
state = records[0] if records else None

if anyone has any insight on why the ORM method does not work and would like to share I would appreciate it

Вернуться на верх