Блог

Главная - Вопросы по программированию - Агрегировать массивы JSON в строках таблицы / Применить функцию форматирования к столбцу postgres JSON

Агрегировать массивы JSON в строках таблицы / Применить функцию форматирования к столбцу postgres JSON

#json #postgresql #jsonb

Вопрос:

У меня есть таблица postgres, которая содержит jsonb строку, подобную этой:

Моя таблица.ссылки
[{"url": "url1", "name": "name1"}, {"url": "url2", "name": "name2"}]
[]
[{"url": "url3", "name": "name3"}

Я хотел бы написать функцию postgres, которая создает новый столбец, подобный этому:

Результат
['<a href="url1">name1</a>', '<a href="url2">name2</a>']
[]
['<a href="url3">name3</a>'}

У меня уже есть кое-что:

 SELECT
       jsonb_agg(format('<a href="%s">%s</a>', (elem ->> 'url'), (elem ->> 'name')))
FROM jsonb_array_elements('[{"url": "url1", "name": "name1"}, {"url": "url2", "name": "name2"}]'::jsonb) elem;

Который возвращает:

 ["<a href="url1">name1</a>", "<a href="url2">name2</a>"]

Как применить это преобразование ко всему столбцу?

Комментарии:

1. Похоже, у вас уже есть решение. Тогда в чем именно заключается ваш вопрос?

2. Я могу сделать это только для одного элемента jsonb, но я слишком глуп, чтобы применить его к столбцу/таблице. Я уверен, что это до смешного просто, поэтому, пожалуйста, просветите меня!

3. Название должно быть улучшено, чтобы отразить фактическую проблему. Может быть, «Агрегировать массивы JSON в строках таблицы»?

Ответ №1:

Я полагаю, вы ищете вложенную инструкцию select:

 with mytable (refs) as (
  values
    ('[{"url": "url1", "name": "name1"}, {"url": "url2", "name": "name2"}]'::jsonb),
    ('[]'),
    ('[{"url": "url3", "name": "name3"}]')
)
select (select coalesce(jsonb_agg(format('<a href="%s">%s</a>',
                                         elem->>'url', elem->>'name')),
                        '[]')
        from jsonb_array_elements(refs) elem) result
from mytable;

(Кстати, references это зарезервированное ключевое слово в PostgreSQL, которое может привести к ошибочным сообщениям об ошибках.)

Комментарии:

1. Спасибо, это решает проблему! (На самом деле я не использовал «ссылки» на самом деле, только для этого примера. Изменил все на «ссылки».)

Метки: