Django JSONField复杂查询…查询复杂嵌套数据结构的实际示例

Question

我继承了以下JSONField数据结构:

[
{
"name": "Firstname",
"show": {
"value": true
},
"type": "text",
"uuid": "55668e45-07d1-404e-bf65-f6a3cacfaa97",
"label": {
"for": "Firstname",
"display": "First name"
},
"value": "Michael",
"options": [],
"required": true,
"component": "Input",
"placeholder": "Input text here",
"validationErrors": []
},
{
"name": "Surname",
"show": {
"value": true
},
"type": "text",
"uuid": "ce91fefa-66e3-4b08-8f1a-64d95771aa49",
"label": {
"for": "Surname",
"display": "Surname"
},
"value": "Roberts",
"options": [],
"required": true,
"component": "Input",
"placeholder": "Input text here",
"validationErrors": []
},
{
"name": "EmailAddress",
"show": {
"value": true
},
"type": "email",
"uuid": "6012a805-da62-4cee-8656-b7565b5f8756",
"label": {
"for": "Email",
"display": "Email"
},
"value": "[e-mail]",
"options": [],
"required": true,
"component": "Input",
"placeholder": "Input text here",
"validationErrors": []
},
{
"name": "University",
"show": {
"value": true
},
"type": "text",
"uuid": "434e3781-ab8a-4f09-9c68-5ec35188f3c7",
"label": {
"for": "University",
"display": "University/College"
},
"value": "University College London",
"options": [],
"required": true,
"component": "Input",
"placeholder": "Input text here",
"validationErrors": []
},
{
"name": "Subscribe",
"show": {
"value": true
},
"type": "checkbox",
"uuid": "79bdc29e-6357-4175-bf65-07be60776a29",
"label": {
"for": "Subscribe",
"display": "Subscribe to the KEVRI mailing list"
},
"value": true,
"options": [],
"required": true,
"component": "Checkbox",
"description": "KEVRI is committed to respecting and protecting your privacy. The data collected here will create your personalised report which we can email to you after this review if you wish. We will not share personal data with anyone else or send you any further emails.",
"placeholder": "",
"validationErrors": []
}
]

存在于模型上。json字段调用"about";";MyModel"，如下:

class MyModel(
AbstractTimestampedModel
):
uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
about = models.JSONField()

我想知道，我如何过滤MyModel，其中about中的字段由"名称"EmailAddress"…然后查询特定字段的"值"?

基本上，对于查询集MyModel.objects.all().filter()…我想过滤掉EmailAddress等于某个值的所有值

我不确定这在Django ORM中是否可以实现。然而，也许有人可以建议……

Answer 1

如果我是正确的，你应该使用jsonb_to_recordsetPostgreSQL函数。

首先你应该创建一个自定义的数据库函数，因为Django Core中没有这个函数。

class JSONRecordSet(Func):
template = "(SELECT id from %(function)s(%(expressions)s) as items(%(key)s %(output_type)s) where %(key)s='%(search)s')"
function = "jsonb_to_recordset"
def __init__(self, expression, key, output_type, search):
super().__init__(expression, key=key, output_type=output_type, search=search)

请注意SQL注入。

之后，你可以用annotate来使用这个函数

MyModel.objects.annotate(_id=JSONRecordSet(expression="about", key="EmailAddress", output_type="text", search="[e-mail]")).filter(id=F("_id"))

返回所有包含"[e-mail]"的MyModel实例

Answer 2

试试这个方法:

MyModel.objects.filter(about__name='EmailAddress')

它可能返回你想要的结果。

还有，看看这个链接。它还描述了如何使用JSONField查询嵌套字典:

https://docs.djangoproject.com/en/3.2/topics/db/queries/key-index-and-path-transforms