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:

Vc

try using the ->> operator? – Darryldarryn

Start with simplified autonomous example like with t(x) as (values('{"en":"a","de":"b","ru":"c"}'::jsonb)) select x, x->'en' from t; If it will work for you (it should) then try to find the difference. – Antoineantoinetta

@Antoineantoinetta query with t(x) as (values('{"en":"a","de":"b","ru":"c"}'::jsonb)) select x, x->'en' from t; works! – Vc

How connect this query with my table and get the data? And why actually magic with -> and ->> does not work?... According to Postgres Documentation it should.. – Vc

I bet that the data you're seeing isn't really what is stored. Try the same data like this: select '{"en":"asdasdadad","de":"asdasdadad","ru":"asdasdadad"}'::jsonb->'en' If it works, your data contains some additional characters which you don't see. – Rev

@KamilG. but why PgAmin show this column as JSONB type? – Vc

I'm not saying it isn't JSONB. Though, please check by casting the column explicitly to this data type. I'm saying that there are characters probably inside "en" that you don't see. Try this: select length(i18n_name::text), length('{"en":"asdasdadad","de":"asdasdadad","ru":"asdasdadad"}') and see if it brings up the same number – Rev

@KamilG. awesome, attached new screenshot – Vc

@KamilG. so length of '{"en":"asdasdadad","de":"asdasdadad","ru":"asdasdadad"}' is 55 but in database 69 ! – Vc

Ok, there is template for debugging: dbfiddle.uk/… Just use your table/column names instead of the CTE. Note that the "bе" key in the JSONB object contains cyrillic letter "е" so its hexadecimal representation is different. – Antoineantoinetta

@Vc told you there are characters you don't see. You can't extract en key if there is no such key. There lies your answer. Investigate the data to see what's inside or simply fix it by running an UPDATE – Rev

@KamilG. can you please write your last comment as the answer of question? – Vc

I changed my JSONB column to {"q": "some text"}. as "q" - is not cyrryllic character. And -> magic begin to work) – Vc

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.

Rev

Answer 2

For my case, in psql 11, both -> and ->> work for both json and jsonb object.

-> gets the value with double quotes (""), ->> gets the value without double quotes.

Ringlet

Answer 3

You need the ->> operator:

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

Lookin

Just curious: why do you think that the ->> operator will work where -> operator does not working? – Antoineantoinetta

Unfortunately, I did not work... I attached a screenshot of PgAdmin query – Vc