如何在PostgreSQL中遍历含数组的JSON字典并获取无引号元素

问题背景 #

尝试编写PostgreSQL函数遍历包含数组的JSON字典，输入JSON示例：'{"Key1": ["value1", "value2", "value3"],"key2":["value4","value5"]}'，目标是获取不带引号的文本格式数组元素用于后续处理，但执行原函数时触发格式错误。

原函数输入 #

datajson json: '{"Key1": ["value1", "value2", "value3", "value4"],"Default":["value5","value6"]}';

原函数代码 #

$BODY$
DECLARE
    keys text[];
    key text;
    value text;
    element text;

BEGIN
    for key in select json_object_keys(datajson) loop
        raise notice '%', key;
        for value in (select json_array_elements(datajson->key) )
        loop
            raise notice '%', value;
        end loop;
    end loop;
end;

$BODY$ language plpgsql

报错信息 #

NOTICE:   key1
NOTICE:   "value1"
ERROR:  Got exception:
state  : 22P02
message: malformed array literal: ""value1""
detail : Array value must start with "{" or dimension information.

错误原因 #

json_array_elements(datajson->key)返回的是JSON类型值，直接赋值给text类型变量时，PostgreSQL会尝试将带双引号的JSON字符串解析为数组，导致格式不匹配触发错误。需要明确将JSON值转换为无引号的纯文本。

修改方案 #

方案1：使用json_array_elements_text直接提取文本元素 #

该函数专门用于从JSON数组中提取文本类型的元素，自动去除引号：

$BODY$
DECLARE
    key text;
    element text;
BEGIN
    FOR key IN SELECT json_object_keys(datajson) LOOP
        RAISE NOTICE '当前键: %', key;
        FOR element IN SELECT json_array_elements_text(datajson->key) LOOP
            RAISE NOTICE '元素值: %', element;
        END LOOP;
    END LOOP;
END;
$BODY$ language plpgsql

方案2：对JSON类型结果做强制类型转换 #

如果需要保留json_array_elements，可以将其返回的JSON值强制转为text类型，自动去除引号：

$BODY$
DECLARE
    key text;
    element text;
BEGIN
    FOR key IN SELECT json_object_keys(datajson) LOOP
        RAISE NOTICE '当前键: %', key;
        FOR element IN SELECT json_array_elements(datajson->key)::text LOOP
            RAISE NOTICE '元素值: %', element;
        END LOOP;
    END LOOP;
END;
$BODY$ language plpgsql

执行效果 #

修改后的函数执行后，会输出不带双引号的纯文本元素，例如：

NOTICE:  当前键: Key1
NOTICE:  元素值: value1
NOTICE:  元素值: value2
NOTICE:  元素值: value3
NOTICE:  元素值: value4
NOTICE:  当前键: Default
NOTICE:  元素值: value5
NOTICE:  元素值: value6

内容的提问来源于stack exchange，提问作者anonymous_101