doris 可以实现一个类似mysql json_table 的函数可否,这样nosql 数据更好聚合

👨🏻‍💻 技术问答
1
  1. 数据格式
    create table test_json_table(
    roay_way_detail json
    );

INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 2, “productId”: 1591251275692351491, “productName”: “益生菌”, “productAlias”: “B”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591249630820540419, “productName”: “纯羊奶”, “productAlias”: “A”}, {“mark”: “MONDAY”, “quantity”: 2, “productId”: 1591250052880769025, “productName”: “草莓味”, “productAlias”: “C”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 2, “productId”: 1591251275692351491, “productName”: “益生菌”, “productAlias”: “B”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591250438224060417, “productName”: “麦芽味”, “productAlias”: “D”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591250052880769025, “productName”: “草莓味”, “productAlias”: “C”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591250052880769025, “productName”: “草莓味”, “productAlias”: “C”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591250438224060417, “productName”: “麦芽味”, “productAlias”: “D”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591249630820540419, “productName”: “纯羊奶”, “productAlias”: “A”}, {“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591251803566481410, “productName”: “红枣枸杞味”, “productAlias”: “F”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591249630820540419, “productName”: “纯羊奶”, “productAlias”: “A”}, {“mark”: “MONDAY”, “quantity”: 10, “productId”: 1591251803566481410, “productName”: “红枣枸杞味”, “productAlias”: “F”}]’);
INSERT INTO test_json_table (roay_way_detail) VALUES (‘[{“mark”: “MONDAY”, “quantity”: 1, “productId”: 1591250052880769025, “productName”: “草莓味”, “productAlias”: “C”}]’);

2.mysql 实现如下
select tt.productId ,sum(tt.quantity) from test_json_table t
inner join json_table(t.roay_way_detail,‘$[*]’ columns (productId bigint path ‘$.productId’,quantity int path ‘$.quantity’))tt group by tt.productId;