在 Presto 中提取复杂的嵌套 JSON 数组

我有一个像这样的复杂 JSON 对象：

  {
    "item_detail": [
      {
        "itemid": "4702385896",
        "modelid": "8307307322",
        "quantity": "1"
      },
      {
        "itemid": "3902478595",
        "modelid": "8306561848",
        "quantity": "1"
      },
      {
        "itemid": "3409528897",
        "modelid": "10922686275",
        "quantity": "1"
      },
      {
        "itemid": "4702385896",
        "modelid": "8307307323",
        "quantity": "1"
      }
    ],
    "shopid": "128449080"
  },
  {
    "item_detail": [
      {
        "itemid": "7906381345",
        "modelid": "9745718882",
        "quantity": "1"
      },
      {
        "itemid": "6710792892",
        "modelid": "11474621623",
        "quantity": "1"
      }
    ],
    "shopid": "36121097"
  }
]

我正在努力将所有（itemid、shopid）提取到 Presto 的行中。我想要的结果是：

itemid     | shopid
-----------+-------
4702385896 | 128449080
3902478595 | 128449080
3409528897 | 128449080
4702385896 | 128449080
7906381345 | 36121097
6710792892 | 36121097

我已经使用 CROSS JOIN UNNEST 和 TRANSFORM 来获得结果，但没有运气。有人有解决方案吗？

非常感谢！

Use json_extract https://prestosql.io/docs/current/functions/json.html#json_extract with cast to array and unnest https://prestosql.io/docs/current/sql/select.html#unnest， 像这样：

presto:default> SELECT
             ->  json_extract_scalar(item_detail, '$.itemid') itemid,
             ->  json_extract_scalar(shopping, '$.shopid') shopid
             -> FROM t
             -> CROSS JOIN UNNEST(CAST(my_json AS array(json))) AS x(shopping)
             -> CROSS JOIN UNNEST(CAST(json_extract(shopping, '$.item_detail') AS array(json))) AS y(item_detail)
             -> ;
             ->
   itemid   |  shopid
------------+-----------
 4702385896 | 128449080
 3902478595 | 128449080
 3409528897 | 128449080
 4702385896 | 128449080
 7906381345 | 36121097
 6710792892 | 36121097
(6 rows)

（在 Presto 327 上验证）

顺便说一句，如果任何数组可能为空或丢失，我建议使用LEFT JOIN UNNEST ... ON true代替CROSS JOIN UNNEST（这需要一个像样的 Presto 版本）：

SELECT
 json_extract_scalar(item_detail, '$.itemid') itemid,
 json_extract_scalar(shopping, '$.shopid') shopid
FROM t
LEFT JOIN UNNEST(CAST(my_json AS array(json))) AS x(shopping) ON true
LEFT JOIN UNNEST(CAST(json_extract(shopping, '$.item_detail') AS array(json))) AS y(item_detail) ON true;