json - 如何检查嵌套类型elasticsearch对象中的缺失键？

Question

我有一种情况，我以键值对json格式doc收集一些常规信息和数据库信息(db2，oracle，sybase，informix)。

我还具有一些规则来检查上述文档是否满足特定规则，如果满意，则退还该特定文档以进行分析。

这是医生

PUT /twitter/tweet/1
{
    "name": "Athena",
    "version": 1,
    "db": {
        "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit",
        "oracle_props": [
            {
                "@name": "open_cursors",
                "@value": 4000
            },
            {
                "@name": "USER_ROLE_PRIVS_COUNT",
                "@value": 1
            }
        ]
    }
}

这是它的映射

PUT /twitter/tweet/_mapping
{
   "properties": {
      "db": {
         "type": "object",
         "properties": {
            "@type": {
               "type": "string"
            },
            "oracle_props": {
               "type": "nested",
               "properties": {
                  "@name": {
                     "type": "string"
                  },
                  "@value": {
                     "type": "long"
                  }
               }
            }
         }
      }
   }
}

规则标准

列出文档中带有 name Athena和 Oracle database的推文具有 opencursors less than recommendaed value 4000或何时 opencursors is not present的文档。

因此，仅当以下条件匹配时，上述doc /twitter/tweet/1才会作为结果返回。

If(name ==“Athena”)&&(db。@ type包含“Oracle”关键字)

和(如果(((“open_cursors” @value <4000)或(“open_cursors”未在“db.oracle_props。@ name”下找到)

以下是与上面的文档匹配的搜索查询，但缺少最后一个条件 (即使“db.oracle_props。@ name”下缺少“open_cursors”键，也显示文档“/ twitter / tweet / 1”)

GET /twitter/tweet/_search
{
   "query": {
      "bool": {
         "must": [
            {
               "match": {
                  "tweet.name": "Athena"
               }
            },
            {
               "match": {
                  "tweet.db.@type": "Oracle"
               }
            }
         ],
         "should": [
            {
               "nested": {
                  "path": "db.oracle_props",
                  "query": {
                     "bool": {
                        "must": [
                           {
                              "term": {
                                 "db.oracle_props.@name": "open_cursors"
                              }
                           },
                           {
                              "range": {
                                 "db.oracle_props.@value": {
                                    "lt": 4001
                                 }
                              }
                           }
                        ]
                     }
                  }
               }
            }
         ],
         "minimum_should_match": 1
      }
   }
}

Answer 1

我将再次提及您的other question和my answer。

如果我理解您的要求正确，那么我将设置一些示例文档，这些文档应与我的评论正确或不正确匹配:

// All good, should match
curl -XPUT 'http://localhost:9200/twitter/tweet/1' -d '{
    "name": "Athena",
    "version": 1,
    "db": {
        "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit",
        "oracle_props": [
            {
                "@name": "open_cursors",
                "@value": 4000
            },
            {
                "@name": "USER_ROLE_PRIVS_COUNT",
                "@value": 1
            },
            {
                "@name": "CREATE_PERMISSION",
                "@value": "Y"
            }
        ]
    }
}'

// open cursors missing, should match
curl -XPUT 'http://localhost:9200/twitter/tweet/2' -d '{
    "name": "Athena",
    "version": 1,
    "db": {
        "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit",
        "oracle_props": [
            {
                "@name": "USER_ROLE_PRIVS_COUNT",
                "@value": 2
            },
            {
                "@name": "CREATE_PERMISSION",
                "@value": "N"
            }
        ]
    }
}'

// open_cursors less than 4000, should match
curl -XPUT 'http://localhost:9200/twitter/tweet/3' -d '{
    "name": "Athena",
    "version": 1,
    "db": {
        "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit",
        "oracle_props": [
            {
                "@name": "open_cursors",
                "@value": 2134
            },
            {
                "@name": "USER_ROLE_PRIVS_COUNT",
                "@value": 6
            },
            {
                "@name": "CREATE_PERMISSION",
                "@value": "N"
            }
        ]
    }
}'

// Different name, shouldn't match
curl -XPUT 'http://localhost:9200/twitter/tweet/4' -d '{
    "name": "Alexandroupolis",
    "version": 1,
    "db": {
        "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit",
        "oracle_props": [
            {
                "@name": "open_cursors",
                "@value": 4000
            },
            {
                "@name": "USER_ROLE_PRIVS_COUNT",
                "@value": 1
            },
            {
                "@name": "CREATE_PERMISSION",
                "@value": "Y"
            }
        ]
    }
}'

// open_cursors more than 4000, shouldn't match
curl -XPUT 'http://localhost:9200/twitter/tweet/5' -d '{
    "name": "Athena",
    "version": 1,
    "db": {
        "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit",
        "oracle_props": [
            {
                "@name": "open_cursors",
                "@value": 6500
            },
            {
                "@name": "USER_ROLE_PRIVS_COUNT",
                "@value": 1
            },
            {
                "@name": "CREATE_PERMISSION",
                "@value": "Y"
            }
        ]
    }
}'

因此，我们有3个文档( ID 1,2,3)应该返回。

我发现的解决方案似乎很复杂，也许其他人可以提供一种更简单的方法来解决此问题？

我已经设置了 filtered query，以便能够使用 OR filter

curl -XGET 'http://localhost:9200/twitter/tweet/_search?pretty=true' -d '
{
    "query" : {
        "filtered" : {
            "filter" : {
                /* Set up two conditions */
                "or" : [
                    /* First */
                    {
                        /* Check for open_cursors AND value < 4000 */
                        "bool" : {
                            "must" : [
                                /* Same nested query as in other questions answer */
                                {
                                    "nested" : {
                                        "path" : "db.oracle_props",
                                        "filter" : {
                                            "bool" : {
                                                "must" : [
                                                    {
                                                    "term": {
                                                        "db.oracle_props.@name": "open_cursors"
                                                    }
                                                },
                                                {
                                                    "range": {
                                                        "db.oracle_props.@value": {
                                                            "lte": 4000
                                                        }
                                                    }
                                                }
                                                ]
                                            }
                                        }
                                    }
                                }
                            ]
                        }
                    },
                    /* OR */
                    {
                        "bool" : {
                            /* watch out: negation, this MUST NOT be found*/
                            "must_not" : [
                                {
                                    "nested" : {
                                        "path" : "db.oracle_props",
                                        "filter" : {
                                            "bool" : {
                                                /* We do not want open_cursors to be in the nested document */
                                                "must" : [
                                                    {
                                                    "term": {
                                                        "db.oracle_props.@name": "open_cursors"
                                                    }
                                                }
                                                ]
                                            }
                                        }
                                    }
                                }
                            ]
                        }
                    }
                ]
            },
            /* the query for the non-nested things */
            "query" : {
                "bool" : {
                    "must" : [
                        {
                            "match" : {"tweet.name" : "Athena"}
                        },
                        {
                            "match" : {"tweet.db.@type" : "Oracle"}
                        }
                    ]
                }
            }
        }
    }
}
'

退回文件1,2和3。

更新:
这是一个更简单的解决方案，也应该起作用。谢谢@TuanHuynh

curl -XGET 'http://localhost:9200/twitter/tweet/_search?pretty=true' -d '
{
    "query" : {
        "filtered" : {
            "filter" : {
                /* Set up two conditions */
                "or" : [
                    /* First */
                    {
                        "nested" : {
                            "path" : "db.oracle_props",
                            "filter" : {
                                "bool" : {
                                    "must" : [
                                        {
                                        "term": {
                                            "db.oracle_props.@name": "open_cursors"
                                        }
                                    },
                                    {
                                        "range": {
                                            "db.oracle_props.@value": {
                                                "lte": 4000
                                            }
                                        }
                                    }
                                    ]
                                }
                            }
                        }
                    },
                    /* OR */
                    {
                        "nested" : {
                            "path" : "db.oracle_props",
                            "filter" : {
                                "bool" : {
                                    /* We do not want open_cursors to be in the nested document */
                                    "must" : [
                                        {
                                        "term": {
                                            "db.oracle_props.@name": "open_cursors"
                                        }
                                    }
                                    ]
                                }
                            }
                        }
                    }
                ]
            },
            /* the query for the non-nested things */
            "query" : {
                "bool" : {
                    "must" : [
                        {
                            "match" : {"tweet.name" : "Athena"}
                        },
                        {
                            "match" : {"tweet.db.@type" : "Oracle"}
                        }
                    ]
                }
            }
        }
    }
}
'