问题描述:
trade__shop_order_b_o表中字段matchedtags , 数据类型为string,其中存储了JSON_ARRAY对象信息,例如
SELECT id, matchedtags
FROM trade__shop_order_b_o
| id | matchedtags |
|---|---|
| 89393908 | [{"@id":"@id:14f8259d-8742-466a-ba59-2ed2e0ddf3af","id":3,"name":"客审晚处理","type":"LOGIC","tagPurpose":"SALE","level":"ORDER_LINE"},{"@id":"@id:6ff27674-f24b-4d49-81af-601726020b66","id":7,"name":"重新客审","type":"LOGIC","tagPurpose":"SALE","level":"ORDER"},{"@id":"@id:9a88f663-17f7-46e7-ac5d-7b8df939650d","id":41,"name":"超期订单","type":"LOGIC","tagPurpose":"SALE","level":"ORDER"},{"@id":"@id:83a4068d-d9ac-423a-9616-3e58d53a9c33","id":130025,"name":"机器人测试等通知发货","type":"CUSTOM","tagPurpose":"ON_SALE","level":"ORDER"}] |
想要实现的效果:
根据这个对象中的字段信息,拿到这个对象中的值信息,
例如 对 这个嵌套的对象进行遍历,
当 tagPurpose = SALE,那么就拿到name 对应的值信息 ,也就是["客审晚处理", "超期订单", "重新客审"]
当 tagPurpose = ON_SALE,那么拿到的name 对应的值信息,也就是["机器人测试等通知发货"]
我当前的SQL处理方式为
SELECT
COLLECT_SET(sale_tags) AS sale_tags_list
, COLLECT_SET(on_sale_tags) AS on_sale_tags_list
FROM (
SELECT matchedtags
, tmp.matchtag
, CASE
WHEN
get_json_string(tmp.matchtag, '$.tagPurpose') = 'SALE'
THEN get_json_string(tmp.matchtag, '$.name') END AS sale_tags
, CASE
WHEN
get_json_string(tmp.matchtag, '$.tagPurpose') = 'ON_SALE'
THEN get_json_string(tmp.matchtag, '$.name') END AS on_sale_tags
FROM trade__shop_order_b_o LATERAL VIEW explode_json_array_json(matchedtags) tmp AS matchtag
) t1
;
当前的写法有一些麻烦,想问下doris 有没有像 duck db 的unnest 函数,
或者这种嵌套的Json array 类型如果用 variant数据类型进行处理的话,需要怎么做呢?
或者大家有没有其他的一些处理方式可以得到这种结果呢?
测试用例数据:
DDL :
CREATE TABLE `trade__shop_order_b_o` (
`id` bigint NOT NULL,
`matchedtags` text NULL
) ENGINE=OLAP
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);
INSERT INTO trade__shop_order_b_o VALUES (89393908,"[{""@id"":""@id:14f8259d-8742-466a-ba59-2ed2e0ddf3af"",""id"":3,""name"":""客审晚处理"",""type"":""LOGIC"",""tagPurpose"":""SALE"",""level"":""ORDER_LINE""},{""@id"":""@id:6ff27674-f24b-4d49-81af-601726020b66"",""id"":7,""name"":""重新客审"",""type"":""LOGIC"",""tagPurpose"":""SALE"",""level"":""ORDER""},{""@id"":""@id:9a88f663-17f7-46e7-ac5d-7b8df939650d"",""id"":41,""name"":""超期订单"",""type"":""LOGIC"",""tagPurpose"":""SALE"",""level"":""ORDER""},{""@id"":""@id:83a4068d-d9ac-423a-9616-3e58d53a9c33"",""id"":130025,""name"":""机器人测试等通知发货"",""type"":""CUSTOM"",""tagPurpose"":""ON_SALE"",""level"":""ORDER""}]");
