如何从JSON列的数组元素中实现数据库端分页查询？

已知my_table表的data列为JSON类型，包含rows数组，当前SQL语句提取整个数组：

SELECT JSON_EXTRACT(data, '$.rows[*]') AS name from my_table ;

需要直接在数据库层面实现对该数组的分页（例如每页25条，按页码依次取值）。

以下是主流数据库的实现方案：

MySQL（5.7+，8.0+推荐） #

利用JSON_TABLE将JSON数组转换为关系型行数据，再通过LIMIT + OFFSET实现分页：

基础分页（按数组原始顺序） #

-- 第1页（前25条）
SELECT t.id, jt.name
FROM my_table t,
     JSON_TABLE(t.data, '$.rows[*]' COLUMNS (
         row_index FOR ORDINALITY, -- 保留数组原始索引，用于稳定排序
         name VARCHAR(255) PATH '$.name'
     )) jt
ORDER BY jt.row_index
LIMIT 25 OFFSET 0;

-- 第2页（第26-50条）
SELECT t.id, jt.name
FROM my_table t,
     JSON_TABLE(t.data, '$.rows[*]' COLUMNS (
         row_index FOR ORDINALITY,
         name VARCHAR(255) PATH '$.name'
     )) jt
ORDER BY jt.row_index
LIMIT 25 OFFSET 25;

FOR ORDINALITY会生成数组元素的原始位置索引，保证排序稳定，避免分页时数据重复或遗漏。

PostgreSQL #

使用jsonb_array_elements（json类型用json_array_elements）解析数组，结合OFFSET + FETCH实现分页：

-- 第1页
SELECT t.id, (row_data ->> 'name') AS name
FROM my_table t,
     jsonb_array_elements(t.data -> 'rows') AS row_data
ORDER BY (row_data ->> 'name') -- 或按数组顺序排序，需额外处理索引
LIMIT 25 OFFSET 0;

-- 标准分页语法（PostgreSQL 9.5+支持）
SELECT t.id, (row_data ->> 'name') AS name
FROM my_table t,
     jsonb_array_elements(t.data -> 'rows') AS row_data
ORDER BY (row_data ->> 'name')
OFFSET 25 FETCH NEXT 25 ROWS ONLY;

如果需要按数组原始顺序排序，可以使用WITH ORDINALITY获取索引：

SELECT t.id, (row_data ->> 'name') AS name, ordinality AS row_index
FROM my_table t,
     jsonb_array_elements(t.data -> 'rows') WITH ORDINALITY AS row_data(row_data, ordinality)
ORDER BY row_index
OFFSET 0 FETCH NEXT 25 ROWS ONLY;

SQL Server #

通过OPENJSON解析JSON数组，使用OFFSET ... FETCH语法分页：

-- 第1页
SELECT t.id, jt.name
FROM my_table t
CROSS APPLY OPENJSON(t.data, '$.rows')
WITH (
    name VARCHAR(255) '$.name'
) AS jt
ORDER BY jt.name -- 或按自定义规则排序
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY;

-- 第2页
SELECT t.id, jt.name
FROM my_table t
CROSS APPLY OPENJSON(t.data, '$.rows')
WITH (
    name VARCHAR(255) '$.name'
) AS jt
ORDER BY jt.name
OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY;

关键注意事项 #

• 分页必须配合稳定排序：如果没有明确的ORDER BY子句，数据库返回的行顺序可能不稳定，导致不同页出现重复数据或遗漏。
• 根据实际JSON结构调整字段路径：如果rows数组中的对象结构不同，需要修改PATH或WITH子句中的字段定义。

内容的提问来源于stack exchange，提问作者Amr Elkady