How get values from postgres JSONB column?

Question

I have PostgreSQL 10.5 database with Rails 5 application.

My model:

# == Schema Information
#
# Table name: property_keys
#
#  id         :integer          not null, primary key
#  name       :string
#  created_at :datetime         not null
#  updated_at :datetime         not null
#  i18n_name  :jsonb
#

class PropertyKey < ApplicationRecord
  # Fields
  store :i18n_name, accessors: I18n.available_locales, coder: JSON
end

My migration:

class LocalizeProperties < ActiveRecord::Migration[5.2]
  def up
    add_column :property_keys, :i18n_name, :jsonb

    PropertyKey.all.each do |property_key|
      [:en, :de, :ru].each do |locale_key|
        property_key.i18n_name[locale_key] = property_key.name
      end

      property_key.save!
    end
  end

  def down
    remove_column :property_keys, :i18n_name
  end
end

Table name: property_keys. List of fields:

• id: bigint
• i18n_name: jsonb

Here is a request for all data:

I want to get all english names (values of "en" key inside i18n_name column).

Here is a request:

SELECT
    id,
    i18n_name,
    i18n_name->'en' AS en
FROM property_keys;

And it returns nothing.

But theoretically should return the data with filled "en" column.

Here is a screenshot:

Also I tried query with ->> and it did not work:

How should I change my request and which PostgreSQL operators should I use to make it works?

Checked length of JSONB columns:

Answer 1

-> and ->> operators work as expected retrieving json object by key or as text in latter case.

The real issue that I suspect you have is that the data you're seeing isn't really what is stored inside your table which is why i18_name->'en' won't work since there is no key en.

To be able to confirm my writing please run below query to see if lengths of what you see in the string and what is stored inside a table match. They probably won't:

select
  length(i18n_name::text) AS stored,
  length('{"en":"asdasdadad","de":"asdasdadad","ru":"asdasdadad"}') AS whatisee

What you can do with it? Either investigate the data using bytea data type conversion or simply UPDATE the row with correct (what you see) data in this column.

This will make operators bring what you actually expect them to, since there will be en key within the jsonb field.