如何创建接收JSON数组参数的存储过程，将对象属性插入对应列

我来帮你完善这个函数，让它能正确处理JSON对象数组，把每个对象的name和age属性插入到数据表的对应列中。先说说原函数存在的问题：

• 参数类型不对：PostgreSQL里表示JSON数组应该用json[]（或更推荐的jsonb[]），而不是ARRAY[]
• 没有解析JSON结构：直接把整个数组插入会和表的列类型不匹配，我们需要提取每个JSON对象里的属性

完整实现（循环遍历版） #

这个版本用FOREACH循环逐个处理数组里的JSON对象，逻辑直观易懂：

CREATE OR REPLACE FUNCTION insertJson(jsonObjects json[]) 
RETURNS void AS $$
DECLARE
    jsonObj json; -- 声明变量存储单个JSON对象
BEGIN
    -- 遍历数组中的每个JSON元素
    FOREACH jsonObj IN ARRAY jsonObjects LOOP
        -- 提取属性并插入数据表
        INSERT INTO MyTable (name, age)
        VALUES (
            jsonObj->>'name', -- 提取字符串类型的name
            (jsonObj->>'age')::integer -- 提取age并转为整数类型
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

更高效的实现（UNNEST版） #

如果处理大量数据，用UNNEST函数展开数组再批量插入会更高效，不需要显式写循环：

CREATE OR REPLACE FUNCTION insertJson(jsonObjects json[]) 
RETURNS void AS $$
BEGIN
    INSERT INTO MyTable (name, age)
    SELECT 
        obj->>'name',
        (obj->>'age')::integer
    FROM UNNEST(jsonObjects) AS obj;
END;
$$ LANGUAGE plpgsql;

使用步骤 #

1. 确保你的数据表已经创建，结构如下：

CREATE TABLE MyTable (
    name VARCHAR(100),
    age INT
);

2. 调用函数插入示例数据：

SELECT insertJson('[{"name":"ravi","age":20},{"name":"kumar","age":22}]'::json[]);

可选优化：用jsonb类型 #

如果需要更好的JSON处理性能，可以改用jsonb[]类型，只需要修改参数类型和调用时的转换：

CREATE OR REPLACE FUNCTION insertJson(jsonObjects jsonb[]) 
RETURNS void AS $$
BEGIN
    INSERT INTO MyTable (name, age)
    SELECT 
        obj->>'name',
        (obj->>'age')::integer
    FROM UNNEST(jsonObjects) AS obj;
END;
$$ LANGUAGE plpgsql;

-- 调用方式
SELECT insertJson('[{"name":"ravi","age":20},{"name":"kumar","age":22}]'::jsonb[]);

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