如何向PostgreSQL的JSON[]类型列插入JSON数组？

问题详情 #

我尝试向PostgreSQL表的app_acl列（类型为JSON[]，用于存储JSON对象数组，后续由Spring实体映射读取）插入测试数据，使用pgAdmin 4的两种方法均失败：

1. GUI单元格直接输入 #

• 输入的JSON数组内容：

[{
    "appName": "UCRM",
    "scopes": [
      "read",
      "write"
    ]
  },
  {
    "appName": "OCTA",
    "scopes": [
      "read",
      "write",
      "delete"
    ]
  }]

• 结果：点击保存后无法成功，触发报错。

2. SQL脚本批量更新 #

• 执行的SQL脚本：

UPDATE application_acl SET app_acl = array[
  '{
    "appName": "UCRM",
    "scopes": [
      "read",
      "write"
    ]
  }',
  '{
    "appName": "OCTA",
    "scopes": [
      "read",
      "write",
      "delete"
    ]
  }'
]::json[];

• 结果：数据插入成功，但存储的内容是无效的JSON数组，无法被Spring正确解析，实际存储内容：

{"{
    \"appName\": \"UCRM\",
    \"scopes\": [
      \"read\",
      \"write\"
    ]
  }","{
    \"appName\": \"OCTA\",
    \"scopes\": [
      \"read\",
      \"write\",
      \"delete\"
    ]
  }"}

解决方法 #

修正SQL脚本插入逻辑 #

之前的错误在于将字符串数组强制转换为json[]，导致数组元素是字符串而非JSON对象。正确的做法是先将每个JSON字符串转为json类型，再组成数组：

UPDATE application_acl SET app_acl = array[
  '{
    "appName": "UCRM",
    "scopes": [
      "read",
      "write"
    ]
  }'::json,
  '{
    "appName": "OCTA",
    "scopes": [
      "read",
      "write",
      "delete"
    ]
  }'::json
];

也可以用PostgreSQL内置的json_build_array函数直接构造符合要求的JSON数组，写法更简洁：

UPDATE application_acl SET app_acl = json_build_array(
  '{"appName": "UCRM", "scopes": ["read", "write"]}'::json,
  '{"appName": "OCTA", "scopes": ["read", "write", "delete"]}'::json
)::json[];

pgAdmin GUI输入正确格式 #

如果必须用GUI插入，需要遵循PostgreSQL对JSON[]类型的输入格式要求：用大括号包裹所有元素，每个JSON对象需转义内部引号，正确输入示例：

{"{\"appName\":\"UCRM\",\"scopes\":[\"read\",\"write\"]}","{\"appName\":\"OCTA\",\"scopes\":[\"read\",\"write\",\"delete\"]}"}

注意：GUI输入容易因格式问题出错，优先推荐SQL脚本方式。

验证数据有效性 #

插入后可执行以下SQL验证数据是否符合要求：

-- 查看数组长度
SELECT app_acl, json_array_length(app_acl::json) FROM application_acl;

-- 提取单个JSON对象的属性
SELECT app_acl[1]->>'appName' FROM application_acl;

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