PostgreSQL: cannot extract elements from an object (JSON)

Question

Всем привет.

Мне нужно распарсить JSON в PostregSQL.

Вот таблица с данными:

CREATE TABLE source_table (
    id              INT,
    prompttype      VARCHAR(20),
    corpuscode      VARCHAR(2000),
    text            VARCHAR(2000),
    attributes      VARCHAR(2000),
    inputs          VARCHAR(2000),
    comment         VARCHAR(2000),
    created         TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    projectid       BIGINT,
    promptnum       INT,
    scriptid        INT,
    modified        TIMESTAMP WITHOUT TIME ZONE


);

INSERT INTO source_table VALUES (209687,'recording','24-Z-02-669-0001-012','<html><p>Please read the following prompt: </p><p><span style="color: #ff0000;"><strong><span style="font-size: 14pt;">के म जुलाई पन्ध्रमा बस्ने होटल जाँच गर्नुभएको छ ?</span></strong></span></p></html>','{"prompt": "के म जुलाई पन्ध्रमा बस्ने होटल जाँच गर्नुभएको छ ?", "skippable": false, "sampleRate": 16000, "audioMaxDuration": 60, "audioMinDuration": 4, "locationRequired": true, "maxLeadingSilence": 5, "minLeadingSilence": 0.6, "maxTrailingSilence": 5, "minTrailingSilence": 0.6}',null,'','2021-09-01 05:42:15.807260',224,73,10217,'2021-09-03 13:15:15.807260');

SELECT * FROM source_table WHERE prompttype ='input' ORDER BY created DESC LIMIT 10;

CREATE TABLE target_table (
    location_required       BOOLEAN,
    access_medialibrary     BOOLEAN,
    skippable               BOOLEAN,
    flashmode               BOOLEAN,
    other_attributes        VARCHAR(2000),
    custom_framerate        VARCHAR(2000),
    video_quality           VARCHAR(2000),
    default_camerafacing    VARCHAR(2000),
    prompt_values           VARCHAR(2000),
    subtitle                VARCHAR(2000),
    use_case                VARCHAR(2000),
    topic                   VARCHAR(2000),
    prompt_desc             VARCHAR(2000),
    prompt_name             VARCHAR(2000),
    prompt_source           VARCHAR(2000),
    prompt_type             VARCHAR(2000),
    max_recordings          BIGINT,
    min_recordings          BIGINT,
    script_id               BIGINT,
    with_transcription      BIGINT,
    video_maxduration       BIGINT,
    prompt_attribute_id     BIGSERIAL PRIMARY KEY,
    collect_project_id      BIGINT,
    edw_date_updated        TIMESTAMP WITHOUT TIME ZONE,
    edw_date_created        TIMESTAMP WITHOUT TIME ZONE,
    date_updated            TIMESTAMP WITHOUT TIME ZONE,
    date_created            TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT unique_dim_collect_prompt_attr_configs UNIQUE (collect_project_id, prompt_values)

);

Вот такой парсер:

WITH all_values AS (
    SELECT s.projectid AS projectid,
           s.prompttype AS prompttype,
           el.attributes->>'prompt' AS prompt,
           el.attributes->>'description' AS description,
           el.attributes->>'topic' AS topic,
           el.attributes->>'context' AS context,
           el.attributes->>'use_case' AS use_case,
           el.attributes->>'subtitle' AS subtitle,
           el.attributes->>'txValues' AS txValues,
           el.attributes->>'flashmode' AS flashmode,
           el.attributes->>'Isskippable' AS Isskippable,
           el.attributes->>'videoMaxDuration' AS videoMaxDuration,
           el.attributes->>'defaultCameraFacing' AS defaultCameraFacing,
           s.corpuscode AS corpuscode,
           s.scriptid AS scriptid,
           s.promptnum AS promptnum
    FROM source_table AS s
       CROSS JOIN LATERAL jsonb_array_elements(s.attributes::jsonb) AS el(attributes)
    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, Isskippable, videoMaxDuration, defaultCameraFacing, corpuscode, scriptid, promptnum
   )
SELECT * FROM all_values;

Выводится ошибка:

ERROR: cannot extract elements from an object

Не могу понять что не так.

Тестирую здесь: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ff08ce40b358b8041e23cd0ee73866ff

Answer 1

Если вы думаете, что в JSON нет типов данных - то вы ошибаетесь. У вас в attributes на верхнем уровне объект. Вы пытаетесь к нему применить jsonb_array_elements - функцию обработки массива. О том и сообщает ошибка, что jsonb_array_elements не может работать с объектом.

Смотря на запрос - зачем вам вообще jsonb_array_elements? Поменяйте тип поля attributes на jsonb и обращайтесь напрямую

s.attributes->>'prompt' AS prompt,

Можно, конечно, и на лету скастовать

(s.attributes::jsonb)->>'prompt' AS prompt,

только это будет неэффективно.

БЛОГ НА HUSL

PostgreSQL: cannot extract elements from an object (JSON)

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