Django JSONField inside ArrayField

Question

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

This is known bug.

drhagen
– drhagen

2018-05-18 12:40:48 +00:00
Commented May 18, 2018 at 12:40 — drhagen
– drhagen, Commented May 18, 2018 at 12:40
This was fixed in Django==2.2a1

Artem Bernatskyi
– Artem Bernatskyi

2019-02-04 15:25:12 +00:00
Commented Feb 4, 2019 at 15:25 — Artem Bernatskyi
– Artem Bernatskyi, Commented Feb 4, 2019 at 15:25

e4c5 · Accepted Answer · 2018-08-29 12:37:23Z

21

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.

edited Aug 29, 2018 at 12:37

answered Dec 14, 2016 at 3:34

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

aldesabido Over a year ago

Thanks. so when to use ArrayField?

e4c5 Over a year ago

On 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 a year ago

How about we use custom lookups like __contains for searching? docs.djangoproject.com/en/1.10/ref/contrib/postgres/fields/…

e4c5 Over a year ago

and that doesn't search the arrayfield?

Ben Gotow Over a year ago

This 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.

|

Sathish Kumar · Accepted Answer · 2018-03-13 12:24:22Z

7

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

edited Mar 13, 2018 at 12:24

answered Mar 13, 2018 at 12:19

Sathish Kumar

5477 silver badges18 bronze badges

1 Comment

Khatri Over a year ago

now how do you search for a particular location inside JSONBField, let's say with locations="loc1"?

Matt Shirley · Accepted Answer · 2018-08-14 23:09:40Z

4

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.

answered Aug 14, 2018 at 23:09

Matt Shirley

763 bronze badges

Comments

Artem Bernatskyi · Accepted Answer · 2019-02-04 15:23:22Z

1

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

answered Feb 4, 2019 at 15:23

Artem Bernatskyi

4,7252 gold badges28 silver badges39 bronze badges

Comments

Benny Chan · Accepted Answer · 2020-11-26 04:45:55Z

0

I guess the easiest way is to turn the field from an array of jsonfield into a jsonfield. Just add a key to the location_arr.

Related thread: ArrayField with JSONField as base_field in Django

answered Nov 26, 2020 at 4:45

Benny Chan

9321 gold badge9 silver badges28 bronze badges

Collectives™ on Stack Overflow

Django JSONField inside ArrayField

5 Answers 5

Arrays

JSONB

Recap

7 Comments

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions