class JsonKeys(Func):
function = 'jsonb_object_keys'Now you can write
File.objects.all().annotate(metadata_keys = JsonKeys('metadata')I'll suggest the following
list_of_keys = []
for obj in File.objects.all().values_list('metadata', flat = True)
list_of_keys = list_of_keys + obj.keys()Now you will have a list of keys, then you can do
set = {}
map(set.__setitem__, list_of_keys, [])
unique_keys = set.keys()I'll suggest the following,How to get a django QuerySet of all ForeignKeys of all objects in a Queryset,Get a list of all installed applications in Django and their attributes,Django Rest framework: I want my GET response to be a JSON object of links to all files in my media directory on my local machine. How do i do this?
I'll suggest the following
list_of_keys = []
for obj in File.objects.all().values_list('metadata', flat = True)
list_of_keys = list_of_keys + obj.keys()Now you will have a list of keys, then you can do
set = {}
map(set.__setitem__, list_of_keys, [])
unique_keys = set.keys()class JsonKeys(Func):
function = 'jsonb_object_keys'Now you can write
File.objects.all().annotate(metadata_keys = JsonKeys('metadata')I would like to be able to list all of the anycodings_python unique metadata keys in use by all of the anycodings_python File objects.,You can use Func() expression (docs) of anycodings_django django to use Postgres function anycodings_django jsonb_object_keys(docs),Now you will have a list of keys, then anycodings_django you can do ,The metadata can be filled with whatever a anycodings_python user likes.
I have a JSONField in a django model like anycodings_python so:
from django.db
import models
from django.contrib.postgres.fields
import JSONField
class File(models.Model):
metadata = JSONField(null = True, blank = True,
default = {})You can use Func() expression (docs) of anycodings_django django to use Postgres function anycodings_django jsonb_object_keys(docs)
class JsonKeys(Func):
function = 'jsonb_object_keys'Now you can write
File.objects.all().annotate(metadata_keys = JsonKeys('metadata')I'll suggest the following
list_of_keys = []
for obj in File.objects.all().values_list('metadata', flat = True)
list_of_keys = list_of_keys + obj.keys()Now you will have a list of keys, then anycodings_django you can do
set = {}
map(set.__setitem__, list_of_keys, [])
unique_keys = set.keys()Django's JSONField actually stores the data in a Postgres JSONB column, which is only available in Postgres 9.4 and later., Django's JSONField actually stores the data in a Postgres JSONB column, which is only available in Postgres 9.4 and later. ,JSONField is great when you want a more flexible schema. For example if you want to change the keys without having to do any data migrations, or if not all your objects have the same structure., JSONField is great when you want a more flexible schema. For example if you want to change the keys without having to do any data migrations, or if not all your objects have the same structure.
Available in Django 1.9+
from django.contrib.postgres.fields
import JSONField
from django.db
import models
class IceCream(models.Model):
metadata = JSONField()Pass data in native Python form, for example list, dict, str, None, bool, etc.
IceCream.objects.create(metadata = {
'date': '1/1/2016',
'ordered by': 'Jon Skeet',
'buyer': {
'favorite flavor': 'vanilla',
'known for': ['his rep on SO', 'writing a book']
},
'special requests': ['hot sauce'],
})Querying top-level data
IceCream.objects.filter(metadata__ordered_by = 'Guido Van Rossum')An integer will be interpreted as an index lookup.
IceCream.objects.filter(metadata__buyer__known_for__0 = 'creating stack overflow')Ordering directly on JSONField is not yet supported in Django. But it's possible via RawSQL using PostgreSQL functions for jsonb:
from django.db.models.expressions
import RawSQL
RatebookDataEntry.objects.all().order_by(RawSQL("data->>%s", ("json_objects_key", )))Django extension, executing bulk update operations for PostgreSQL,Django extension to update multiple table records with similar (but not equal) conditions in efficient way on PostgreSQL,django >= 1.8 Previous versions may also work, but are not tested in CI. django.postgres.contrib fields are also supported (available since django 1.8) django.postgres.contrib.JSONField is supported since django 1.9,PostgreSQL before 9.5 doesn't support INSERT ... ON CONFLICT statement. So 3-query transactional update will be used.
pdnf_clause(key_fields, field_values, key_fields_ops=())
Pure django implementation of principal disjunctive normal form. It is base on combining Q() objects.
Condition will look like:
SELECT...WHERE(a = x AND b = y AND...) OR(a = x1 AND b = y1 AND...) OR...
Here is all the code needed to recreate the error, Im using Django 1.9, Postgres 9.4 and psycopg2 2.6.1. , I have a model called File with a JSONField. I create a new file and inside the JSONField is a key value pair , In order to use the textfield__contains operator on metadata -> 'title' you'll have to cast your JSON field to text (just like you do in your raw() query) before hand. , Marking as "Patch needs improvement" per Simon's comment, "Searching a whole JSON blob seems like a bad practice to me." The technique described in comment:3 doesn't work, so it's possible some code changes might also be needed to allow the desired behavior.
I have a model called File with a JSONField. I create a new file and inside the JSONField is a key value pair
{
"title": "the cow jumped over the moon"
}
I can do it will the following raw SQL statement in Django
File.objects.raw('SELECT * FROM web_file WHERE (metadata ->> %s)::text LIKE %s;', ['title', '%moon%'])[0]
This works, but doesn't return a queryset, and doesn't play nice with the rest of my code. I was hoping I could do this query with the Django ORM. From the docs, I thought the following code would work:
File.objects.filter(metadata__title__contains = 'moon')
models.py
from django.db
import models
from django.contrib.postgres.fields
import JSONField
class File(models.Model):
owner = models.ForeignKey('auth.User', related_name = 'file_user')
created_on = models.DateTimeField(auto_now_add = True, blank = True)
name = models.CharField(max_length = 100)
metadata = JSONField(null = True, blank = True,
default = dict())
query.py
from web.models
import File
from django.contrib.auth.models
import User
user = User.objects.get(pk = 1)
File.objects.create(name = "testfile.txt", owner = user, metadata = {
'title': 'the cow jumped over the moon'
})
# raw query that works
# File.objects.raw('SELECT * FROM web_file WHERE (metadata ->> %s)::text LIKE %s;', ['title', '%moon%'])[0]
File.objects.filter(metadata__title__contains = 'moon')
>>>
outputs traceback https: //gist.github.com/eoinmurray/04843b77e8d71fad8cfa4a990c00740a
>>>
DataError: invalid input syntax
for type json
>>>
LINE 1: ...b_file " WHERE "
web_file "."
metadata " -> 'title' @> 'moon' ORD... ^
>>>
DETAIL: Token "moon"
is invalid. >>>
CONTEXT: JSON data, line 1: moon
I have just tested it there and using your suggested syntax doesnt work for me
File.objects.create(name = "testfile.txt", owner = some_user, metadata = {
'title': 'the cow jumped over the moon'
})
print File.objects.filter(metadata__contains = {
'title': 'moon'
})
>>>
[]
I just checked and it seems to me that
print File.objects.filter(metadata__title = 'moon')
and
print File.objects.filter(metadata__contains = {
'title': 'moon'
})from django.db
import models
from django.db.models.functions
import Cast
File.objects.annotate(
title = Cast('metadata__title', models.TextField()),
).filter(title__contains = 'moon')
I've done something like:
like_arg = "%%%s%%" % query_string
qs.extra(where = ["json_values ->> %s LIKE %s"], params = [lookup_key, like_arg])