PostgreSQL如何将JSON对象插入含varchar[][]字段的表的单行中

• 你提供的原始JSON存在语法错误，外层应为数组结构、class字段也应为数组结构，修正后的合法JSON如下：

[
  {
    "name": "Mr. A",
    "class": [
      {
        "class_id": 1,
        "exam": 95
      },
      {
        "class_id": 2,
        "exam": 78
      }
    ]
  },
  {
    "name": "Mr. B",
    "class": [
      {
        "class_id": 1,
        "exam": 87
      },
      {
        "class_id": 2,
        "exam": 87
      }
    ]
  }
]

• 你表中insert_date的字段类型应为PostgreSQL标准类型timestamp，不存在复数形式的timestamps类型。

两种常用实现方式均可实现把所有JSON数据存入同一行的data字段：

方法1：手动构造二维数组直接插入 #

适合数据量小、格式固定的场景，SQL示例如下：

INSERT INTO 你的表名(id, data, insert_date)
VALUES (
  1, -- 按需求自定义id值
  ARRAY[
    ['Mr. A', '1:95', '2:78'],
    ['Mr. B', '1:87', '2:87']
  ]::varchar[][],
  NOW() -- 取当前时间作为插入时间，也可自定义指定时间
);

方法2：通过PostgreSQL内置JSON函数自动转换 #

适合JSON数据量大、不想手动拼接的场景，可直接基于输入JSON自动转成二维数组，SQL示例如下：

INSERT INTO 你的表名(id, data, insert_date)
SELECT
  1 AS id,
  array_agg(
    ARRAY[
      elem->>'name',
      concat((elem->'class'->0)->>'class_id', ':', (elem->'class'->0)->>'exam'),
      concat((elem->'class'->1)->>'class_id', ':', (elem->'class'->1)->>'exam')
    ]::varchar[]
  )::varchar[][] AS data,
  NOW() AS insert_date
FROM json_array_elements(
-- 此处替换为你的实际JSON字符串即可
'[
  {
    "name": "Mr. A",
    "class": [
      {
        "class_id": 1,
        "exam": 95
      },
      {
        "class_id": 2,
        "exam": 78
      }
    ]
  },
  {
    "name": "Mr. B",
    "class": [
      {
        "class_id": 1,
        "exam": 87
      },
      {
        "class_id": 2,
        "exam": 87
      }
    ]
  }
]'::json
) elem;

如果你需要调整data字段的存储格式，只需要修改上述SQL中ARRAY[]内部的取值、拼接逻辑即可。

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