Django JSONField complex query ... practical example of querying complex nested data structure

Question

I have inherited the following JSONField data structure:

[
  {
    "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": "michael@hiyield.co.uk",
    "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": []
  }
]

which exists on the models.JSONField called "about" for "MyModel", as follows:

class MyModel(
    AbstractTimestampedModel
):

    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

    about = models.JSONField()

I was wondering, how do I filter MyModel where the field within about is called by the "name": "EmailAddress" ... then query for that particular fields "value"?

Essentially, for the queryset MyModel.objects.all().filter() ... I want to filter out all the values where the EmailAddress is equal to some value ...

I'm not sure this is achievable within the Django ORM. However, there might be someone who could advise ...

Answer 1

If i am correct you should use jsonb_to_recordset PostgreSQL function.

Firstly you should create a custom database function since there is no function for that in 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)

Please be aware of SQL injection.

After that, you can use this function with annotate.

MyModel.objects.annotate(_id=JSONRecordSet(expression="about", key="EmailAddress", output_type="text", search="foo@bar.com")).filter(id=F("_id"))

Return all MyModel instance which has "foo@bar.com" value in EmailAddress Key.

Answer 2

Try this approach:

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

It might return the result you want.

Also, have a look at this link. It also describes how to query into nested dictionary using JSONField:

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