{
    "颜色标签": {
        "颜色": ["黑色", "白色", "红色", "蓝色", "绿色", "紫色"],
        "尺寸": ["超大", "大"]
    },
    "颜色标签2": {
        "颜色2": ["黑色", "白色", "红色", "蓝色", "绿色", "紫色", "黄色"],
        "尺寸2": ["超大", "大"]
    }
}

问题

使用SparkSQL将上述的数据根据嵌套提取成3列

解答

with base_tb as (
    select '{
       "颜色标签": {
            "颜色": ["黑色", "白色", "红色", "蓝色", "绿色", "紫色"],
            "尺寸": ["超大", "大"]
        },
       "颜色标签2": {
            "颜色2": ["黑色", "白色", "红色", "蓝色", "绿色", "紫色", "黄色"],
            "尺寸2": ["超大", "大"]
        }
    }' as col
)

select key, inner_key, json_tuple(inner_json, inner_key) js
from (
    select key, json_object_keys(inner_json) inner_keys, inner_json
    from (
        select key, json_tuple(col, key) inner_json
        from (
            select json_object_keys(col) keys, col
            from base_tb
        ) as a
        LATERAL view explode(keys) as key
    ) as a
) as a
LATERAL view explode(inner_keys) as inner_key

主要采用Spark 3.1.0新增的json_object_keys函数拿到key值，然后依次提取，最后输出如下：