json行转列问题

Viewed 74
2.1

请问目前Doris能处理出这样的目标数据吗，sql应该怎么写

-- 表
CREATE TABLE market_1_all(
            part_date date,
            app_id VARCHAR(16) comment '应用ID',
            user_id VARCHAR(16) comment '用户ID',
            `channel_ids` JSON comment '渠道素材ID'
) UNIQUE KEY(`part_date`,`app_id`,`user_id`)
        DISTRIBUTED BY HASH(`user_id`) BUCKETS 60
        PROPERTIES (
                       "storage_format" = "V2",
                       "enable_unique_key_merge_on_write" = "true",
                       "replication_num" = "1"
                   );

-- 数据
insert into market_1_all values
('2024-08-06','app_id_11','1','[{"id":"11"},{"id":"12"},{"id":"13"}]')
,('2024-08-06','app_id_11','2','[{"id":"21"},{"id":"22"},{"id":"23"}]')
,('2024-08-06','app_id_11','3','[{"id":"31"},{"id":"32"},{"id":"33"}]');

-- 查询
select user_id,channel_ids from market_1_all;

+---------+-------------------------------------------------+
| user_id | channel_ids                                     |
+---------+-------------------------------------------------+
| 1       | [{"id":"11"},{"id":"12"},{"id":"13"}]           |
| 2       | [{"id":"21"},{"id":"22"},{"id":"23"}]           |
| 3       | [{"id":"31"},{"id":"32"},{"id":"33"}]           |
+---------+-------------------------------------------------+
-- 目标表
+---------+----------------+
| user_id | channel_ids    |
+---------+----------------+
| 1       | 11             |
| 1       | 12             |
| 1       | 13             |
| 2       | 21             |
| 2       | 22             |
| 2       | 23             |
| 3       | 31             |
| 3       | 32             |
| 3       | 33             |
+---------+----------------+
sqffly1
2 Answers

可以暂时实现，但结果不是很理想，结果多了引号

-- 表
CREATE TABLE market_1_all(
            part_date date,
            app_id VARCHAR(16) comment '应用ID',
            user_id VARCHAR(16) comment '用户ID',
            `channel_ids` string
) UNIQUE KEY(`part_date`,`app_id`,`user_id`)
        DISTRIBUTED BY HASH(`user_id`) BUCKETS 60
        PROPERTIES (
                       "storage_format" = "V2",
                       "enable_unique_key_merge_on_write" = "true",
                       "replication_num" = "1"
                   );

-- 数据
insert into market_1_all values
('2024-08-06','app_id_11','1','[{"id":"11"},{"id":"12"},{"id":"13"}]')
,('2024-08-06','app_id_11','2','[{"id":"21"},{"id":"22"},{"id":"23"}]')
,('2024-08-06','app_id_11','3','[{"id":"31"},{"id":"32"},{"id":"33"}]');
-- 查询
select user_id, json_extract(e1,'$.id') from market_1_all lateral view explode_json_array_json(channel_ids) tmp1 as e1 order by user_id, e1;
+---------+--------------------------+
| user_id | json_extract(e1, '$.id') |
+---------+--------------------------+
| 1       | "11"                     |
| 1       | "12"                     |
| 1       | "13"                     |
| 2       | "21"                     |
| 2       | "22"                     |
| 2       | "23"                     |
| 3       | "31"                     |
| 3       | "32"                     |
| 3       | "33"                     |
+---------+--------------------------+
sqffly1

引号可以 replace 替换吧

yky_young57