MariaDB低版本替代JSON_TABLE查询JSON数组方案求助

我有一个存储JSON数组的字段，数据格式如下：

[{"low": 57.07, "rsi": 0.0, "date": 1675935000000, "high": 57.07, "open": 57.07, "close": 57.07, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 0, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 57.0, "rsi": 0.0, "date": 1675935900000, "high": 58.49, "open": 57.07, "close": 58.4, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 2500, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 57.7, "rsi": 0.0, "date": 1675936800000, "high": 58.5, "open": 58.4, "close": 58.49, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 27000, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 58.15, "rsi": 0.0, "date": 1675937700000, "high": 59.5, "open": 58.5, "close": 59.5, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 41000, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 59.0, "rsi": 0.0, "date": 1675938600000, "high": 59.5, "open": 59.5, "close": 59.0, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 2500, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}]

原本使用JSON_TABLE的查询可以正常获取symbol、open、close字段：

SELECT indicators_15.symbol,indicators_15.open,indicators_15.close
FROM indicators_15, 
     JSON_TABLE(data, '$[*]' COLUMNS (
                close DOUBLE  PATH '$.close',
                open DOUBLE PATH '$.open')
     ) indicators_15;

但Namecheap主机的MariaDB版本较低，不支持JSON_TABLE，需要等价的非JSON_TABLE查询方案，输出包含symbol、open、close列的表格。

方案一：递归CTE生成索引序列（兼容MariaDB 10.2+） #

如果你的MariaDB版本支持递归CTE，可自动生成数组索引并提取字段：

WITH RECURSIVE idx AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM idx 
    WHERE JSON_EXTRACT(t.data, CONCAT('$[', n + 1, ']')) IS NOT NULL
)
SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(t.data, CONCAT('$[', idx.n, '].symbol'))) AS symbol,
    JSON_EXTRACT(t.data, CONCAT('$[', idx.n, '].open')) AS `open`,
    JSON_EXTRACT(t.data, CONCAT('$[', idx.n, '].close')) AS close
FROM your_table t
JOIN idx ON JSON_EXTRACT(t.data, CONCAT('$[', idx.n, ']')) IS NOT NULL;

• 替换your_table为存储JSON字段的实际表名
• JSON_UNQUOTE用于去除symbol字段的字符串引号，数值类型的open/close无需此处理
• 递归CTE会自动遍历到数组最后一个有效元素

方案二：临时数字表（兼容更低版本） #

若不支持CTE，可创建临时数字表覆盖数组元素数量：

-- 创建临时数字表，按需扩展数字数量以覆盖最大数组长度
CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(t.data, CONCAT('$[', nums.n, '].symbol'))) AS symbol,
    JSON_EXTRACT(t.data, CONCAT('$[', nums.n, '].open')) AS `open`,
    JSON_EXTRACT(t.data, CONCAT('$[', nums.n, '].close')) AS close
FROM your_table t
JOIN nums ON JSON_EXTRACT(t.data, CONCAT('$[', nums.n, ']')) IS NOT NULL;

• 临时表nums中的数字需覆盖JSON数组的最大元素个数
• 通过判断对应索引的元素是否存在，过滤无效行

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