Django RawSql query to do filter on nested json

Question

My Models looks like this

class Foo():
  payload = JSONField()

Payload looks something like this

[{
  "id": 12,
   "names": [{"location": "New York"}, ]{"location": "Philly"}]
 },
 {
  "id": 15,
   "names": [{"location": "Paris"}, ]{"location": "Cannes"}]
 }]

queryset = Foo.objects.all()
queryset = queryset.filter(payload__names__contains=[{"name": "Paris"}]) #this works!!
queryset = queryset.filter(payload__names__contains=[{"name": "Par"}]) #does not work

The second statement does not work - it returns 0 results. In the first case it works because the full text is provided. What is the best way to do this in rawSql? [I am assuming it is not possible in Django]

I went thorough this but cannot find what to use to do like on a nested array json field.

This question may be related: https://stackoverflow.com/questions/14349889/how-to-use-a-regular-expression-to-extract-json-fields — Quentin, Jun 21 '17 at 22:27

score 0 · Accepted Answer · answered Jun 23 '17 at 17:19

0

It cannot be done as seen here - https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/fields/

My solution is to implement RawSql something like this - jsonb query with nested objects in an array

answered Jun 23 '17 at 17:19

suprita shankar

1,554
2
16
47

Django RawSql query to do filter on nested json

1 Answers1