Presto提取多层嵌套JSON数组对齐原表列解决CROSS JOIN冗余问题

多层CROSS JOIN UNNEST产生冗余笛卡尔积的核心原因有两个：一是未提前过滤无效JSON节点，把所有层级的数组全量展开后再做筛选，产生大量无意义中间行；二是同层级平行数组独立展开时，默认做全量交叉匹配，而非按位置关联。可根据场景选择以下方案解决：

方案1：提前过滤无效节点，减少无效展开 #

不要将JSON全量解析展开后再用WHERE条件筛选，用Presto内置的filter数组函数，在UNNEST之前就把不符合条件的JSON节点剔除，从源头减少参与展开的数据量。
以给出的两层嵌套场景为例，优化后可直接实现id对齐、无冗余行的效果，代码如下：

select
  t.id,
  json_extract_scalar(item_detail, '$.itemid') as itemid,
  json_extract_scalar(item_detail, '$.modelid') as modelid,
  json_extract_scalar(item_detail, '$.quantity') as quantity,
  json_extract_scalar(shopping, '$.shopid') as shopid
from dataset t,
-- 提前过滤外层数组中shopid不符合要求的节点，不进入后续展开逻辑
unnest(
  filter(
    cast(json_parse(t.nested) as array(json)),
    node -> json_extract_scalar(node, '$.shopid') = '128449080'
  )
) as x(shopping),
unnest(cast(json_extract(shopping, '$.item_detail') as array(json))) as y(item_detail)

方案2：同层级平行数组合并后展开，避免交叉匹配 #

如果单个JSON节点下存在多个平行、按位置一一对应的数组（比如同节点下同时存在商品明细数组、优惠券数组），不要对多个数组分别写UNNEST，用arrays_zip函数将同层的多个数组合并为结构体数组后，单次UNNEST即可按位置配对展开，完全避免笛卡尔积。
示例写法如下：

select
  t.id,
  item.itemid,
  promo.promoid
from dataset t,
unnest(
  filter(cast(json_parse(t.nested) as array(json)), node -> json_extract_scalar(node, '$.shopid') = '128449080')
) as x(shopping),
-- 将同层级的商品、优惠券两个平行数组合并后单次展开
unnest(
  arrays_zip(
    cast(json_extract(shopping, '$.item_detail') as array(json)),
    cast(json_extract(shopping, '$.promo_detail') as array(json))
  )
) as z(item, promo)

如果使用的是较早版本的Presto没有arrays_zip，可以用zip_with自定义合并逻辑实现相同效果。

方案3：用JSONPath直接提取目标节点，减少UNNEST层级 #

针对3层及以上的深度嵌套场景，不需要逐层UNNEST拆解，Presto的json_extract支持带过滤逻辑的JSONPath语法，可以直接定位到目标层级的数组，一次UNNEST即可拿到结果，从根本上避免多层JOIN带来的冗余问题。
还是以需求场景为例，用JSONPath直接提取的写法更简洁：

select
  t.id,
  json_extract_scalar(item_detail, '$.itemid') as itemid,
  json_extract_scalar(item_detail, '$.modelid') as modelid,
  json_extract_scalar(item_detail, '$.quantity') as quantity,
  '128449080' as shopid
from dataset t,
unnest(
  cast(
    json_extract(
      json_parse(t.nested),
      -- 直接筛选shopid符合条件的节点，提取其下所有item_detail元素
      '$[?(@.shopid == "128449080")].item_detail[*]'
    ) as array(json)
  )
) as y(item_detail)

注意事项 #

• 用json_extract_scalar提取字符串类型值时，过滤条件里的匹配值要加单引号，比如示例中的shopid是字符串类型，不能直接写数字128449080，否则会出现类型不匹配、匹配结果为空的问题。
• 用arrays_zip合并数组时，如果同层多个数组长度不一致，函数会自动用null补全缺失位置，不会丢失数据。

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