Рекурсивное извлечение значений JSON в postgres
У меня есть данные JSON, хранящиеся в столбце. Я хочу проанализировать данные json и извлечь все значения для определенного ключа.
Вот мои примерные данные:
{
"fragments": [
{
"fragments": [
{
"fragments": [
{
"fragments": [],
"fragmentName": "D"
},
{
"fragments": [],
"fragmentName": "E"
},
{
"fragments": [],
"fragmentName": "F"
}
],
"fragmentName": "C"
}
],
"fragmentName": "B"
}
],
"fragmentName": "A"
}
Ожидаемый результат:
D, E, F, C, B, A
Я хочу извлечь все значения fragmentName из приведенного выше JSON.
Я прошел через следующие стеки, но не нашел ничего полезного: Собирайте рекурсивные ключи JSON в Postgres Рекурсивный запрос Postgres с row_to_json
Отредактировано:
Вот один из подходов, который я пробовал на вышеуказанных стеках:
WITH RECURSIVE key_and_value_recursive(key, value) AS (
SELECT
t.key,
t.value
FROM temp_frg_mapping, json_each(temp_frg_mapping.info::json) AS t
WHERE id=2
UNION ALL
SELECT
t.key,
t.value
FROM key_and_value_recursive,
json_each(CASE
WHEN json_typeof(key_and_value_recursive.value) <> 'object' THEN '{}' :: JSON
ELSE key_and_value_recursive.value
END) AS t
)
SELECT *
FROM key_and_value_recursive;
Выход:
Получение только 0 уровня вложенности.
Ответы 1
Я бы использовал рекурсивный запрос, но с jsonb_array_elements():
with recursive cte as (
select id, info ->> 'fragmentName' as val, info -> 'fragments' as info, 1 lvl
from mytable
where id = 2
union all
select c.id, x.info ->> 'fragmentName', x.info -> 'fragments', c.lvl + 1
from cte c
cross join lateral jsonb_array_elements(c.info) as x(info)
where c.info is not null
)
select id, val, lvl
from cte
where val is not null
Запрос обходит объект в глубину; на каждом этапе пути мы распаковываем json-массив и проверяем, доступно ли имя фрагмента. Нам не нужно проверять типы возвращаемых значений: мы просто используем стандартные функции, пока не закончатся данные.
Демо на DB Fiddle
Образец данных:
{
"fragments": [
{
"fragments": [
{
"fragments": [
{
"fragments": [
],
"fragmentName": "D"
},
{
"fragments": [
],
"fragmentName": "E"
},
{
"fragments": [
],
"fragmentName": "F"
}
],
"fragmentName": "C"
}
],
"fragmentName": "B"
}
],
"fragmentName": "A"
}
Полученные результаты:
id | val | lvl -: | :-- | --: 2 | A | 1 2 | B | 2 2 | C | 3 2 | D | 4 2 | E | 4 2 | F | 4