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 stackoverflow.com/questions/34358278/… — Andrei Berenda
– Andrei Berenda, Commented May 24, 2018 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
– freethebees, Commented May 25, 2018 at 8:13

Georg Zimmer · Accepted Answer · 2019-04-09 12:59:16Z

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 9, 2019 at 12:59

answered Jan 29, 2019 at 21:09

Georg Zimmer

9081 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

antonzy Over a year ago

Is there a way for this to behave as a LIKE operator? (i.e. 'FIND' lookup will also match 'FINDME')

Georg Zimmer Over a year ago

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.

Collectives™ on Stack Overflow

Django: filter JSONField with multiple nested arrays

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related