HiveQL如何统计JSON列含指定键值的表记录数

问题说明 #

在名为product_type的表中，需统计JSON类型字段type里键"costly"取值为false的记录数。表数据如下：

id  |   product |            type                     |
1    | product_1  | {"costly": true, "l_type": true}  |
2    | product_2  | {"costly": false, "l_type": true} |
3    | product_3  | {"costly": false, "l_type": true} |
4    | product_4  | {"costly": false, "l_type": true} |

期望结果为3，但使用语句select count(*) from product_table where type LIKE '%"costly": false%'未返回有效结果，需用合适的HiveQL函数实现查询。

问题原因 #

用LIKE匹配JSON字符串极易失效，因为JSON格式可能存在空格差异（比如"costly":false和"costly": false的空格不同），或者键值对顺序变动，都会导致匹配失败。

可行解决方案 #

Hive提供了专门处理JSON的函数，推荐两种实现方式：

方式1：使用get_json_object函数 #

该函数可从JSON字符串中提取指定路径的值，语法为get_json_object(json_string, '$.key')。
对应查询语句：

SELECT COUNT(*) 
FROM product_type 
WHERE get_json_object(type, '$.costly') = 'false';

方式2：使用json_tuple函数 #

如果需要提取多个JSON键值，json_tuple效率更高，支持一次性提取多个字段，语法为json_tuple(json_string, key1, key2, ...)。
对应查询语句：

SELECT COUNT(*) 
FROM product_type 
LATERAL VIEW json_tuple(type, 'costly') jt AS costly 
WHERE jt.costly = 'false';

补充说明 #

• get_json_object适合单个键值提取场景，json_tuple更适用于多键值提取，性能表现更优。
• 注意比较值需用字符串'false'，因为Hive中从JSON提取的布尔值会以字符串形式返回。

内容的提问来源于stack exchange，提问作者zeva_u