elasticsearch之exists查询

2023-01-12 16:43:13

一、exists查询简介

elastic search提供了exists查询，用以返回字段存在值的记录，默认情况下只有字段的值为null或者[]的时候，elasticsearch才会认为字段不存在；

exists查询的形式如下，其中field用于指定要查询的字段名字；

{
    "query": {
        "exists": {
            "field": "user"
        }
    }
}

二、测试数据准备

我们尽量模拟document中字段可能出现的各种形式，以便可以全面的测试以下exists查询；

PUT exists_test/_doc/1/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}


PUT exists_test/_doc/2/
{
	    "name":"",
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}


PUT exists_test/_doc/3/
{
	    "name":null,
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}


PUT exists_test/_doc/4/
{
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}


PUT exists_test/_doc/5/
{
	    "name":"sam",
        "age":null,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}

PUT exists_test/_doc/6/
{
	    "name":"sam",
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}


PUT exists_test/_doc/7/
{
	    "name":"sam",
        "age":30,
        "man": null,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}

PUT exists_test/_doc/8/
{
	    "name":"sam",
        "age":30,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}

PUT exists_test/_doc/9/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":["", "lily"],
        "address":{"country":"US"}
}

PUT exists_test/_doc/10/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":["", ""],
        "address":{"country":"US"}
}


PUT exists_test/_doc/11/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":[null, "lily"],
        "address":{"country":"US"}
}

PUT exists_test/_doc/12/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":[null, null],
        "address":{"country":"US"}
}

PUT exists_test/_doc/13/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":[],
        "address":{"country":"US"}
}

PUT exists_test/_doc/14/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":null,
        "address":{"country":"US"}
}

PUT exists_test/_doc/15/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "address":{"country":"US"}
}


PUT exists_test/_doc/16/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":{}
}


PUT exists_test/_doc/17/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":null
}


PUT exists_test/_doc/18/
{
	    "name":"sam",
        "age":30,
        "man": true,
        "child":["jhon", "lily"]
}



PUT exists_test/_doc/19/
{
	    "name":"sam",
        "age":0,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}


PUT exists_test/_doc/20/
{
	    "name":"-",
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}


PUT exists_test/_doc/21/
{
	    "name":";",
        "age":30,
        "man": true,
        "child":["jhon", "lily"],
        "address":{"country":"US"}
}

三、exists查询测试

对于字符串类型字段，当字段没有出现、字段值为null的情况下，则该字段不存在；字段值为空则计算为字段存在；

POST exists_test/_doc/_search/
{
    "query":{
        "bool":{
            "must_not":{
                "exists":{
                    "field":"name"
                }
            }
        }
    }
}



{
    "took":3,
    "timed_out":false,
    "_shards":{
        "total":5,
        "successful":5,
        "skipped":0,
        "failed":0
    },
    "hits":{
        "total":2,
        "max_score":1,
        "hits":[
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"4",
                "_score":1,
                "_source":{
                    "age":30,
                    "man":true,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":{
                        "country":"US"
                    }
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"3",
                "_score":1,
                "_source":{
                    "name":null,
                    "age":30,
                    "man":true,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":{
                        "country":"US"
                    }
                }
            }
        ]
    }
}

对于数字类型字段，当字段没有出现、字段值为null的情况下，则该字段不存在；

POST exists_test/_doc/_search/
{
    "query":{
        "bool":{
            "must_not":{
                "exists":{
                    "field":"age"
                }
            }
        }
    }
}


{
    "took":1,
    "timed_out":false,
    "_shards":{
        "total":5,
        "successful":5,
        "skipped":0,
        "failed":0
    },
    "hits":{
        "total":2,
        "max_score":1,
        "hits":[
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"5",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":null,
                    "man":true,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":{
                        "country":"US"
                    }
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"6",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "man":true,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":{
                        "country":"US"
                    }
                }
            }
        ]
    }
}

对于布尔类型字段，当字段没有出现、字段值为null的情况下，则该字段不存在；

POST exists_test/_doc/_search/
{
    "query":{
        "bool":{
            "must_not":{
                "exists":{
                    "field":"man"
                }
            }
        }
    }
}


{
    "took":1,
    "timed_out":false,
    "_shards":{
        "total":5,
        "successful":5,
        "skipped":0,
        "failed":0
    },
    "hits":{
        "total":2,
        "max_score":1,
        "hits":[
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"8",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":{
                        "country":"US"
                    }
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"7",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":null,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":{
                        "country":"US"
                    }
                }
            }
        ]
    }
}

对于数组类型字段，只要字段没有出现、字段值为null、字段值为空数组、字段值数组的所有元素都为null，则该字段不存在；

POST exists_test/_doc/_search/
{
    "query":{
        "bool":{
            "must_not":{
                "exists":{
                    "field":"child"
                }
            }
        }
    }
}

{
    "took":1,
    "timed_out":false,
    "_shards":{
        "total":5,
        "successful":5,
        "skipped":,
        "failed":
    },
    "hits":{
        "total":4,
        "max_score":1,
        "hits":[
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"14",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":true,
                    "child":null,
                    "address":{
                        "country":"US"
                    }
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"12",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":true,
                    "child":[
                        null,
                        null
                    ],
                    "address":{
                        "country":"US"
                    }
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"15",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":true,
                    "address":{
                        "country":"US"
                    }
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"13",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":true,
                    "child":[

                    ],
                    "address":{
                        "country":"US"
                    }
                }
            }
        ]
    }
}

对于对象字段，只要字段没有出现、字段值是空对象、字段值为null，则该字段不存在；

POST exists_test/_doc/_search/
{
    "query":{
        "bool":{
            "must_not":{
                "exists":{
                    "field":"address"
                }
            }
        }
    }
}



{
    "took":40,
    "timed_out":false,
    "_shards":{
        "total":5,
        "successful":5,
        "skipped":0,
        "failed":0
    },
    "hits":{
        "total":3,
        "max_score":1,
        "hits":[
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"16",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":true,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":{

                    }
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"18",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":true,
                    "child":[
                        "jhon",
                        "lily"
                    ]
                }
            },
            {
                "_index":"exists_test",
                "_type":"_doc",
                "_id":"17",
                "_score":1,
                "_source":{
                    "name":"sam",
                    "age":30,
                    "man":true,
                    "child":[
                        "jhon",
                        "lily"
                    ],
                    "address":null
                }
            }
        ]
    }
}

四、测试总结

elasticsearch对于各种类型字段判断字段是否存在的判断条件如下
1.对于字符串类型字段，当字段没有出现、字段值为null的情况下，则该字段不存在；字段值为空则计算为字段存在；
2.对于数字类型字段，当字段没有出现、字段值为null的情况下，则该字段不存在；
3.对于布尔类型字段，当字段没有出现、字段值为null的情况下，则该字段不存在；
4.对于数组类型字段，只要字段没有出现、字段值为null、字段值为空数组、字段值数组的所有元素都为null，则该字段不存在；
5.对于对象字段，只要字段没有出现、字段值是空对象、字段值为null，则该字段不存在；

分享好友

分享这个小栈给你的朋友们，一起进步吧。

数据库开发

创建时间：2020-06-17 14:33:07

数据库开发是数据库管理系统(DBMS)和数据库应用软件设计研发的总称，主要是数据运维、参与数据库生产环境的问题优化和解决等方面的事宜

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多

小雨滴
专家