Chad Chad - 2 months ago 14
JSON Question

How do I query a complex JSONB field in Django 1.9

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

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

Comments