Как извлечь вложенные значения JSONB в PostgreSQL с помощью динамического запроса SQL для определенного ключа?

В моей базе данных PostgreSQL есть столбец JSONB под названием metadata. Я хочу извлечь значения определенного ключа в этом столбце.

Например, рассмотрим следующую структуру JSON:

{
  "key1": "value1",
  "key2": [
    {"key3": "value3"},
    {"key3": "value4"}
  ]
}

Мне нужно получить значения key3, даже если они вложены на разных уровнях (до 4 или 5 уровней). Существует ли общий подход для динамического создания SQL-запроса PostgreSQL для этой цели?

sql postgresql query-optimization jsonb

05.08.2024 19:26

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Функция jsonb_path_query() может это сделать: демо в db<>fiddle

create table your_table(
   id int generated by default as identity primary key
  ,metadata  jsonb);
insert into your_table(metadata) values
 ('{"key1": "value1",
    "key2": [ {"key3": "value3"}
             ,{"key3": "value4"} ]
   }')
,('{"key3": "value5",
    "key2": [ {"key4": {"key3": "value6"}} ]
   }');

SELECT jsonb_path_query(metadata,'strict $.**.key3')
FROM your_table
WHERE metadata @? '$.**.key3';

jsonb_path_query "значение3" "значение4" "значение5" "значение6"

$ — корень структуры jsonb
.** означает, что учтены все уровни структуры
.key3 возвращает значение этого ключа, найденное на любом уровне
строгий режим предотвращает дублирование value3 и value4, которые были бы возвращены из-за двойного доступа к массиву key2 в противном случае в режиме по умолчанию lax
В условии WHERE metadata @? '$.**.key3' используется индекс для поиска только тех строк, в которых есть key3