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?

Answer 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')