JSONB PostgreSQL type: how to lookup

3
votes

I am not sure to understand how far we could use the JSONB type of PostgreSQL 9.4

For example, if I store the following in a JSON field:

 id |                 data                 
----+--------------------------------------
  1 | {"a": ["1", "2"]}
  2 | {"b": ["1", "5"]}
  3 | {"a": ["1", "3", "8"], "c": ["1", "2"]}
  4 | {"a": ["7", "8"]}

How can I select the rows where the JSONB field has a key "a" that has the value "1" in its list?

For this example, the result would be:

 id |                 data                 
----+--------------------------------------
  1 | {"a": ["1", "2"]}
  3 | {"a": ["1", "3", "8"], "c": ["1", "2"]}
(2 rows)

If yes, how to use it with django?

Thanks

3
djangojsonbpostgresql-9.4

3 Answers

0
votes

Your best bet is to write custom lookups and transforms using Django 1.7 or later. You'll need to write a couple of classes which generate the needed SQL.

Another option is to wait for this feature to land in Django. There is a kickstarter project for this, see https://www.kickstarter.com/projects/mjtamlyn/improved-postgresql-support-in-django

0
votes

A query that solve your problem is:

select *
from mytable
where data -> 'a' ? '1'
0
votes

You can get it using following query:

yourModel.objects.filter(data__contains={"a": [*]})

You can have look at this