如何在SQL中对JSON数组字段解包并进行分组聚合?

冬静酱_2849

冬静酱_2849

2026-09-15

424人浏览

原创

jsonb_array_elements()将JSONB数组每个元素展开为一行,仅支持jsonb类型,需显式转换;遇NULL返回空结果集,建议配合LATERAL LEFT JOIN保留原行,展开后默认别名value应显式重命名。

如何在sql中对json数组字段解包并进行分组聚合?

PostgreSQL 中用 jsonb_array_elements() 展开 JSON 数组

PostgreSQL 的 jsonb_array_elements() 是解包 JSON 数组最直接的函数,它把每个数组元素转成一行。注意:它只接受 jsonb 类型,如果字段是 json,得先显式转换:jsonb_array_elements(data::jsonb)。

常见错误是直接对 text 字段调用该函数,会报错 function jsonb_array_elements(text) does not exist;还有人误用 json_array_elements()(不带 b),在含 Unicode 或数字精度要求高的场景可能丢数据,优先选 jsonb_* 系列。

  • 必须确保目标字段非 NULL,否则 jsonb_array_elements() 返回空结果集(不是 NULL 行)
  • 若想保留原始行即使数组为空,需改用 LEFT JOIN LATERAL jsonb_array_elements(...)
  • 展开后字段默认别名为 value,建议显式重命名,比如 jsonb_array_elements(tags::jsonb) AS tag

MySQL 8.0+ 用 JSON_TABLE() 实现等效解包

MySQL 没有类似 jsonb_array_elements() 的标量函数,必须用 JSON_TABLE() —— 它本质是把 JSON 数组“虚拟建表”,需要指定列定义和路径表达式。

典型写法:JSON_TABLE(data, '$[*]' COLUMNS (name TEXT PATH '$.name', score INT PATH '$.score')) AS jt。其中 '$[*]' 表示遍历根数组所有元素,COLUMNS 定义输出字段名、类型和取值路径。

  • 路径表达式必须用单引号包裹,且不能省略 $ 开头;写成 '[*]' 会报错
  • TEXT 类型字段实际最大长度受 MySQL 行限制影响,若原始 JSON 字符串超长,可能被截断,可改用 MEDIUMTEXT 并在 COLUMNS 中声明
  • JSON_TABLE() 不支持对 NULL 或非法 JSON 值静默跳过,遇到会报错 Invalid JSON text,需提前用 JSON_VALID() 过滤

解包后分组聚合的常见陷阱

解包只是第一步,真正容易出问题的是后续 GROUP BY 和聚合逻辑。核心原则:所有非聚合字段必须出现在 GROUP BY 中,且不能直接 GROUP BY 原始 JSON 字段(它不可哈希/不可比较)。

Expanso yaml-to-json
Expanso yaml-to-json

使用 Expanso Edge 管道将 YAML 输入转换为 JSON 格式。

下载
  • PostgreSQL 示例:要统计每个标签出现次数,得写 SELECT tag->>'name' AS tag_name, COUNT(*) FROM tbl, jsonb_array_elements(tags::jsonb) AS tag GROUP BY tag->>'name';写成 GROUP BY tag 会失败
  • MySQL 示例:需在 JSON_TABLE() 外层再套一层查询做聚合,不能在 JSON_TABLE() 的 COLUMNS 子句里写聚合函数
  • 性能隐患:对大表反复展开 JSON 数组可能触发全表扫描 + 大量中间行,建议在 JSON 字段上建 GIN 索引(PostgreSQL)或生成列 + 普通索引(MySQL)加速过滤

SQLite 3.38+ 的替代方案:json_each() 和 json_tree()

SQLite 虽无原生数组解包聚合语法,但 3.38 版本起支持 json_each(),可将 JSON 数组转为含 key、value、type 等列的虚拟表。

关键点:json_each() 默认从根开始解析,若 JSON 是数组,value 列即为各元素;若 JSON 是对象,则需用 json_tree() 配合 WHERE type = 'array' 定位后再展开。

  • json_each() 对非数组 JSON(如对象或标量)返回空结果,不会报错,但容易漏数据——务必确认字段内容结构
  • SQLite 不支持在 GROUP BY 中直接用 json_each().value,需用子查询或 CTE 先展开再聚合
  • 没有 JSON 索引支持,纯内存解析,千万级数据慎用

JSON 数组解包本身不难,难的是跨数据库语法差异大、错误反馈模糊、性能边界不直观。最常被忽略的是:没验证 JSON 数据一致性(空数组、NULL 值、嵌套深度超标),结果聚合时静默丢失行或报错中断。

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

js json json数组

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5043

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1329

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7121

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3763

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7100

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

9961

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1350

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1172

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
WEB前端教程【HTML5+CSS3+JS】
WEB前端教程【HTML5+CSS3+JS】

共101课时 | 21.4万人学习

JS进阶与BootStrap学习
JS进阶与BootStrap学习

共39课时 | 4.9万人学习