PostgreSQL: how to group JSON keys and into one line?

V

vandriichuk2021-09-03 17:34:16

PostgreSQL

vandriichuk, 2021-09-03 17:34:16

Hello.

I have the following tables and values:

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 14:22: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)

);

There is such a query that decomposes data from the required keys into the required columns:

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
    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;

The code itself is here: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=87b...

How can I modify the query to get the following:

Extract from the JSON text all the attributes other than the stated above (flashmode, skippable , video_maxduration, etc.) of attributes column and store as comma seperated