Filtering Django JSONB items in list

Question

I have a Django data JSONField with the following data in

{
    "title": "Some Title",
    "projects": [
        {"name": "Project 1", "score": "5"},
        {"name": "Project 2", "score": "10"},
        {"name": "Project 3", "score": "2"},
    ]
}

And I need to filter rows by project scores (numeric type), i.e. where project -> score <= 5

Django ORM provides the basics of filtering and working with a Postgres JSONB field. That is, I can do

Model.objects.filter(data__title="Some Title")

But it is not so straight forward when having a list of items in the JSON column, i.e. I can't do

Model.objects.filter(data__projects__score__lte=5)

Only way I can reach the `score` in Django ORM is to use the array index, i.e.

Model.objects.filter(data__projects__0__score__lte=5)

But that doesn't really work now, I'm interested in all items in that list, which can be however many

Where I am currently:

I've joined that list to the queryset as a jsonb_array_elements

queryset.query.join(join=join_cfg)

Which appended the following sql to the QUERY

LEFT JOIN LATERAL jsonb_array_elements("my_table"."data" -> 'projects') projects ON TRUE

Now, in Raw SQL, I can do something like this to filter that list

WHERE (projects->>'score')::numeric < 5

Problem is I can't get this to play nicely with the Django ORM

Currently, I'm adding this WHERE clause using queryset.extra()

queryset = queryset.extra(where=where_clause)

Problem with this is that by using .extra(), the WHERE clause is ANDed at the end of the query, which produces unexpected results in combination with other filters that are done using Django Q objects.

I.e. The final WHERE clause generated looks like this:

WHERE condition1 AND (condition2 OR condition3) AND MY_CUSTOM_WHERE_CONDITION

// but what I need is 

WHERE condition1 AND (condition2 OR condition3 OR MY_CUSTOM_WHERE_CONDITION)

Ideally, this custom condition should be added with a Q object, i.e.

query_filters |= Q(projects__score__lte=5)

But with the projects column being a custom join, I get Cannot resolve keyword 'projects' into field. So I can't use the django ORM on it to filter the same way I filter title in the beginning of this post

I think this custom join has to be annotated somehow and then use the Q object on that annotated column, but I can't get this to work.

I've tried, extra select, annotations with RawSQL, FilteredRelation, everything..

Any help is appreciated. Thank you

Filtering Django JSONB items in list

0 Answers