SQL-оператор || не может работать со несколькими операторами ->>

Question

Работал над формированием из выборочных значений json строки c адресом посредством sql-запроса (postgresql).

Пример json:

[
    {"addr_type": 1, "city": "Рязань", "flat": "", "house": "д 123", "region": "", "street": "ул Ленина", "zip_code": "998877", "country": "РФ"},
    {"addr_type": 2, "city": "Москва", "flat": "кв 123", "house": "д 123", "region": "", "street": "ул Пушкина", "zip_code": "112233", "country": "РФ"}
]

Пример строки:

112233, Москва, ул Пушкина

Столкнулся с тем, что формирование строки через select с операторами || не работает напрямую с несколькими объектами из json, которые получаются через операторы ->>.

Неработающий запрос:

select
    (select ja->>'zip_code' || ', ' || ja->>'city' || ', ' || ja->>'street'
    from json_array_elements(v.addr) as ja
    where ja->>'addr_type' = '2'
    )::varchar as "ADDR"
from public.addr_info as v;

json_array_elements распаковывает нужный json, и далее я ожидаю с помощью ->> получить значения в типе text по нужным ключам, как это заявлено в документации.

Работает это только с одним ->>. При добавлении в select новых операторов возникает ошибка:

SQL Error [42883]: ERROR: operator does not exist: text ->> unknown. No operator matches the given name and argument type(s). You might need to add explicit type casts.

Приведение получаемых элементов через ::text не помогает:

select ja->>'zip_code'::text || ', ' || ja->>'city'::text || ', ' || ja->>'street'::text

SQL Error [42883]: ERROR: operator does not exist: text ->> text. No operator matches the given name and argument type(s). You might need to add explicit type casts.

Если сделать дополнительный select, в котором предварительно получить из json нужные значения, то потом можно успешно создать строку:

select
    (select ja.zip_code || ', ' || ja.city || ', ' || ja.street
    from (select
            vv->>'zip_code' as "zip_code",
            vv->>'city' as "city",
            vv->>'street' as "street"
            from json_array_elements(v.addr) as vv
            where vv->>'addr_type' = '2') as ja
    )::varchar as "ADDR"
from public.addr_info as v;

Либо помогает использование функции concat_ws:

select
    (select concat_ws(', ', ja->>'zip_code', ja->>'city', ja->>'street')
    from json_array_elements(v.addr) as ja
    where ja->>'addr_type' = '2'
    )::varchar as "ADDR"
from public.addr_info as v;

Есть ли какое-то объяснение, почему даже при принудительном приведении к text значений из json строка не может быть конкатенирована через ||, но при этом нормально собирается иными методами?

Answer 1

Источник ошибки в порядке вычисления. Приоритет операторов одинаковый, вычисляются по порядку.

ja->>'zip_code' - к значению ja применили оператор ->> text, допустимая штука, получили text
к полученному text применили || ', ' - допустимая штука, на выходе text
к результату применили || ja - допустимая операция, есть подходящий оператор text || anynonarray, привели ja к текстовому виду и добавили к строке
а далее в списке ->>'city' - опаньки, а вот оператора похожего на text ->> что-нибудь-подходящее-к-unknown уже не нашли.

вот до этого состояния вычисление доходит, потом натыкается на оператор ->>

select ja->>'zip_code' || ', ' || ja 
from (select '{"zip_code":1}'::json as ja) s;
         ?column?      
    -------------------
     1, {"zip_code":1}

Помимо выделения приоритета вычисления выражения скобками, может быть более читаемо будет сделать format

select format('%s, %s, %s', ja->>'zip_code', ja->>'city', ja->>'street')

PS: а вот приведение типа имеет приоритет повыше, поэтому ja->>'city'::text читается как ja->>('city'::text), а не как (ja->>'city'::text)

БЛОГ НА HUSL

SQL-оператор || не может работать со несколькими операторами ->>

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