Django JSONfield Inside Arrayfield

Django JSONField inside ArrayField

Arrays

First of all, let's take a close look at this important text from the Postgresql Arrays document.

Tip: Arrays are not sets; searching for specific array elements can be
a sign of database misdesign. Consider using a separate table with a
row for each item that would be an array element. This will be easier
to search, and is likely to scale better for a large number of
elements.

Most of the time, you should not be using arrays.

JSONB

JSONB is available in Django as the JSONField type. This field is more scalable and flexible than array fields and can be searched more efficiently. However if you find yourself searching inside JSONB fields all the time the above statement about Arrays is equally valid for JSONB.

Now what do you have in your system? A an array that holds JSONB field. This is a disaster waiting to happen. Please normalize your data.

Recap

so when to use ArrayField?

On the rare occasion when you don't need to search in that column and you don't need to use that column for a join.

ArrayField with JSONField as base_field in Django

You don't need to wrap the JSON data in an ArrayField. You can store your address_components list directly into a JSONField.

Difference between JSONField and ArrayField

No, you're not correct in that assumption. You can make PostgreSQL arrays of any type that PostgreSQL supports but JSON only supports strings, integers, floating point numbers, booleans, JavaScript-style objects, and arrays.

For example, you can create an array of timestamps or dates but to store those types in JSON you end converting them to strings (or sometimes numbers) and store those strings. Then, if you need to use the time functions or operators, you'd have to dig the strings out of the JSON arrays, convert them into timestamps or dates, and finally apply the functions or operators.

Similarly for all the other data types that PostgreSQL understands that JSON doesn't.

The array functions and operators and JSON (array) functions and operators are comparable but the data types are not.

JSONField() does not save properly when nested as a child of ArrayField()

@jDO it seems your comment is correct. There is no need to nest JSONField in ArrayField as JSONField supports lists. Here is the updated code:

# models.py
class Foo(models.Model):
    bar = JSONField(default=list([]))

# app.py
...
data = request.data #ie. [{...}, {...}] 
Foo(bar=data)

# works!

How to filter JSON Array in Django JSONField

The right answer should be:

myModel.objects.filter(tasks__contains=[{"task":"test"}])

You may want to add more filters to narrow down and speed up the query if needed, something like

myModel.objects.filter(Q(tasks_level=10, tasks__contains=[{"task":"test"}]))

Django JsonField Array data query

In django's documentation for querying JsonFields:

If the key is an integer, it will be interpreted as an index lookup in an array

As your json data is list of json datas, you need a query like this:

Employee.objects.filter(income_info__0__min_income__lte=4000000)

Not able to insert data into ArrayField while using psqlextra.backend with django

After further digging up, this was a known bug in Django<2.2

https://code.djangoproject.com/ticket/28291

Upgrading django did the job!

---

Related Topics