Postgresql как с помощью json_to_record / json_to_recordset преобразовать JSON с array-полями в таблицу

Question

select 
    x.*
from
    (
        select row_to_json(testdata) as json
            from (
                select 1::int as id, ARRAY['s1'::text,'s2'::text,'s3'::text] as data
            ) as testdata
    ) as test,
    json_to_record(test.json) as x("id" int, "data" text[])

завершается с ошибкой

>ОШИБКА:  ошибочный литерал массива: "["s1","s2","s3"]" 
DETAIL:  За "[" должны следовать явно задаваемые размерности массива.

Если вместо as x("id" int, "data" text[]) определить as x("id" int, "data" text) то поле data возвращается в виде строки, что понятно, но не то что нужно.

Answer 1

Проблема есть, похоже, в PostgreSQL9. Начиния с PostgreSQL10 преобразование в массив text[] работает нормально. Для PostgreSQL9 я нашел решение в преобразовании к json

select 
    x.*
from
    (
        select row_to_json(testdata) as json
            from (
                select 1::int as id, ARRAY['s1'::text,'s2'::text,'s3'::text] as data
            ) as testdata
    ) as test,
    json_to_record(test.json) as x("id" int, "data" json)

А дальше работать как с json массивом. Если все-таки нужен именно массив, то придется построить запрос посложнее

select
    dum.id,
    array_agg(data)
from
(   
    select 
            x.id,
            json_array_elements_text(x.data) as data
    from
            (
                    select row_to_json(testdata) as json
                            from (
                                    select 1::int as id, ARRAY['s1'::text,'s2'::text,'s3'::text] as data
                            ) as testdata
            ) as test,
            json_to_record(test.json) as x("id" int, "data" json)
) as dum
group by dum.id

Но нужно учитывать, что преобразования туда-сюда сжирают ресурсы.

БЛОГ НА HUSL

Postgresql как с помощью json_to_record / json_to_recordset преобразовать JSON с array-полями в таблицу

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