Nested search without keys in jsonb in PostgreSql

Question

The question is about selection from JSON in PostgreSQL.

For example, application contains translation data in jsonb:

{
  "en":{
    "locale":"en",
    "title":"Title",
    "textShort":"Short text",
    "textFull":"Full text"
  }
  "ru":{
    "locale":"ru",
    "title":"Заголовок",
    "textShort":"Короткий текст",
    "textFull":"Подробный текст"
  }
}

This query works successfully:

select * 
from content_records 
where translations::json->'en'->>'title' like '%Title.';

But this query requires information about the locale, but the case is that we don't know anything about locales and search must be done for every locale, for example:

select * 
from content_records 
where translations::json->'any locale'->>'title' like '%Title.';

In MySQL it works as:

select * 
from content_records 
where LOWER(JSON_EXTRACT(translations, '$.*.title')) LIKE LOWER(:title);

There is the similar function in PostgreSQL: json_extract_path, but it requires keywords and you can't miss the key as the symbol * does in MySQL.

The question is - how to do the selection of a nested JSON in this situation?

blog.2ndquadrant.com/…

user330315
– user330315

2018-06-12 10:10:07 +00:00
Commented Jun 12, 2018 at 10:10 — user330315
– user330315, Commented Jun 12, 2018 at 10:10

user330315 · Accepted Answer · 2018-06-12 10:08:33Z

1

Unfortunately, in Postgres you have to "unnest" the keys first.

Something like this:

select t.*, cr.translations
from content_records cr
  cross join lateral jsonb_object_keys(translations) as t(locale)
where lower(cr.translations -> t.locale ->> 'title') like '%title';

Note that if a title matches in more than one locale, you will get one row for each matching locale. If you don't want that, you can do the following:

select cr.*
from content_records cr
where exists (select *
              from jsonb_object_keys(cr.translations) as t(locale)
              where lower(cr.translations -> t.locale ->> 'title') like '%title')

answered Jun 12, 2018 at 10:08

user330315

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

Collectives™ on Stack Overflow

Nested search without keys in jsonb in PostgreSql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related