Django filter JSONField list of dicts

Question

I run Django 1.9 with the new JSONField and have the following Test model :

class Test(TimeStampedModel):
    actions = JSONField()

Let's say the action JSONField looks like this :

[
  {
    "fixed_key_1": "foo1",
    "fixed_key_2": {
      "random_key_1": "bar1",
      "random_key_2": "bar2",
    }
  },
  {
    "fixed_key_1": "foo2",
    "fixed_key_2": {
      "random_key_3": "bar2",
      "random_key_4": "bar3",
    }
  }
]

I want to be able to filter the foo1 and foo2 keys for every item of the list. When I do :

>>> Test.objects.filter(actions__1__fixed_key_1="foo2")

The Test is in the queryset. But when I do :

>>> Test.objects.filter(actions__0__fixed_key_1="foo2")

It isn't, which makes sense. I want to do something like :

>>> Test.objects.filter(actions__values__fixed_key_1="foo2")

Or

>>> Test.objects.filter(actions__values__fixed_key_2__values__contains="bar3")

And have the Test in the queryset.

Any idea if this can be done and how ?

score 34 · Answer 1 · answered Feb 12 '16 at 15:59

If you wan't to filter your data by one of fields in your array of dicts, you can try this query:

Test.objects.filter(actions__contains=[{'fixed_key_1': 'foo2'}])

It will list all Test objects that have at least one object in actions field that contains key fixed_key_1 of value foo2.

Also it should work for nested lookup, even if you don't know actual indexes:

Test(actions=[
    {'fixed_key_1': 'foo4', 'fixed_key_3': [
        {'key1': 'foo2'},
    ]}
}).save()

Test.objects.filter(actions__contains=[{'fixed_key_3': [{'key1': 'foo2'}]}])

In simple words, contains will ignore everything else.

Unfortunately, if nested element is an object, you must know key name. Lookup by value won't work in that case.

While trying the first one, I got this error:- `TypeError: Lookup type 'contains' not supported with list argument`, which got reduced by manipulating query as `Test.objects.filter(actions__contains={'fixed_key_1': 'foo2'})` — Manish Shah, Sep 23 '22 at 08:02

score 11 · Answer 2 · edited Jan 29 '19 at 00:57

11

You should be able to use a __contains lookup for this and pass queried values as list as documented here. The lookup would behave exactly like ArrayField. So, something like this should work:

Test.objects.filter(actions__contains=[{'fixed_key_1': 'foo2'}])

edited Jan 29 '19 at 00:57

Stan Reduta

3,292
5
31
55

answered Dec 18 '15 at 15:52

Joey Wilhelm

5,729
1
28
42

Dhia · Answer 3 · 2015-12-18T16:10:53.810

3

You can use the django-jsonfield package, I guess it's already the one you are using.

from jsonfield import JSONField
class Test(TimeStampedModel):
    actions = JSONField()

So to search to make a search with a specific property, you can just do this:

def test_filter(**kwargs):
    result = Test.objects.filter(actions__contains=kwargs)
    return result

If you are using PostgreSQL, maybe you can take advantage of PostgreSQL specific model fields.

PS: If you are dealing with a lot of JSON structure you have maybe to consider using NoSQL Database.

edited Dec 18 '15 at 16:10

answered Dec 18 '15 at 15:27

Dhia

10,119
11
58
69

1

I'm actually already using PostgreSQL's JSONField specific model field (`from django.contrib.postgres.fields import JSONField`). Your solution works when `your_property` is known (`fixed_key_1` and `fixed_key_2` in my case) but how can I do when I don't know `your_property` (`random_key_#` in my case) ? – Scentle5S Dec 18 '15 at 15:48
You should make `{'fixed_key_1': 'foo2'}` your paramter, I updated the code with generic function. – Dhia Dec 18 '15 at 15:57
1

The thing is I don't especially know `your_property`. It could be anything and I don't even care about it, just want to know if the JSONField contains a given string in any of its values, regardless the depth. – Scentle5S Dec 18 '15 at 16:07
Yes as I explained, you can insert whatever variable to the `test_filter` method and if the result is an empty list, this means it does not contain the given parameter, otherwise the list will be not be empty and this means it contains the variable. – Dhia Dec 18 '15 at 16:16
1

Could you please give an exemple of what you would insert to your `test_filter` method to see if `"bar3"` is contained anywhere in the JSONField, knowing that you **don't know** any key except `fixed_key_1` and `fixed_key_2` ? 'Cause I don't think that we're talking about the same problem. – Scentle5S Dec 18 '15 at 16:32
`Test.objects.filter(actions__contains="bar3")` should work. – Dhia Dec 19 '15 at 10:55
1

This doesn't work. It says that this is not a valid syntax for the json type. – Scentle5S Dec 20 '15 at 19:40

Django filter JSONField list of dicts

3 Answers3

Linked

Related