1

I'm trying to query an element within a list of dicts in a Django JSONField. I can find similar questions to mine but not one that covers what I'm trying to do. It seems like it should be simple and fairly common so I suspect I'm missing something simple.

So. to expand slightly on the example in the Django docs

Let's say I have a Dog model with a JSONField named data and it has some data like so:

Dog.objects.create(name='Rufus',
    data = {"other_pets":
            [{"name", "Fishy"},
             {"name": "Rover"},
             {"name": "Dave"}]}
)

There's a key named "other_pets" which is a list of dicts. I would like to write a query which returns any models which includes a dict with the key name=="Dave"

I am able to directly do this if I refer to the element by the index, eg:

Dog.objects.filter(data__other_pets__2="Dave")

Would return me the row, but I need to reference the index of the list element, and both the position of the item I'm looking up, and the size of the list vary among models.

I thought that maybe:

Dog.objects.filter(data__other_pets__contains={"name":"Dave"})

Would work but this returns no elements.

Is what I'm trying to do possible? As an aside, is it possible to add an index for queries like this?

Thanks!

Ludo
  • 2,739
  • 2
  • 28
  • 42
  • Don't use JSONField's for structured data: relational databases are not good with filtering, aggregating, etc. data in JSON blobs. – Willem Van Onsem Apr 28 '22 at 08:12
  • Thanks Will, I hear you. This is for data which comes as JSON imported from elsewhere, so I'm just dumping it into tables more or less and am picking through it to put it into its eventual home where it will be queried from for day to day use as I don't know for sure what I do or don't need to start with, – Ludo Apr 28 '22 at 13:15
  • but then still I would advise to process it, and create an extra table and thus create records... – Willem Van Onsem Apr 28 '22 at 13:39
  • Yes - that's exactly what I'm doing. – Ludo May 03 '22 at 09:38

1 Answers1

2

Since other_pets is a list, you should pass a list to the contains filter

Dog.objects.filter(data__other_pets__contains=[{"name":"Dave"}])
Iain Shelvington
  • 31,030
  • 3
  • 31
  • 50