SQL如何连接JSON格式的数据字段_利用JSON_TABLE函数实现关系型映射-SQL-PHP中文网

只有mysql 8.0.4+和oracle 12cr1（12.1.0.2）及以上版本原生支持json_table；postgresql需用jsonb_to_recordset()替代，sql server用openjson()。

sql如何连接json格式的数据字段_利用json_table函数实现关系型映射

MySQL 8.0+ 和 Oracle 12c+ 支持 JSON_TABLE，但 PostgreSQL 和 SQL Server 不支持该函数——别在 pg 或 mssql 里找它，会白忙活。

哪些数据库能用 `JSON_TABLE`？

只有 MySQL 8.0.4+ 和 Oracle 12cR1（12.1.0.2）及以上版本原生支持 JSON_TABLE。PostgreSQL 用 jsonb_to_recordset() 或 json_array_elements() 替代；SQL Server 用 OPENJSON()。不同系统语法差异大，不能照搬。

MySQL 示例：SELECT * FROM JSON_TABLE('{"a":1,"b":2}', "$" COLUMNS (a INT PATH "$.a", b INT PATH "$.b")) AS jt
Oracle 示例：SELECT * FROM JSON_TABLE('{"id":100,"name":"Alice"}', '$' COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(50) PATH '$.name'))
MySQL 要求 JSON 字段类型为 JSON 或合法字符串；Oracle 对输入类型更宽松，但非标准 JSON 字符串可能报 ORA-40441

`JSON_TABLE` 的 PATH 表达式怎么写才不报错？

PATH 是核心，写错直接导致 NULL 或报错。MySQL 报 Invalid path expression，Oracle 报 ORA-40464，基本都是路径格式问题。

Json Schema Toolkit

使用 JSON Schema 验证 JSON 数据，从示例 JSON 生成 schema，并将其转换为 TypeScript 接口、Python 数据类或 Markdown 文档。

下载

必须以 $ 开头，如 "$.items[0].name"，不能漏掉引号或写成 $items[0].name
数组索引用方括号，且从 0 开始（MySQL 和 Oracle 一致），"$[0]" 取根数组首项，"$.list[*]" 才能展开全部元素
字段名含特殊字符（如连字符、空格）必须用双引号包裹：写成 "$.\"user-id\""，而不是 "$.user-id"（后者会被解析为 $.user 减 id）
Oracle 对路径大小写敏感，MySQL 默认不敏感，但若 JSON 中键名是 "Name"，而你写 "$.name"，Oracle 返回 NULL

为什么 `JSON_TABLE` 查询变慢？常见性能坑点

JSON_TABLE 是运行时解析，无法走索引，数据量稍大就明显拖慢——尤其当它嵌套在 JOIN 或子查询里反复执行时。

避免对全表每行都调用 JSON_TABLE：先用 WHERE json_contains(col, '"active":true')（MySQL）或 col ? 'active'（Oracle）过滤，再解析
MySQL 中，JSON_EXTRACT(col, "$.status") = "done" 比在 JSON_TABLE 后加 WHERE status = "done" 快得多，因为前者可利用生成列 + 索引
Oracle 若频繁解析同一 JSON 字段，考虑建虚拟列：ALTER TABLE t ADD (status VARCHAR2(20) GENERATED ALWAYS AS (json_value(col, '$.status')))，再对该列建索引
MySQL 8.0.22+ 支持在 JSON_TABLE 中用 NESTED PATH 处理多层嵌套，但每级 NESTED 都触发一次解析，三层嵌套可能让执行计划膨胀数倍

MySQL 和 Oracle 的 `COLUMNS` 子句关键区别

看着像，但字段定义逻辑不同：MySQL 强制要求显式声明所有列，Oracle 允许用 FOR ORDINALITY 自动生成序号列，且默认值行为不一致。

MySQL 不支持 DEFAULT 值兜底，路径不存在就是 NULL；Oracle 可写 name VARCHAR2(50) PATH '$.name' DEFAULT 'N/A' ON ERROR
Oracle 支持 ON EMPTY/ON ERROR 细粒度控制，MySQL 只有 ERROR ON ERROR 或静默为 NULL（无显式声明时）
MySQL 要求每个 COLUMNS 条目必须带数据类型，如 id BIGINT；Oracle 可省略类型，自动推导（但不推荐，易出隐式转换问题）
Oracle 允许 COLUMNS 中嵌套另一个 JSON_TABLE（用于深度结构），MySQL 不支持这种递归嵌套