PostgreSQL: ERROR: set-valued function called in context that cannot accept a set
Я пробую сделать такой запрос:
WITH all_values AS (
SELECT s.projectid AS projectid,
s.prompttype AS prompttype,
(s.attributes::jsonb)->>'prompt' AS prompt,
(s.attributes::jsonb)->>'description' AS description,
(s.attributes::jsonb)->>'topic' AS topic,
(s.attributes::jsonb)->>'context' AS context,
(s.attributes::jsonb)->>'use_case' AS use_case,
(s.attributes::jsonb)->>'subtitle' AS subtitle,
(s.attributes::jsonb)->>'txValues' AS txValues,
(s.attributes::jsonb)->>'flashmode' AS flashmode,
(s.attributes::jsonb)->>'skippable' AS skippable,
(s.attributes::jsonb)->>'videoMaxDuration' AS videoMaxDuration,
(s.attributes::jsonb)->>'defaultCameraFacing' AS defaultCameraFacing,
s.corpuscode AS corpuscode,
s.scriptid AS scriptid,
s.promptnum AS promptnum,
string_agg(jsonb_object_keys(attributes::jsonb), ',')
FROM source_table AS s
WHERE
s.prompttype != 'input' AND (s.created > now() - interval '30 minutes' OR s.modified > now() - interval '30 minutes')
GROUP BY s.projectid, s.prompttype, prompt, description, topic, context, use_case, subtitle, txValues, flashmode, skippable, videoMaxDuration, defaultCameraFacing, corpuscode, scriptid, promptnum
)
SELECT * FROM all_values;
Получаю ошибку: ERROR: set-valued function called in context that cannot accept a set
Суть такова, что мне нужно ключи, которые есть в JSON, соединить дополнительно вместе и записать их в одну строчку рядом с другими значениями.
Код тестирую здесь: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=e63bc49acdd09ca9e9d05a41e3a013d6