如何在SQL中通过JOIN关联JSON数组字段-SQL-PHP中文网

json_table必须在from或join子句中使用，路径须含[*]展开数组，columns需显式声明字段名与类型，null或非法json会静默跳过整行。

如何在sql中通过join关联json数组字段

MySQL 8.0+ 怎么用 `JSON_TABLE` 展开 JSON 数组做 JOIN

MySQL 原生不支持直接对 JSON 字段里的数组做 JOIN，但 8.0 引入的 JSON_TABLE 可以把数组“摊平”成临时表，再和其他表关联。这是目前最稳妥、无需额外应用层处理的方式。

常见错误是试图写 JOIN ... ON t1.json_col = t2.id —— JSON 字段是字符串类型，根本无法和数值 ID 对齐；或者误用 JSON_CONTAINS 做等值关联，它只能判断包含关系，不能展开多行。

确保 JSON 字段存储的是标准数组格式，比如 ["a","b","c"] 或 [{"id":1,"name":"x"},{"id":2,"name":"y"}]
JSON_TABLE 的 COLUMNS 子句必须显式声明别名和类型（FOR ORDINALITY 或 INT PATH "$.id"），否则字段不可见
路径表达式里用 $[*] 表示遍历数组每一项；嵌套对象用 $.field，不要漏掉 $

SELECT u.name, jt.tag_name
FROM users u
JOIN JSON_TABLE(
  u.tags, '$[*]' COLUMNS (
    tag_name VARCHAR(50) PATH '$'
  )
) AS jt
WHERE u.id = 123;

PostgreSQL 怎么用 `jsonb_array_elements()` 关联 JSONB 数组

PostgreSQL 的 jsonb_array_elements() 是更轻量的展开方式，返回一行一行的 JSONB 值，可直接在 FROM 或 LATERAL 中使用。注意它只接受 jsonb 类型，json 类型需先转： col::jsonb。

容易踩的坑是忘记加 LATERAL —— 如果在 WHERE 或 ON 里直接调用函数，会报错 “cannot use column from outer query”，因为普通函数无法引用左侧表字段。

Json Schema Toolkit

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

下载

若数组元素是对象（如 [{"id":1},{"id":2}]），用 elem->>'id' 提取文本值，elem->'id' 返回 JSONB 类型
关联时优先用 LATERAL + JOIN，比在 ON 里写子查询更清晰、性能更好
如果数组可能为空或为 NULL，jsonb_array_elements() 默认跳过，需要外连接或 COALESCE 处理缺失情况

SELECT u.name, t.title
FROM users u
JOIN LATERAL jsonb_array_elements(u.posts) AS elem
JOIN articles t ON (elem->>'article_id')::int = t.id
WHERE u.id = 456;

SQL Server 怎么用 `OPENJSON()` 解析 JSON 数组并 JOIN

SQL Server 2016+ 的 OPENJSON() 必须配合 WITH 子句才能把 JSON 字段映射成关系列，否则只返回键值对（key, value, type）。它本质是表值函数，可直接用于 FROM 或 APPLY。

典型错误是省略 WITH，导致后续无法按业务字段（如 product_id）关联；或者没加 AS json_data 别名，使 ON 条件里无法引用解析后的列。

OPENJSON() 默认路径是 $，对数组要显式指定 $.items 或直接传入数组字段值
数据类型必须严格匹配：INT 对应整数，NVARCHAR(50) 对应字符串，否则转换失败返回 NULL
若 JSON 字段为 NULL，OPENJSON() 返回空集，不影响主表行；但若想保留主表行，得改用 OUTER APPLY

SELECT u.username, p.name
FROM users u
CROSS APPLY OPENJSON(u.cart, '$.items') 
  WITH (product_id INT '$.id', qty INT '$.quantity') AS p
JOIN products pr ON p.product_id = pr.id
WHERE u.id = 789;