达梦数据库JSON_TABLE使用说明

在达梦数据库（DM Database）中，将 JSON 数据转换为表格形式可以使用内置的 JSON_TABLE 函数。以下是详细步骤和示例：

---

1. 核心函数：JSON_TABLE

JSON_TABLE 用于将 JSON 数据解析为关系表结构，支持从 JSON 对象或数组中提取数据。

语法：

SELECT *
FROM JSON_TABLE(
    json_doc,         -- JSON 字符串或 JSON 列
    path              -- JSON 路径（指定要解析的部分）
    COLUMNS (
        列名1 数据类型 PATH '字段路径1' [ERROR | NULL ON ERROR],
        列名2 数据类型 PATH '字段路径2' [TRIM | DEFAULT 默认值 ON EMPTY],
        ... 
    )
) AS 别名;

---

2. 示例演示

示例 1：解析 JSON 数组

假设 JSON 数据如下：

[
  {"id": 1, "name": "Alice", "age": 25},
  {"id": 2, "name": "Bob", "age": 30}
]

查询语句：

SELECT *
FROM JSON_TABLE(
    '[{"id":1,"name":"Alice","age":25},{"id":2,"name":"Bob","age":30}]',
    '$[*]'  -- 解析根数组的所有元素
    COLUMNS (
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age'
    )
) AS jt;

结果：

ID	NAME	AGE
1	Alice	25
2	Bob	30

---

示例 2：解析嵌套 JSON

假设 JSON 数据：

{
  "dept": "IT",
  "employees": [
    {"id": 101, "name": "Tom"},
    {"id": 102, "name": "Jerry"}
  ]
}

查询语句：

SELECT *
FROM JSON_TABLE(
    '{"dept":"IT","employees":[{"id":101,"name":"Tom"},{"id":102,"name":"Jerry"}]}',
    '$.employees[*]'  -- 解析 employees 数组
    COLUMNS (
        dept VARCHAR(10) PATH '$.dept',  -- 注意：此处需用根路径
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name'
    )
) AS jt;

修复路径后的正确写法：

SELECT 
    jt.*,
    JSON_VALUE(原JSON字段, '$.dept') AS dept  -- 额外提取部门
FROM JSON_TABLE(
    '{"dept":"IT","employees":[{"id":101,"name":"Tom"},{"id":102,"name":"Jerry"}]}',
    '$.employees[*]'
    COLUMNS (
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name'
    )
) AS jt;

---

3. 处理空值/错误

• NULL ON EMPTY：路径不存在时返回 NULL
• DEFAULT 值 ON EMPTY：路径不存在时返回默认值
• ERROR ON ERROR：转换错误时报错（默认行为）

示例：

SELECT *
FROM JSON_TABLE(
    '[{"id":1,"score":"90"},{"id":2}]',
    '$[*]'
    COLUMNS (
        id INT PATH '$.id',
        score INT PATH '$.score' DEFAULT -1 ON EMPTY  -- 缺失时返回 -1
    )
) AS jt;

结果：

ID	SCORE
1	90
2	-1

---

4. 从表中读取 JSON 列

若 JSON 数据存储在表的列中（如 user_data JSON）：

SELECT jt.*
FROM your_table,
JSON_TABLE(
    your_table.json_column,  -- 直接引用 JSON 列
    '$.employees[*]'
    COLUMNS (
        id INT PATH '$.id',
        name VARCHAR(100) PATH '$.name'
    )
) AS jt;

---

5. 复杂路径处理

解析多层嵌套 JSON：

{
  "project": "X",
  "tasks": [
    {
      "task_id": 1,
      "details": {"owner": "Amy", "priority": "high"}
    }
  ]
}

查询：

SELECT *
FROM JSON_TABLE(
    '{"project":"X","tasks":[{"task_id":1,"details":{"owner":"Amy","priority":"high"}}]}',
    '$.tasks[*]'
    COLUMNS (
        project VARCHAR(10) PATH '$.project',
        task_id INT PATH '$.task_id',
        owner VARCHAR(20) PATH '$.details.owner',
        priority VARCHAR(10) PATH '$.details.priority'
    )
) AS jt;

---

注意事项：

1. 路径语法：使用 $ 表示根，. 访问属性，[*] 遍历数组。
2. 版本兼容：确保达梦数据库版本支持 JSON_TABLE（DM 8.0 及以上通常支持）。
3. 性能：处理大 JSON 时注意性能，避免全表扫描。

通过以上方法，可灵活地将 JSON 数据转换为表格结构，便于 SQL 查询和分析。