通过SQL过滤JSON数组元素：提取含指定键的对象

你的原查询问题在于用字符串模糊匹配x like '%name%'，这种方式会误保留包含dep_namename的元素（因为该字符串里也有"name"），无法精准过滤。需要借助JSON专用函数来检查元素的键是否符合要求。

以下是针对Oracle SQL的正确实现：

方法1：使用JSON路径过滤 #

WITH dataset AS 
(
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"dep_namename":"project1", "completed":false},{"name":"project2", "completed":false},{"name":"project3", "completed":true}]}'
     AS myblob
)
SELECT json_query(
         myblob,
         'lax $.projects[*]?(@.name exists() and every $k in json_keys(@) satisfies not starts_with($k, "dep_name"))'
         WITH ARRAY WRAPPER
       ) AS project_name
FROM dataset;

逻辑说明： #

• lax $.projects[*]遍历projects数组的所有元素
• ?()是JSON路径的过滤条件：
  • @.name exists()确保当前元素包含name键
  • every $k in json_keys(@) satisfies not starts_with($k, "dep_name")检查元素的所有键都不以dep_name开头，彻底排除目标元素

方法2：使用filter结合json_table #

WITH dataset AS 
(
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"dep_namename":"project1", "completed":false},{"name":"project2", "completed":false},{"name":"project3", "completed":true}]}'
     AS myblob
)
SELECT filter(
         json_value(myblob, 'lax $.projects' returning json),
         x -> json_exists(x, '$.name') 
             and not exists (
               select 1 
               from json_table(json_keys(x), '$[*]' columns key_name varchar2(100) path '$')
               where key_name like 'dep_name%'
             )
       ) AS project_name
FROM dataset;

逻辑说明： #

• json_value(myblob, 'lax $.projects' returning json)把projects数组转为JSON类型
• filter遍历数组元素：
  • json_exists(x, '$.name')检查元素含name键
  • 子查询通过json_table把元素的所有键拆成行，判断是否存在以dep_name开头的键，不存在则保留该元素

两种方法都能得到你期望的输出：

[
    {"name":"project2", "completed":false},
    {"name":"project3", "completed":true}
]

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