Django JSONField inside ArrayField
Solution 1
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.
Solution 2
I have encountered the same scenario. Here is the way how I solved it
models.py
from django.contrib.postgres.fields.jsonb import JSONField as JSONBField
location = JSONBField(default=list,null=True,blank=True)
insert
model_object.location = [{"locations" : "loc1","amount":Decimal(100.00)},{"locations" : "loc2","amount":Decimal(200.25)}]
update
model_object.location.append({"locations" : "loc1","amount":Decimal(100.00)})
model_object.save()
This worked for me in Django - 2.0.2 Postgres - 9.5 psycopg2 - 2.7.4 python - 3.4.3
Solution 3
You can sidestep this issue by using the JSONField as the column field type with a list
as the root element.
from django.contrib.postgres.fields import JSONField
class MyDBArray(models.Model):
array_data = models.JSONField(default=list)
my_db_array = MyDBArray(array_data=[1, 2, 3])
my_db_array.save()
You would need to validate in the save
method that the array_data
field is actually list-like.
Solution 4
This was fixed in the latest unreleased version of Django 2.2a1
pip install Django==2.2a1
PS I believe that it will work with versions >= 2.2a1
Related videos on Youtube
Comments
-
aldesabido almost 2 years
I have a problem inserting to a field using ArrayField with JSONField inside.
models.py
locations = ArrayField(JSONField(null = True,blank = True), blank=True, null = True)
Insert
location_arr = [{"locations" : "loc1","amount":Decimal(100.00)},{"locations" : "loc2","amount":Decimal(200.25)}] instance.locations = location_arr instance.save()
When I do this, I got
column "locations" is of type jsonb[] but expression is of type text[]
LINE 1: ...d" = 2517, "locations" = ARRAY['{"loc...
Hint: You will need to rewrite or cast the expression.
So I tried to dump it using:
import json location_arr = [{"locations" : "loc1","amount":Decimal(100.00)},{"locations" : "loc2","amount":Decimal(200.25)}] instance.locations = json.dumps(location_arr) instance.save()
then I got this
LINE 1: ...d" = 2517, "locations" = '[{"loc":...
DETAIL: "[" must introduce explicitly-specified array dimensions.
I am using:
- Django 1.9
- Python 2.7
- Postgres 9.4.10
- psycopg2 2.6.2
-
drhagen almost 6 yearsThis is known bug.
-
Artem Bernatskyi about 5 yearsThis was fixed in
Django==2.2a1
-
aldesabido over 7 yearsThanks. so when to use ArrayField?
-
e4c5 over 7 yearsOn the rare occaision when you don't need to search in that column and you don't need to use that column for a join.
-
aldesabido over 7 yearsHow about we use custom lookups like __contains for searching? docs.djangoproject.com/en/1.10/ref/contrib/postgres/fields/…
-
e4c5 over 7 yearsand that doesn't search the arrayfield?
-
aldesabido over 7 yearsoh yeah. my bad. Thanks!
-
e4c5 over 7 yearsno worries glad to have been of service. All the best with your project
-
Khatri about 4 yearsnow how do you search for a particular location inside JSONBField, let's say with locations="loc1"?
-
Ben Gotow almost 3 yearsThis answer is quite out-of-date - now that Postgres supports GIN indexes on JSONB columns (with the crazy fast
?
operator for array/key membership), storing tags in a column instead of a join table can help you avoid massive join fan-out during queries and is a totally reasonable solution. -
e4c5 almost 3 yearsOn the contrary Ben, you are following a well known SQL anti pattern by putting comma seperated data in a column.