CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
Gurkenkönig
Gurkenkönig

Reputation: 828

Filter List in Django JSON Field with string contains

My Django JSON field contains a list of values like ["N05BB01", "R06AX33"].

atc_code = JSONField(default=list())

I would like to filter this field, for 'does any string in list contain "N05"?'.

like

mymodel.objects.filter(?????)

Upvotes: 1

Views: 2214

Answers (2)

fannik
fannik

Reputation: 1153

Relational-based answer

Usually, such an approach (with a list of values in JSONField) can be possible when the relational structure is used in the wrong way.

The best approach here:

  1. Create a new model that describes your atc_code entity. For example AtcCode
  2. Depends on the meaning of the atc_code and its relation to the MainEnity use ForeignKeyField or ManyToManyField

Utilize all pros from the relational database and powerful Django ORM with such built-in features as filtering, adding, removing, querying with any database backend.

It will work on any supported database. A relational database will work faster when you are using relations properly.

My recommendation is to use JSONField when you have a really unstructured object.

Upvotes: 1

Brian Destura
Brian Destura

Reputation: 12068

In this case if you are not using SQLite or Oracle, you can use contains:

mymodel.objects.filter(atc_code__contains='N05')

Which generates this SQL:

SELECT * FROM "mymodel" WHERE UPPER("mymodel"."atc_code"::text) LIKE UPPER(%N05%)

Upvotes: 3

Related Questions