How to judge if an element is in list as defined Jsonfield?

Question

I use peewee related with an exsits table:

import peewee
from playhouse.postgres_ext import *
class Rules(peewee.Model):
    channels = JSONField(null=True)
    remark = peewee.CharField(max_length=500,  null=True)
    class Meta:
        database = db
        db_table = 'biz_rule'
        schema = 'opr'

example: in my table there exists a record in column channels:

["A012102","C012102","D012102","E012102"]

I want to judge whether "A012102" is in the list,how to write the code?

Answer 1

If you're using PostgreSQL 9.4+, you can use the jsonb data type using the corresponding postgres_ext.BinaryJSONField peewee field type. It has contains_any() and contains_all() methods that correspond to the PostgreSQL ?| and ?& operators (see the PostgreSQL JSON docs). So I think it'd be something like this:

from playhouse.postgres_ext import BinaryJSONField

class Rules(peewee.Model):
    channels = BinaryJSONField(null=True)
    ...

query = Rules.select().where(Rules.channels.contains_all('A012102'))