在SQL中如何根据JSON数组批量展开并执行INSERT-SQL-PHP中文网

postgresql用jsonb_array_elements()展开json数组插入，需确保输入为jsonb类型；mysql 8.0+用json_table()映射为虚拟表；sql server用openjson()配合with子句解析；三者均需注意类型转换、null处理及大数据量性能问题。

在sql中如何根据json数组批量展开并执行insert

PostgreSQL 中用 `jsonb_array_elements()` 展开 JSON 数组插入

PostgreSQL 9.4+ 支持原生 JSONB，批量展开并插入最直接的方式是把 JSON 数组作为子查询源，配合 jsonb_array_elements() 拆成行。注意：输入必须是 jsonb 类型，json 会报错。

常见错误现象：ERROR: function jsonb_array_elements(json) does not exist —— 这是因为传入的是 json 而非 jsonb，需显式转换。

确保 JSON 字符串先转为 jsonb：用 ::jsonb 或 to_jsonb()
每个数组元素会被转成一行 jsonb 值，后续用 -> 或 ->> 提取字段（前者返回 jsonb，后者返回 text）
若原始数据来自参数或变量（如函数入参），务必检查是否已解析为 jsonb，而非未处理的字符串

INSERT INTO users (name, age, tags)
SELECT 
  elem->>'name' AS name,
  (elem->>'age')::int AS age,
  elem->'tags' AS tags
FROM jsonb_array_elements('[{"name":"Alice","age":"30","tags":["dev"]},{"name":"Bob","age":"25","tags":["test"]}]'::jsonb) AS elem;

MySQL 8.0+ 用 `JSON_TABLE()` 实现等效展开

MySQL 不支持类似 PostgreSQL 的 set-returning 函数，但 8.0.14+ 引入了 JSON_TABLE()，可将 JSON 数组映射为虚拟表。这是目前最接近“批量展开”的标准方式。

使用场景：你有一段 JSON 字符串（如来自应用层、配置字段或临时变量），想一次性插入多行。

baidu-map-jsapi-three

使用 MapV-Three 构建专业的 3D 地图和 GIS 应用 - 基于 Z-up 坐标系的 3D 地图库，支持地图编辑、测量工具、要素绘制、数据管理等地理可视化功能。适用于创建地图编辑器、测量工具、空间数据可视化等 Web-GIS 应用。

下载

JSON_TABLE() 第一个参数必须是合法 JSON 字符串，否则整个 INSERT 失败（不会跳过错误项）
列定义里用 PATH 指定路径，FOR ORDINALITY 可选，用于获取序号
类型强制需在 SELECT 投影中完成，JSON_TABLE 内部只做路径提取，不自动转类型

INSERT INTO users (name, age, tags)
SELECT name, age, tags FROM JSON_TABLE(
  '[{"name":"Alice","age":30,"tags":["dev"]},{"name":"Bob","age":25,"tags":["test"]}]',
  "$[*]" COLUMNS (
    name TEXT PATH "$.name",
    age INT PATH "$.age",
    tags JSON PATH "$.tags"
  )
) AS jt;

SQL Server 中用 `OPENJSON()` 解析并 JOIN 插入

SQL Server 2016+ 提供 OPENJSON()，它把 JSON 字符串解析为行集，但默认只返回键值对，需搭配 WITH 子句声明结构。关键点在于：它不接受变量直接传入，必须是字符串字面量或变量，且不能嵌套在复杂表达式中。

容易踩的坑：OPENJSON() 对 JSON 格式极其敏感——末尾多逗号、单引号代替双引号、空值写成 null 但没加引号，都会导致返回空结果，而不是报错。

若 JSON 来自变量（如 @json_input），直接传入即可，无需额外包装
WITH 子句中字段名必须与 JSON key 完全匹配（区分大小写取决于数据库排序规则）
数组内对象若含嵌套 JSON（如 "meta": {"score": 95}），需用 AS JSON 标记该列，否则被截断为字符串

DECLARE @json_input NVARCHAR(MAX) = N'[{"name":"Alice","age":30,"tags":["dev"]},{"name":"Bob","age":25,"tags":["test"]}]';

INSERT INTO users (name, age, tags)
SELECT name, age, tags
FROM OPENJSON(@json_input)
WITH (
  name NVARCHAR(50) '$.name',
  age INT '$.age',
  tags NVARCHAR(MAX) '$.tags' AS JSON
);

通用注意事项：NULL、类型不一致与性能边界

所有数据库在 JSON 展开插入时，都不会自动忽略缺失字段或类型错误——它们会转成 NULL 或抛异常，取决于具体实现和上下文。这不是 bug，是设计使然。

真正容易被忽略的是批量规模与执行计划交互的问题：当 JSON 数组超过几百项，PostgreSQL 可能因 planner 误估行数而选错索引；MySQL 的 JSON_TABLE() 在大数据量下解析开销明显上升；SQL Server 的 OPENJSON() 在未建统计信息时可能退化为表扫描。