PostgreSQL特定JSON搜索逻辑的通用SQL替代实现方案咨询

我已经编写了PostgreSQL专属SQL，可成功从数据库中存储为字符串的JSON数据里检索label元素。
目标JSON数据格式如下：

{"list":[{"label":"Label", "taskReference":"myReference",...}]}

所使用的PostgreSQL查询语句为：

SELECT ELEM ->> 'label' 
FROM MY_TABLE
CROSS JOIN LATERAL jsonb_array_elements(DATA::JSONB -> 'list') AS ELEM 
WHERE ELEM ->> 'taskReference' = 'myReference';

执行结果：

Label

但该查询仅适用于PostgreSQL，由于不同环境会使用不同类型的数据库，现咨询是否可通过标准通用SQL函数实现上述相同功能。

目前没有能完全跨所有数据库的通用JSON查询SQL，但SQL:2016标准定义了一套JSON操作规范，主流数据库大多实现了该标准的子集或兼容语法，以下是具体方案：

1. SQL:2016标准语法（最优通用方案） #

SQL:2016引入的JSON_TABLE函数可将JSON数组转换为关系表，是最接近通用的实现方式，语法如下：

SELECT jt.label
FROM MY_TABLE,
     JSON_TABLE(
         DATA,
         '$.list[*]' COLUMNS (
             label VARCHAR PATH '$.label',
             taskReference VARCHAR PATH '$.taskReference'
         )
     ) AS jt
WHERE jt.taskReference = 'myReference';

该语法被MySQL 8.0+、Oracle 12cR2+等数据库直接支持。

2. 主流数据库适配实现 #

SQL Server #

SQL Server 2016+使用OPENJSON实现类似逻辑：

SELECT jt.label
FROM MY_TABLE
CROSS APPLY OPENJSON(DATA, '$.list')
WITH (
    label VARCHAR(255) '$.label',
    taskReference VARCHAR(255) '$.taskReference'
) AS jt
WHERE jt.taskReference = 'myReference';

MySQL 5.7（无JSON_TABLE版本） #

若无法升级到MySQL 8.0，可通过JSON_EXTRACT结合循环处理（仅作兼容方案，不推荐）：

SELECT JSON_UNQUOTE(JSON_EXTRACT(DATA, CONCAT('$.list[', idx, '].label'))) AS label
FROM MY_TABLE,
     (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS indexes
WHERE JSON_UNQUOTE(JSON_EXTRACT(DATA, CONCAT('$.list[', idx, '].taskReference'))) = 'myReference'
  AND idx < JSON_LENGTH(DATA -> '$.list');

总结 #

• 优先使用JSON_TABLE这类标准函数，能最大限度减少跨数据库的适配工作量；
• 旧版本数据库只能依赖各自的专属JSON函数实现；
• 不存在完全通用的单条SQL覆盖所有数据库场景，需根据目标环境选择对应语法。

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