Django模型JSONField查询与is_returned字段更新方法求助

我正在开发一个使用Django JSONField存储实例JSON数据的项目，按照官方文档查询对象时未达预期，无法查询并更新JSONField内is_returned键的值。以下是相关详情：

模型代码 #

class PosOrder(models.Model):
    store = models.ForeignKey('store.Store', on_delete=models.CASCADE, null=True)
    order_number = models.CharField(max_length=30, null=True)
    customer_phone = models.CharField(max_length=100, null=True, blank=True)
    order_date = models.DateTimeField(auto_now_add=True)
    order_total = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    order_discount = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    order_tax = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    coupon = models.ForeignKey('shoppingcoupon.ShoppingCoupon', on_delete=models.PROTECT, null=True, blank=True)
    order_grand_total = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    payment_method = models.CharField(max_length=100, null=True, blank=True, choices=(
        ('cash', 'Cash'), ('card', 'Card'), ('online', 'Online')))
    order_status = models.CharField(max_length=100, null=True, blank=True, choices=order_status)
    payment_status = models.CharField(max_length=100, null=True, blank=True, choices=payment_status)
    payment_id = models.CharField(max_length=100, null=True, blank=True)
    complete = models.BooleanField(default=False)
    order_data = models.JSONField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    created_by = models.ForeignKey('hrms.Employee', on_delete=models.CASCADE, null=True, blank=True)

JSONField示例数据 #

{
    "id": 408,
    "store": {"id": 1, "name": "Salt Lake"},
    "order_number": "POS408",
    "customer_phone": "9877454577",
    "order_status": "processed",
    "payment_status": null,
    "payment_method": "cash",
    "created_at": "2022-08-09T11:38:37.207775+05:30",
    "created_by": {"id": 5, "code": "RMS003"},
    "get_order_total": 39206,
    "get_order_total_discount_amount": 2232,
    "get_order_total_without_tax": 32730,
    "get_order_total_tax": 6476,
    "ordered_items": [
        {
            "id": 41,
            "item": {
                "id": 3,
                "product": {
                    "id": 3,
                    "sku": "TM002",
                    "name": "Striped Men Polo Neck White, Blue, Yellow T-Shirt",
                    "barcode": "552123",
                    "manufacturer": null,
                    "created_at": "2022-08-06T15:04:05.509651+05:30",
                    "category": 1,
                    "brand": null,
                    "size": null,
                    "color": null,
                    "vendor": null,
                    "created_by": 9
                },
                "mrp": "1599.00",
                "price_without_tax": "1427.68",
                "tax": 12,
                "tax_amount": "154.19",
                "discount_percentage": "10.00",
                "discount_amount": "142.77",
                "price": "1439.10",
                "stock": 100,
                "created_at": "2022-08-07T17:13:33.640401+05:30",
                "updated_at": "2022-08-07T17:13:33.639749+05:30",
                "store": 1,
                "created_by": 9,
                "offers": []
            },
            "quantity": 2,
            "mrp": "1599.00",
            "price": "1439.10",
            "discount": "319.80",
            "tax": "22.84",
            "mrp_total": "3198.00",
            "total_without_tax": "2855.36",
            "total": "2878.20",
            "is_returned": false,
            "is_exchanged": false,
            "order": 408,
            "offer": null
        },
        {
            "id": 42,
            "item": {
                "id": 2,
                "product": {
                    "id": 2,
                    "sku": "TFM01",
                    "name": "Demo 1",
                    "barcode": "012345",
                    "manufacturer": null,
                    "created_at": "2022-07-29T14:32:14.900200+05:30",
                    "category": 1,
                    "brand": 1,
                    "size": 1,
                    "color": 1,
                    "vendor": null,
                    "created_by": null
                },
                "mrp": "38240.00",
                "price_without_tax": "29875.00",
                "tax": 28,
                "tax_amount": "7946.75",
                "discount_percentage": "5.00",
                "discount_amount": "1493.75",
                "price": "36328.00",
                "stock": 100,
                "created_at": "2022-08-06T12:15:45.279121+05:30",
                "updated_at": "2022-08-06T12:15:38+05:30",
                "store": 1,
                "created_by": 1,
                "offers": [1, 2]
            },
            "quantity": 1,
            "mrp": "38240.00",
            "price": "36328.00",
            "discount": "1912.00",
            "tax": "6453.00",
            "mrp_total": "38240.00",
            "total_without_tax": "29875.00",
            "total": "36328.00",
            "is_returned": false,
            "is_exchanged": false,
            "order": 408,
            "offer": null
        }
    ]
}

需求 #

更新JSONField中ordered_items数组内指定项的is_returned键值。

方式一：取出对象修改后保存（简单场景适用） #

如果只需更新单个订单内的指定商品，先查询出PosOrder对象，修改order_data中的对应字段后保存：

# 示例：更新订单号POS408中，ordered_items里id为41的商品is_returned为True
order = PosOrder.objects.get(order_number="POS408")
for item in order.order_data["ordered_items"]:
    if item["id"] == 41:
        item["is_returned"] = True
        break
order.save()

这种方式直观易懂，但数据量大时效率较低，需将整个JSON数据拉取到内存修改后再写入数据库。

方式二：使用Django JSON路径表达式直接更新（高效批量操作） #

Django 3.1及以上版本支持JSON路径查询与更新，可直接通过数据库操作修改JSON内字段，无需拉取整个对象：

1. 更新指定订单内的特定商品

from django.db.models import F
from django.db.models.functions import JSONObject

PosOrder.objects.filter(
    order_number="POS408",
    order_data__ordered_items__contains=[{"id": 41}]
).update(
    order_data=F('order_data').transform(
        lambda obj: [
            JSONObject(**{**item, "is_returned": True}) if item["id"] == 41 else item
            for item in obj["ordered_items"]
        ],
        path=["ordered_items"]
    )
)

2. 批量更新符合条件的商品

from django.db.models import F
from django.db.models.functions import JSONObject

# 示例：将所有ordered_items中商品sku为TM002的is_returned设为True
PosOrder.objects.filter(
    order_data__ordered_items__item__product__sku="TM002"
).update(
    order_data=F('order_data').transform(
        lambda obj: [
            JSONObject(**{**item, "is_returned": True}) if item["item"]["product"]["sku"] == "TM002" else item
            for item in obj["ordered_items"]
        ],
        path=["ordered_items"]
    )
)

注意事项 #

• 需确保Django版本≥3.1，低版本需升级或使用方式一；
• transform方法依赖数据库的JSON函数（如PostgreSQL的jsonb_set），在数据库层面执行修改，比拉取对象更高效；
• 查询条件中__contains用于匹配数组中的对象，__可深入JSON层级访问嵌套字段。

内容的提问来源于stack exchange，提问作者Kuldeep