主页 / 问题 / 1324995
Accepted
Viktor Andriichuk
Asked:2022-09-03 20:22:00 +0000 UTC2022-09-03 20:22:00 +0000 UTC

PostgreSQL：无法从对象中提取元素（JSON）

  • 772

大家好。

我需要在 PostregSQL 中解析 JSON。

这是数据表：

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;

抛出一个错误：

错误：无法从对象中提取元素

我不知道出了什么问题。

在这里测试：https ://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ff08ce40b358b8041e23cd0ee73866ff

postgresql

1 个回答

  1. Best Answer

    如果你认为 JSON 中没有数据类型，那你就错了。您在顶层的属性中有一个对象。您正在尝试对其应用 jsonb_array_elements - 一个数组处理函数。这就是错误所说的， jsonb_array_elements 不能与对象一起使用。

    查看请求 - 为什么需要 jsonb_array_elements 呢？将attributes字段类型改为jsonb，直接访问

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

    当然，您可以即时投射

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

    它只会效率低下。

    • 1

相关问题