1

My application is heavily reliant on APIs that unpredictably make changes to the way they return data. For this reason, I've chosen to use PSQL and JSONFields with Django.
I've seen plenty of examples/docs on how to filter by values in a JSONField, but I haven't seen any that allow me to SELECT on these values.

What I know works;
queryset.filter(jsonfield__key_name = 'value')

What I want to know how to do;
queryset.values('jsonfield__key_name')

Thanks in advance!

J__
  • 636
  • 6
  • 20

2 Answers2

5

The answer is a RawSQL expression; 

queryset.annotate(value = RawSQL("(jsonfield->%s)", ('key_name',)))
queryset.values('value')

The first argument to RawSQL is like a template string, the second argument will fill in the first's %s

UPDATE: apparently Django 2.1+ now supports my original expected behavior;

queryset.values('jsonfield__key_name')
J__
  • 636
  • 6
  • 20
3

Since Django 2.1, transforms are supported in order_by(), values() and values_list(), so you can now use this:

queryset.values('jsonfield__key_name')

Here's the relevant ticket and pull request.

Endre Both
  • 5,540
  • 1
  • 26
  • 31