Spark SQL中能否指定inline_outer函数生成的列名？

开发者阿泽

2026-7-31

在Spark SQL中为inline_outer生成的列指定别名的方法

原表结构与数据

id	campaigns
2	`[{"id": "1", "title": "test", "type": "one"}, {"id": "2", "title": "test2", "type": "two"}]`
5	`[{"id": "3", "title": "test3", "type": "three"}]`

期望结果

id	campaignId	title	type
2	1	test	one
2	2	test2	two
5	3	test3	three

当前代码与遇到的问题

当前使用的SQL需要通过子查询重命名原表id字段来避免和结构体中的id冲突：

SELECT orderId AS id, id AS campaignid, title, type
FROM (
    SELECT id AS orderId, inline_outer(from_json(campaigns, 'ARRAY<STRUCT<id: STRING, title: STRING, type: STRING>>'))
    FROM `order`
);

尝试过以下两种直接指定别名的写法，但不符合Spark SQL语法：

SELECT id, inline_outer(from_json(campaigns, 'ARRAY<STRUCT<id: STRING, title: STRING, type: STRING>>')) AS ('campaignId', 'title', 'type')
FROM `order`;

SELECT id, inline_outer(from_json(campaigns, 'ARRAY<STRUCT<id: STRING, title: STRING, type: STRING>>')) AS {'campaignId', 'title', 'type'}
FROM `order`;

解决方案

在Spark SQL中，你可以通过**LATERAL VIEW配合inline_outer**直接为展开后的列指定别名，无需子查询：

SELECT 
    o.id, 
    c.campaignId, 
    c.title, 
    c.type
FROM 
    `order` o
LATERAL VIEW inline_outer(from_json(o.campaigns, 'ARRAY<STRUCT<id: STRING, title: STRING, type: STRING>>')) c AS campaignId, title, type;

也可以通过将inline_outer的结果作为结构体别名，再逐个为字段指定别名：

SELECT 
    id, 
    campaign.id AS campaignId, 
    campaign.title, 
    campaign.type
FROM 
    `order`,
    inline_outer(from_json(campaigns, 'ARRAY<STRUCT<id: STRING, title: STRING, type: STRING>>')) AS campaign;