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:

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. — Abelisto, Aug 17 '18 at 04:14
@Abelisto query `with t(x) as (values('{"en":"a","de":"b","ru":"c"}'::jsonb)) select x, x->'en' from t;` works! — bmalets, Aug 17 '18 at 07:02
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.. — bmalets, Aug 17 '18 at 07:03
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. — Kamil Gosciminski, Aug 17 '18 at 07:07
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 — Kamil Gosciminski, Aug 17 '18 at 07:22
@KamilG. so length of '{"en":"asdasdadad","de":"asdasdadad","ru":"asdasdadad"}' is 55 but in database 69 ! — bmalets, Aug 17 '18 at 07:30
Ok, there is template for debugging: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=4d7047d19bf7c911d757f5de8c79fa0c 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. — Abelisto, Aug 17 '18 at 07:36
@bmalets 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 — Kamil Gosciminski, Aug 17 '18 at 07:42
@KamilG. can you please write your last comment as the answer of question? — bmalets, Aug 17 '18 at 07:47
I changed my JSONB column to `{"q": "some text"}`. as "q" - is not cyrryllic character. And `->` magic begin to work) — bmalets, Aug 17 '18 at 07:48

score 5 · Accepted Answer · answered Aug 17 '18 at 07:57

-> 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.

score 3 · Answer 2 · answered Sep 24 '21 at 15:11

3

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.

answered Sep 24 '21 at 15:11

zhihong

1,808
2
24
34

score 2 · Answer 3 · answered Aug 16 '18 at 23:26

2

You need the ->> operator:

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

answered Aug 16 '18 at 23:26

Pablo Santa Cruz

176,835
32
241
292

Just curious: why do you think that the `->>` operator will work where `->` operator does not working? – Abelisto Aug 17 '18 at 04:29
Unfortunately, I did not work... I attached a screenshot of PgAdmin query – bmalets Aug 17 '18 at 07:01

How get values from postgres JSONB column?

3 Answers3