73809

SELECT on JSONField with Django

Question:

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.<br /> 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;<br />queryset.filter(jsonfield__key_name = 'value')

What I want to know how to do;<br />queryset.values('jsonfield__key_name')

Thanks in advance!

Answer1:

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')

Answer2:

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 <a href="https://code.djangoproject.com/ticket/24747" rel="nofollow">ticket</a> and <a href="https://github.com/django/django/pull/8528" rel="nofollow">pull request</a>.

Recommend

  • Scala right associative methods
  • grails date format in English language
  • Adding additional UITabbarItem to UITabbarController
  • Extending a return object with keys matching a dynamic child
  • three.js transparent png texture strange border webgl
  • Replace Windows command FTP -s:E:\\FtpScript.txt with SFTP? [duplicate]
  • How to override C# DateTime serialization with class auto-generated from wsdl?
  • Activate UISearchBar when the controller is loaded
  • org.springframework.security.oauth2.common.exceptions.InvalidGrantException: Bad credentials
  • Encoding/decoding PDP-11 assembly language to binary and hex?
  • Cannot retrieve CustomAttributes in Interceptor using DynamicProxy
  • Create my own classList object when the browser does not implement it itself
  • Extract Data from a Web Page - using VBA
  • How do I force ASP.NET Ajax to use a script from the FS and not WebResource.axd, or to reduce its HT
  • Connect from a client to a sails based server
  • NoSuchMethodError javax.ws.rs.core.Application.getProperties()Ljava/util/Map
  • how can i close the resultSet, prepareStatement, conn in several methods below to avoid rs close and
  • Spongycastle is missing many algorithms when loaded on android
  • Signed Java web start application with Glassfish 4.1 and Java7
  • How to make Rss News Reader application in android …? [closed]
  • Ruby on Rails: Get mediaplayer information (iTunes, TRAKTOR, Cog; current song + playlist)
  • Ajax call on Multiple selection in Select box
  • trigger ontouch event programmatically
  • Creating 2d platforms using JavaScript
  • How to merge objects within array based on attribute
  • Typeahead.js does give me suggestions but doesn't select them
  • Problems to understand DXGI DirectX 11 Desktop Duplication to get a Buffer or Array
  • how to get the location(lat/lng) on google maps v3 from the location(x,y)
  • Cloud Code: Creating a Parse.File from URL
  • VSTS work items list through REST API
  • Send array to next viewcontroller iOs xcode [duplicate]
  • Apple Mach-O Linker error (“duplicate symbol”)
  • ssh remote server login script
  • multiple button click in asp.net MVC 3
  • Sql - ON DUPLICATE KEY UPDATE
  • Access to a Matlab gui from the web
  • convert json to excel in java
  • Create/delete users from text file using Bash script