Django中JSONField的模糊查询

项目需求多个条件并联模糊查询

筛选条件中需要使用OR查询于是用Q对象查询先说结果使用字段名__键名__icontains=keyword 解决

# 客户地址表
class UserAddress(models.Model):
    """
    用户地址表
    region 所在地区
        {
            "address":"详细地址",
            "city_code":"市级code",
            "city_name":"市级名称",
            "district_code":"区级code",
            "district_name":"区级名称",
            "province_code":"省级code",
            "province_name":"省级名称"
        }
    """

    address_id = models.AutoField(primary_key=True)
    receiving_unit = models.CharField(max_length=32)  # 收货单位
    consignee = models.CharField(max_length=8)  # 收货人
    phone_number = models.CharField(max_length=11)  # 手机号码
    region = models.JSONField(default=dict)  # 所在地区
    is_default = models.BooleanField(default=0)  # 是否默认收货地址（默认否）
    user = models.ForeignKey(CustomerManagement, **MP, related_name="user_address")
    using = models.BooleanField(default=1)
    create_date = models.DateTimeField(auto_now_add=True)
    update_date = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "customer_address"


q_condition = Q(consignee__contains=keyword) | Q(receiving_unit__contains=keyword) | Q(
                phone_number__contains=keyword) | Q(region__province_name__icontains=keyword) | Q(
                region__city_name__icontains=keyword) | Q(region__city_name__icontains=keyword) | Q(
                region__address__icontains=keyword)

开始选择的是contains 结果查询结果为空也就是说被筛选掉了查看原SQL

SELECT `customer_address`.`address_id`, `customer_address`.`receiving_unit`, `customer_address`.`consignee`, `customer_address`.`phone_number`, `customer_address`.`region`, `customer_address`.`is_default`, `customer_address`.`user_i
d` FROM `customer_address` WHERE ((`customer_address`.`consignee` LIKE BINARY %湖% OR `customer_address`.`receiving_unit` LIKE BINARY %湖% OR `customer_address`.`phone_number` LIKE BINARY %湖% OR JSON_CONTAINS(JSON_EXTRACT(`customer
_address`.`region`, $."province_name"), "\u6e56") OR JSON_CONTAINS(JSON_EXTRACT(`customer_address`.`region`, $."city_name"), "\u6e56") OR JSON_CONTAINS(JSON_EXTRACT(`customer_address`.`region`, $."city_name"), "\u6e56") OR JSON_CONTAINS(JSON_EXTRACT(`customer_address`.`region`, $."address"), "\u6e56")) AND `customer_address`.`user_id` = 4 AND `customer_address`.`using`)

心里咯噔一下怎么是编码后的

于是开始百度.......各种contains语法都试了一遍实在是不行本来是准备直接写原生SQL的最后看到了这位网友发的这个

于是试了试成功查询到数据再看SQL

SELECT `customer_address`.`address_id`, `customer_address`.`receiving_unit`, `customer_address`.`consignee`, `customer_address`.`phone_number`, `customer_address`.`region`, `customer_address`.`is_default`, `customer_address`.`user_i
d` FROM `customer_address` WHERE ((`customer_address`.`consignee` LIKE BINARY %湖% OR `customer_address`.`receiving_unit` LIKE BINARY %湖% OR `customer_address`.`phone_number` LIKE BINARY %湖% OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(`cus
tomer_address`.`region`, $."province_name"))) LIKE LOWER(%湖%) OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(`customer_address`.`region`, $."city_name"))) LIKE LOWER(%湖%) OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(`customer_address`.`region`, $."city_name"))) LIKE LOWER(%湖%) OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(`customer_address`.`region`, $."address"))) LIKE LOWER(%湖%)) AND `customer_address`.`user_id` = 4 AND `customer_address`.`using`)

并未编码于是再次百度查查原因