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


Ответы (0 шт):