Django: filter JSONField with multiple nested arrays

Question

I have a JSONField, called metadata on my model in Django.

The data in that fields might looks something like this

{
    "vis": {
        "plots": [
            // some objects here
            {
                "id": 1,
                "x": "foo",
                "y": "bar",
                "externalData": [
                    // some objects here
                    {
                        "x": "fa",
                        "y": "so",
                        "source": {
                            "name": "FINDME",
                            "location": "some other address"
                        }
                    },
                    // some more objects here
                ]
            },
            // some more objects here
        ],
        "somethingElse": []
    },
    "moreStuff": {}
}

I want to be able to filter models that have "name": "FINDME" in an externalData object (at any index), inside a plots object (also at any index).

I initially tried

MyModel.objects.filter(metadata__vis__plots__externalData__source__name='FINDME')

No good. Then I tried

MyModel.objects.filter(metadata__vis__plots__externalData__source__contains={'name':'FINDME'})

No good. Then I tried

MyModel.objects.filter(metadata__vis__plots__externalData__contains=[{'source': {'name':'FINDME'}}])

Still no luck. Finally, I tried

MyModel.objects.filter(metadata__vis__plots__contains=[{'externalData':[{'source': {'name': 'FINDME'}}]}])

Still no hits.

Clearly, I am doing this all wrong.

Any ideas?

EDIT: I've added some comments in the JSON to make it clear that I don't only have one object in each of my arrays. I am trying to find a 'plot' at an arbitrary index and an 'externalData' at an arbitrary index that contains that "source": {"name": "FINDME"}.

I think it can help use https://stackoverflow.com/questions/34358278/django-filter-jsonfield-list-of-dicts — Andrei Berenda, May 24 '18 at 17:17
My initial attempts came after reading that page. My problem is match _any_ index. I don't seem to be able to wrangle my query to make that work. — freethebees, May 25 '18 at 08:13

Georg Zimmer · Answer 1 · 2019-04-09T12:59:16.560

4

The structure of your contains needs to match the JSON, beginning with the first array. Each level of array in your JSON needs to be represented in the contains=

It is easier to show than to say. Like this:

MyModel.objects.filter(
     metadata__vis__plots__contains=[{'externalData': [{'source': {'name': 'FINDME'}}]}]
)

edited Apr 09 '19 at 12:59

answered Jan 29 '19 at 21:09

Georg Zimmer

899
1
7
13

1

Is there a way for this to behave as a LIKE operator? (i.e. 'FIND' lookup will also match 'FINDME') – antonzy Feb 04 '19 at 18:01
I have not found the answer to that. It is something that seems like it should be possible but I can't figure out how to make it do that. – Georg Zimmer Apr 09 '19 at 12:57

Django: filter JSONField with multiple nested arrays

1 Answers1