Ruby Faker Library with JSONB in PostgreSQL db

My question is whether PostgreSQL actually stores json data in a jsonb column type with quotation marks?

The content in the column is stored as:

"{\"Verdie\":\"Barbecue Ribs\",\"Maurice\":\"Pappardelle alla Bolognese\",\"Vincent\":\"Tiramisù\"}"

I can't workout if this is a feature of PostgreSQL or how I'm seeding my Rails database with Faker data:

# seeds.rb

require "faker"

10.times do
  con = Connector.create(
    user_id: 1,
    name: Faker::Company.name,
    description: Faker::Company.buzzword
  )

  rand(6).times do
    con.connectors_data.create(
      version: Faker::Number.number(digits: 5),
      metadata: Faker::Json.shallow_json(width: 3, options: { key: "Name.first_name", value: "Food.dish" }),
      comment: Faker::Lorem.sentence
    )
  end
end

Its due to what you're using to seed the data. This is a classic "double encoding" issue.

When dealing with JSON columns you need to remember that that the database adapter (the pg gem) will automatically serialize Ruby hashes, arrays, numerals, strings and booleans into json*. If you feed something that you have already converted into JSON the database adapter it will store it as a string - thus the escaped quotes. "JSON strings" in Ruby are not a specific type and the adapter has no idea that you for example intended to store the JSON object {"foo": "bar"} and not the string "{\"foo\": \"bar\"}".

This is what also what commonly happens when serialize or store are used on JSON columns out of ignorance.

The result is that you get garbage data that can't be queried without using the Postgres to_json function on every row which is extremely inefficient or you need to update the entire table with:

UPDATE table_name SET column_name = to_json(column_name);

Which can also be very costly.

While you could do:

  rand(6).times do
    con.connectors_data.create(
      version: Faker::Number.number(digits: 5),
      metadata: JSON.parse(Faker::Json.shallow_json(width: 3, options: { key: "Name.first_name", value: "Food.dish" })),
      comment: Faker::Lorem.sentence
    )
  end

Its very smelly and the underlying method that Faker::Json uses to generate the hash is not public so you might want to look around for a better alternative.

Ruby Faker Library with JSONB in PostgreSQL db

Related

Recent Posts