MariaDB实现UNNEST数据展开功能有哪些可用替代方法

MariaDB 没有和BigQuery完全同名的UNNEST函数，但存在成熟的原生方案可以实现完全一致的嵌套数据展开效果，根据嵌套数据的存储格式，对应实现方式如下：

• 针对JSON格式存储的嵌套字段（你截图中Properties这类嵌套结构最常用JSON类型存储）
  MariaDB 10.2及以上版本支持的JSON_TABLE()函数是最适配的方案，效果和UNNEST完全对齐。举个实际使用示例，如果你的properties字段中存储了tags数组，要把数组元素拆成单行可以直接写：

  SELECT
    base.id,
    expand.tag_name
  FROM your_business_table base,
  JSON_TABLE(
    base.properties,
    '$.tags[*]' COLUMNS (
      tag_name VARCHAR(100) PATH '$'
    )
  ) expand;
  

  如果需要展开嵌套对象的键值对、多层嵌套结构，只需要调整JSON_TABLE里的路径参数和列定义规则即可。
• 针对固定分隔符存储的字符串类数组
  可以用MariaDB内置的SEQUENCE引擎生成连续整数序列，配合字符串函数完成拆分。比如要展开逗号分隔存储的字符串值"apple,banana,orange"：

  SELECT
    base.id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(base.tag_list, ',', seq.num), ',', -1) as single_tag
  FROM your_business_table base
  INNER JOIN seq_1_to_200 seq
    ON seq.num <= LENGTH(base.tag_list) - LENGTH(REPLACE(base.tag_list, ',', '')) + 1;
  

  其中seq_1_to_200是SEQUENCE引擎生成的1到200的连续整数序列，你可以根据单字段内数组元素的最大长度调整序列的上限值。
• 10.2以下不支持JSON_TABLE的老旧版本替代方案
  可以提前创建一张存储连续整数的数字辅助常量表，替代上面的SEQUENCE引擎生成的序列，再配合JSON提取函数、字符串拆分函数完成展开，核心逻辑和上述拆分方案一致，只是整数序列来源换成自建常量表。

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