Keeping json data in the order it was inserted - django

Question

I have a JSONField in my django app. The data might look like this :

{
"05/2013" : 101,
"04/2013" : 100.9,
"03/2013" : 100.5,
"02/2013" : 100.3,
"01/2013" : 100.3
}

It's a price index. When I fetch the data from the field I want to preserve the order in which the data was inserted.

I looked at the advanced option that uses OrderedDict. The Dict (I guess) uses lexical sort so the output losses it's original order in this format. I've tried flipping the dates to the format 2012-01,2012-02 and so on but the output is still scrambled. Partial output :

"2011-08":104.2,
  "2011-09":104,
  "2011-02":102.3,
  "2011-03":102.5,
  "2011-01":102,
  "2011-06":104,
  "2011-07":103.7,
  "2011-04":103.1,
  "2011-05":103.6,
  "2013-04":100.9,
  "2013-05":101,
  "2012-10":106,
  "2012-11":105.5,

I'm not sure what kind of sort is this and i'm one step behind on finding a solution. Appreciate any thoughts.

EDIT: This is the model field declaration

from jsonfield import JSONField
...
values = JSONField(null=True, blank=True, verbose_name=_("values"),load_kwargs={'object_pairs_hook': collections.OrderedDict})
...

Answer 1

this is interesting question, especially because there is no guarantee about order of fields in the javascript object, JSON itself or python dict

taking above into consideration, if we will get our order set properly in some combination of database + serialization + deserialization + presentation it will work only for this (quite narrow) configuration

e.g. django-jsonfield serializes (by default) JSON to the text field, so on the database level it may be safe, but deserialization from few json modules (json, simplejson, ujson, yajl) is done to dictionary and then all order is lost

django 1.9 introduces native postgresql JSONField - and here you do not have guarantee even on the database level, because data is stored internally as jsonb - binary optimized JSON

in my opinion, instead of searching for serialization to OrderedDict, better to change how data is stored, if order is important - let's store data in the table:

[{date: "05/2013", price: 101},
 {date: "04/2013", price: 100.9},
 {date: "03/2013", price: 100.5},
 {date: "02/2013", price: 100.3},
 {date: "01/2013", price: 100.3}]

We have guarantee of the order, data can be processed in linear matter and we are not depending on special implementations.

Answer 2

If you need to maintain key order in a JSONFIeld you can override the db_type for the native JSONField to use json rather than jsonb:

class RawJSONField(JSONField):

    def db_type(self, connection):
        return 'json'

From the Postgres documentation:

The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

If you don't need to index your data based on the contents of the JSON blob and really do need to maintain the key order that was created when you instantianted your dictionary (perhaps you are using a client side library that makes assumption on JSON key order), then this might work for you.