Storing Postgres Array of Jsonb in Rails 5 Escapes Strings Unexpectedly

Question

Perhaps my understanding of how this is supposed to work is wrong, but I seeing strings stored in my DB when I would expect them to be a jsonb array. Here is how I have things setup:

Migration

t.jsonb :variables, array: true

Model

attribute :variables, :variable, array: true

Custom ActiveRecord::Type

ActiveRecord::Type.register(:variable, Variable::Type)

Custom Variable Type

class Variable::Type < ActiveRecord::Type::Json
  include ActiveModel::Type::Helpers::Mutable

  # Type casts a value from user input (e.g. from a setter). This value may be a string from the form builder, or a ruby object passed to a setter. There is currently no way to differentiate between which source it came from.
  # - value: The raw input, as provided to the attribute setter.
  def cast(value)
    unless value.nil?
      value = Variable.new(value) if !value.kind_of?(Variable)
      value
    end
  end

  # Converts a value from database input to the appropriate ruby type. The return value of this method will be returned from ActiveRecord::AttributeMethods::Read#read_attribute. The default implementation just calls #cast.
  #  - value: The raw input, as provided from the database.
  def deserialize(value)
    unless value.nil?
      value = super if value.kind_of?(String)
      value = Variable.new(value) if value.kind_of?(Hash)
      value
    end
  end

So this method does work from the application's perspective. I can set the value as variables = [Variable.new, Variable.new] and it correctly stores in the DB, and retrieves back as an array of [Variable, Variable].

What concerns me, and the root of this question, is that in the database, the variable is stored using double escaped strings rather than json objects:

{
  "{\"token\": \"a\", \"value\": 1, \"default_value\": 1}",
  "{\"token\": \"b\", \"value\": 2, \"default_value\": 2}"
}

I would expect them to be stored something more resembling a json object like this:

{
  {"token": "a", "value": 1, "default_value": 1},
  {"token": "b", "value": 2, "default_value": 2}
}

The reason for this is that, from my understanding, future querying on this column directly from the DB will be faster/easier if in a json format, rather than a string format. Querying through rails would remain unaffected.

How can I get my Postgres DB to store the array of jsonb properly through rails?

I don't understand why you think you need all that junk. The db driver handles deserializing/serialing ruby objects to JSON just fine. attribute :variables, :variable, array: true is most likely the culprit here is and is completely superfluous. — max
– max, Commented May 15, 2019 at 20:22
Also using array: true in the migration does nothing. Postgres will still take any scalar type (string, integer, array, object). Its used when declaring native array columns as t.string :variables, array: true. You also seem to be confusing arrays with objects (json) / hashes (ruby). — max
– max, Commented May 15, 2019 at 20:27
The attributes api allows you to fine tune what is serialized and deserialized from the DB. In this instance, it allows me to save an array of complex ruby objects, have it serialized, and then populated as an array of complex ruby objects everywhere when read from the DB. Not superfluous, as the json in my example is just the json representation of the Variable object. I don't have a problem there, but rather with how the data is being stored. In what should be an array of jsonp objects, I am seeing an array of json strings. — coneybeare
– coneybeare, Commented May 16, 2019 at 0:09
"array: true" actually does do something in the migration. Before being so condescending, you may want to read up on it: guides.rubyonrails.org/active_record_postgresql.html and also the attributes api for good measure: api.rubyonrails.org/classes/ActiveRecord/Attributes/… — coneybeare
– coneybeare, Commented May 16, 2019 at 0:12

coneybeare · Accepted Answer · 2019-05-17 08:51:45Z

2

So it turns out that the Rails 5 attribute api is not perfect yet (and not well documented), and the Postgres array support was causing some problems, at least with the way I wanted to use it. I used the same approach to the problem for the solution, but rather than telling rails to use an array of my custom type, I am using a custom type array. Code speaks louder than words:

Migration

t.jsonb :variables, default: []

Model

attribute :variables, :variable_array, default: []

Custom ActiveRecord::Type

ActiveRecord::Type.register(:variable_array, VariableArrayType)

Custom Variable Type

class VariableArrayType < ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Jsonb

  def deserialize(value)
    value = super # turns raw json string into array of hashes
    if value.kind_of? Array
      value.map {|h| Variable.new(h)} # turns array of hashes into array of Variables
    else
      value
    end
  end

end

And now, as expected, the db entry is no longer stored as a string, but rather as searchable/indexable jsonb. The whole reason for this song and dance is that I can set the variables attribute using plain old ruby objects...

template.variables = [Variable.new(token: "a", default_value: 1), Variable.new(token: "b", default_value: 2)]

...then have it serialized as its jsonb representation in the DB...

[
  {"token": "a", "default_value": 1},
  {"token": "b", "default_value": 2}
]

...but more importantly, automatically deserialized and rehydrated back into the plain old ruby object, ready for me to interact with it.

Template.find(123).variables = [#<Variable:0x87654321 token: "a", default_value: 1>, #<Variable:0x12345678 token: "b", default_value: 2>]

Using the old serialize api causes a write with every save (intentionally by Rails architectural design), regardless of whether or not the serialized attribute had changed. Doing this all manually by overriding setters/getters is an unnecessary complication due to the numerous ways attributes can be assigned, and is partly the reason for the newer attributes api.

answered May 17, 2019 at 8:51

coneybeare

33.1k21 gold badges135 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fydelio Over a year ago

Seems like a great answer. But where have you defined Variable? What is it?

coneybeare Over a year ago

@fydelio, it was just a class I have setup with attributes. Could be anything.

fkotsian · Accepted Answer · 2021-02-07 23:23:59Z

1

If it helps anyone else, Rails wants you to provide the possible keys to permit in the controller as well if you're using strong params:

def controller_params
  params.require(:parent_key)
    .permit(
      jsonb_field: [:allowed_key1, :allowed_key2, :allowed_key3]
    )
end

answered Feb 7, 2021 at 23:23

fkotsian

1941 silver badge6 bronze badges

Comments

kparekh01 · Accepted Answer · 2019-05-15 20:54:59Z

0

One solution could be to just parse the variable via JSON.parse, push it inside an empty array, then assign it to the attribute.

 variables = []
 variable = "{\"token\": \"a\", \"value\": 1, \"default_value\": 1}"
 variable.class #String

 parsed_variable = JSON.parse(variable) #{"token"=>"a", "value"=>1, "default_value"=>1}
 parsed_variable.class #Hash

 variables.push parsed_variable

answered May 15, 2019 at 20:54

kparekh01

2211 silver badge7 bronze badges

4 Comments

coneybeare Over a year ago

So the problem is not reading or writing the data. The Variable::Type class does this with no problems, and the app works well from that perspective. The problem is that the underlying store is storing an array of json strings rather than an array of jsonb that will boost performance.

kparekh01 Over a year ago

I understand reading and writing the data is not an issue as you have a Variable::Type class that handles this. May I ask you what is the value of Variable.new in the array variables = [Variable.new, Variable.new] ?

kparekh01 Over a year ago

What version of rails are you currently running? If it's 5 try running this migration on your column and see if it makes a difference. change_column :table_name, :variables, :jsonb, default: '{}'

kparekh01 Over a year ago

I suspect you may be having a similar issue if you are using Postgres .....(github.com/rails/rails/issues/25594)

Collectives™ on Stack Overflow

Storing Postgres Array of Jsonb in Rails 5 Escapes Strings Unexpectedly

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related