Hive/SparkSQL中过滤数组元素并提取指定字段值的问题

首先修正原SQL中的问题，再调整JSONPath表达式以满足需求：

原代码的错误点 #

• JSON字符串里的none不符合JSON语法规范，需改为null
• JSONPath表达式未正确筛选排除含dep_namename的元素，也未指定提取completed字段
• SQL语法存在错误：get_json_object的参数括号未闭合，别名定义位置不正确

正确的SQL实现 #

WITH dataset AS 
(
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"dep_namename":"project1", "completed":null},{"name":"project2", "completed":false},{"name":"project3", "completed":true}]}'
    AS myblob
)
SELECT get_json_object(myblob, '$.projects[?(!exists(@.dep_namename))].completed') AS completed_values
FROM dataset;

说明 #

• JSONPath表达式$.projects[?(!exists(@.dep_namename))].completed的作用：
  • !exists(@.dep_namename)：筛选出不包含dep_namename键的数组元素
  • .completed：提取这些元素中的completed字段值
• 执行后输出结果为["false","true"]，若需要纯布尔数组格式，可根据使用的SQL引擎（如Hive、Spark SQL）进一步处理，比如用json_tuple或explode结合聚合函数生成目标格式。

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