How do I query a complex JSONB field in Django 1.9

Question

I have a table item with a field called data of type JSONB. I would like to query all items that have text that equals 'Super'. I am trying to do this currently by doing this:

Item.objects.filter(Q(data__areas__texts__text='Super'))

Django debug toolbar is reporting the query used for this is:

WHERE "item"."data" #> ARRAY['areas', 'texts', 'text'] = '"Super"'

But I'm not getting back any matching results. How can I query this using Django? If it's not possible in Django, then how can I query this in Postgresql?

Here's an example of the contents of the data field:

{
  "areas": [
    {
      "texts": [
        {
          "text": "Super"
        }
      ]
    },
    {
      "texts": [
        {
          "text": "Duper"
        }
      ]
    }
  ]
}

Answer 1

try Item.objects.filter(data__areas__0__texts__0__text='Super')

it is not exact answer, but it can clarify some jsonb filter features, also read django docs

I am not sure what you want to achieve with this structure, but I was able to get the desired result only with strange raw query, it can look like this:

Item.objects.raw("SELECT id, data FROM (SELECT id, data, jsonb_array_elements(\"table_name\".\"data\" #> '{areas}') as areas_data from \"table_name\") foo WHERE areas_data #> '{texts}' @> '[{\"text\": \"Super\"}]'")

Dont forget to change table_name in query (in your case it should be yourappname_item).

Not sure you can use this query in real programs, but it probably can help you to find a way for a better solution.

Also, there is very good intro to jsonb query syntax

Hope it will help you