Rails jsonb - Prevent JSON keys from reordering when jsonb is saved to Postgresql database

Question

I have a column amount_splits that I need to save my JSON to in the key order I've specified.

How do I prevent Rails / Postgres jsonb from auto sorting my JSON keys when I save it to the database? (for creating or updating)

It looks like it's trying to sort alphabetically, but does a poor job at it.

Here's what I'm saving:

{
    "str_fee": 3.17,       # key 1
    "eva_fee": 14.37,      # key 2
    "fran_royalty": 14.37, # key 3
    "fran_amount": 67.09   # key 4
}

This is how it actually saves:

{
    "eva_fee": 14.37,     # key 2
    "str_fee": 3.17,      # key 1
    "fran_amount": 67.09, # key 4
    "fran_royalty": 14.37 # key 3
}

Purpose:

Before you answer "sorting doesn't matter when the JSON is consumed on the receiving end", stop and think first please... and please read on

I need the keys to be sorted in the way I need them sorted because the client interface that consumes this JSON is displaying the JSON to developers that need the keys to be in the order that the documentation tells them its in. And the reason it needs to be in that order is to display the process of what calculations happened in which order first:

The correct order tells the developer:

The str_fee was applied first, then the eva_fee, then the fran_royalty... making fran_amount the ending amount.

But based on how jsonb sorts this, it incorrectly tells our developers that:

The eva_fee was applied first, then the str_fee, then the fran_amount... making fran_royalty the ending amount.

Vanegas

Very old question have you got any solution? – Bioplasm

Yes but the answer is only a comment (from @mu is too short) so it can't be "accepted". I didn't want to accept my own answer, and the other answers (though they have likes and good info) doesn't really solve my question the same way that @mu is too short does. Here's the link to his answer below. Basically, you should use an array ([{str_fee: 6}, {eva_fee: 11}, ...]) in the jsonb column to save the order. – Vanegas

I updated my answer to include mu's answer, with a note that it was from mu not from me. I wish I could convert his comment into an answer 😔 – Vanegas

Thank you, well I am also stuck have already created billion data and now can't do much on sorting inside postgres but I have no clue of field as it is custom field and people can add as many as they want. Will work on it by adding dynamic date/number adn later using rails will sort it. – Bioplasm

@Vanegas for the record, it's perfectly ok to answer and accept your own answer on your own question if you managed to find the answer, and nobody else posted it as an answer :) – Vincenzovincible

Answer 1

[Updated on 2021/02/12] see the comment below from @mu is too short for my "accepted" answer (I'm not wanting to accept my own answer, since it's a Rails hack).

Basically to save the order in the jsonb column, I needed to use an array (i.e. [{str_fee: 6}, {eva_fee: 11}, ...]).

---

[old hacky answer]

I can't find anything about how to modify jsonb save/update behavior, but you can control how you return your as_json from your Rails Model.

So instead of returning your JSON by calling the self.amount_splits column directly (where it would return in the wrong key order)... manually break out each key.

NOTE: this will only work if you know your key names ahead of time... if key names are dynamically created before you know them, you'll need to try something else... likely saving your JSON as a string instead of as a Hash.

class Transaction < ApplicationRecord
  store_accessor :amount_splits, :str_fee, :eva_fee, :fran_royalty, :fran_amount

  [...]

  def as_json(options={})
    # simple JSON response:
    json = {
      [...]
      "amount_splits"   => {
        "str_fee"       => self.str_fee,
        "eva_fee"       => self.eva_fee,
        "fran_royalty"  => self.fran_royalty,
        "fran_amount"   => self.fran_amount
      },
      [...]
    }
    return json
  end

  [...]

end

NOTE: I've significantly abbreviated my custom as_json method, only leaving the relevant part of the JSON that it will return

Vanegas

You're not wrong but you're not entirely right either. JSON objects are "an unordered set of name/value pairs" so there is no order for the database to preserve. Yes, Ruby hashes are ordered and (modern) JavaScript objects are ordered but JSON is neither of those. The database is free to return the keys in whatever order is convenient for it, so is JSON.parse in a browser or the JSON parser in a mobile app. You're using the wrong data structure. – Brownson

@muistooshort if an object isn't the right data structure, then what is? – Vanegas

An array would be a better fit. Arrays are inherently ordered everywhere: Ruby, JSON, JavaScript, Go, Python, ... Something more like [{str_fee: 6}, {eva_fee: 11}, ...] or [{type: 'str_fee', amount: 6}, {type: 'eva_fee', amount: 11}, ...] would work the same everywhere and it would be much easier to manipulate the order if the need arises. – Brownson

@muistooshort that makes sense... it's messy and hacky, but would totally work reliably. If I ever need to see the keys ordered correctly when looking at the Database (outside of Rails / rails console), or if I start having dynamic keys where I don't know the names before-hand, I'll go that route. Thanks for the thought! – Vanegas

Answer 2

Actually they're not sorted alphabetically but rather by key length then alphabetically, that explains the order you get. The jsonb type has been create as a better version of the json type to write and access data and it's probably for indexation and search purpose that they change the keys order. If you want your keys order not to change, you can use the json type that does not change the order of the keys when storing the data in the database.

Hope it helps.

Grouty

That's interesting and something that I will need to test out, thank you. Do you have any references/sources that documents these details? – Vanegas

postgresql.org/docs/10/datatype-json.html : "By contrast [to json], jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys." – Kibitka

Answer 3

The Postgres docs suggest using json type to preserve the order of object keys:

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

Carpio

Answer 4

You can use postgresql's json type and preserve order. If you want to take advantage of jsonb's many performance benefits, you lose native order preservation.

Here is one way to preserve order, by injecting a numeric index in each key:

class OrderedHashSerializer < ActiveRecord::Coders::JSON
  class << self
    def dump(obj)
      ActiveSupport::JSON.encode(
        dump_transform(obj)
      )
    end

    def load(json)
      json = ActiveSupport::JSON.decode(json) if json.is_a?(String)

      load_transform(json)
    end

    private

    # to indicate identifiers order as the postgresql jsonb type does not preserve order:
    def dump_transform(obj)
      obj.transform_keys.with_index do |key, index|
        "#{index + 1}_#{key}"
      end
    end

    def load_transform(hash)
      hash
        &.sort { |item, next_item| item.first.to_i <=> next_item.first.to_i }
        &.map { |key, value| format_item(key, value) }
        &.to_h
    end

    def format_item(key, value)
      [
        key.gsub(/^\d+_/, '').to_sym,
        value.in?([nil, true]) ? value : value.try(:to_sym) || value
      ]
    end
  end
end

NOTE that this will undermine using embedded json data in sql queries, as all the key names will be tainted. But if you need preserve order more than you need json queries, this is one solution. (Although json type starts to look pretty good at that point, admittedly)

Tests look like:

describe OrderedHashSerializer do
  describe '#load' do
    subject(:invoke) { described_class.load(data) }

    let(:data) do
      {
        '1_error' => 'checksum_failure',
        '2_parent' => nil,
        '22_last_item' => 'omega',
        '3_code' => 'service_server_failure',
        '4_demographics': { age: %w[29], 'flavor' => %w[cherry vanilla rhubarb] }
      }.to_json
    end

    it 'formats data properly when loading it from database' do
      is_expected.to eq(
        error: :checksum_failure,
        parent: nil,
        last_item: :omega,
        code: :service_server_failure,
        demographics: { 'age' => ["29"], 'flavor' => %w[cherry vanilla rhubarb] },
      )
    end

    it 'preserves intended key order' do
      expect(invoke.keys.last).to eq :last_item
    end
  end

  describe '#dump' do
    subject(:invoke) { described_class.dump(data) }

    let(:data) do
      {
        'error' => 'checksum_failure',
        'parent' => nil,
        'code' => 'service_server_failure',
        demographics: { age: %w[65], 'flavor' => %w[cherry vanilla rhubarb] },
        'last_item' => 'omega'
      }
    end

    it 'prefixes keys with the numbers, in order' do
      is_expected.to eq(
        {
          "1_error" => :checksum_failure,
          "2_parent" => nil,
          "3_code" => :service_server_failure,
          "4_demographics" => { age: %w[65], flavor: %w[cherry vanilla rhubarb] },
          "5_last_item" => :omega
        }.to_json
      )
    end
  end
end

Kibitka