如何在PostgreSQL中插入含数组值的JSON至JSON类型列

场景1：将PostgreSQL数组转为JSON数组插入 #

如果你的数据源是PostgreSQL数组（比如ARRAY[1,2,3]），可以通过函数将其转换为标准JSON数组格式后插入：

CREATE TABLE jsonlist (col JSON);

-- 用to_json转换PostgreSQL数组为JSON数组，再构造JSON对象
INSERT INTO jsonlist(col)
VALUES (json_build_object('usrGrp', to_json(ARRAY[1,2,3])));

也可以直接手动构造符合JSON规范的字符串插入：

INSERT INTO jsonlist(col)
VALUES ('{"usrGrp":[1,2,3]}');

场景2：保留PostgreSQL数组的字符串形式（不推荐） #

如果确实需要把PostgreSQL数组的字符串表示（如"{1,2,3}"）作为JSON的字符串值存入，你的原始写法是可行的，但此时JSON里的usrGrp是字符串类型，而非数组类型：

INSERT INTO jsonlist(col)
VALUES ('{"usrGrp":"{1,2,3}"}');

你可以用以下语句验证插入结果的类型：

SELECT col, col->>'usrGrp' AS usr_grp_value, json_typeof(col->'usrGrp') AS value_type
FROM jsonlist;

场景1的结果中value_type会显示array，场景2则显示string。

内容的提问来源于stack exchange，提问作者Kim Min-Uk