RedShift: проблема с regexp_substr

Question

У меня в RedShift есть такой JSON: {"skippable": true, "unit": true}

Мне надо получить ключи у этого JSON. Какого-то нативного решения не нашел, поэтому решил перевести этот JSON в строку и через regexp_substr вытащить эти ключи. В итоге должен получить какой-то такой список: "skippable", "unit" etc.

Вот такой у меня запрос:

SELECT regexp_substr(REPLACE(REPLACE(attributes, '{', ''), '}', '')::VARCHAR, '\S+:') AS regexp, JSON_PARSE(attributes) AS attributes_super
    FROM source.table
    WHERE prompttype != 'input'.

По идее должно работать, но нет - не работает. Просто пустоту выводит в колонке "regexp".

Answer 1

Сработала такая регулярка:

SELECT regexp_substr(REPLACE(REPLACE(attributes, '{', ''), '}', ''), '\"[0-9a-zA-Z]+\"') AS regexp, REPLACE(REPLACE(attributes, '{', ''), '}', '') AS replace, JSON_PARSE(attributes) AS attributes_super
    FROM source.table
    WHERE prompttype != 'input'

В целом решение такое:

        SELECT
            n::int
        INTO TEMP numbers
        FROM
            (SELECT 
                row_number() over (order by true) as n
            FROM table limit 30)
            CROSS JOIN
                (SELECT 
                    max(regexp_count(attributes, '[,]')) as max_num 
                FROM table  limit 30)
                WHERE
                  n <= max_num + 1; 

WITH all_values AS (
            SELECT  c.id, c.attributes, c.attributes_super.prompt, c.attributes_super.description,
                    c.attributes_super.topic, c.attributes_super.context,
                    c.attributes_super.use_case, c.attributes_super.subtitle, c.attributes_super.txValues, c.attributes_super.flashmode,
                    c.attributes_super.skippable, c.attributes_super.videoMaxDuration, c.attributes_super.defaultCameraFacing, c.attributes_super.locationRequired
            FROM    (
                    SELECT *, JSON_PARSE(attributes) AS attributes_super
                    FROM table
                    WHERE prompttype != 'input' 

                    ) AS c
                                  
                    ORDER BY created DESC 
                    limit 1
        ), list_of_attr AS (
            SELECT *, regexp_substr(split_part(attributes,',',n), '\"[0-9a-zA-Z]+\"') as others_attrs 
            FROM
                    all_values

            CROSS JOIN
                    numbers
            WHERE
                    split_part(attributes,',',n) is not null
                    AND split_part(attributes,',',n) != ''

        ), combine_attrs AS (
            SELECT id, attributes, prompt, description, 
                    topic, context, use_case, subtitle, txvalues,   flashmode,
                    skippable, videomaxduration, defaultcamerafacing, locationrequired, LISTAGG(others_attrs, ',') AS others_attrs
            FROM list_of_attr
            GROUP BY id, attributes, prompt, description, topic,
                    context, use_case, subtitle, txvalues, flashmode,
                    skippable, videomaxduration, defaultcamerafacing, locationrequired)

Заморочено, но работает.

БЛОГ НА HUSL

RedShift: проблема с regexp_substr

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