Alex Nikitin
Reputation: 931
Find null nested filed documents in ElascicSearch
i would like to find documents that have preices.info filed equals to null. Prices is a nested filed The example of query looks like this
{
"query": {
"bool": {
"must": [
{
"term": {
"doc_type": "supnom"
}
},
{
"term": {
"is_linking_disabled": false
}
},
{
"has_parent": {
"parent_type": "sup",
"query": {
"bool": {
"must": [
{
"term": {
"id": 89634082
}
}
]
}
}
}
},
{
"bool": {
"should": [
{
"bool": {
"must": [
{
"term": {
"folder_type": 0
}
},
{
"term": {
"folder": "NULL"
}
},
{
"term": {
"is_folder": true
}
},
{
"term": {
"is_folder_hybrid": true
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"folder_type": 1
}
},
{
"term": {
"folder": "89634082-NULL"
}
},
{
"term": {
"is_folder": true
}
},
{
"term": {
"is_folder_hybrid": true
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"is_folder": false
}
},
{
"term": {
"folder_hybrid": "NULL"
}
},
{
"nested": {
"path": "prices",
"query": {
"terms": {
"prices.id_prcknd": [
22215
]
}
}
}
}
]
}
},
{
"bool": {
"must_not": [
{
"nested": {
"path": "prices",
"query": {
"bool": {
"filter": {
"exists": {
"field": "prices.info"
}
}
}
}
}
}
]
}
}
]
}
}
],
"must_not": [],
"should": []
}
},
"size": 70,
"from": 0,
"_source": true,
"sort": [
{
"is_folder": {
"order": "desc"
}
},
{
"title_low.order": {
"order": "asc"
}
}
]
}
And anyway i get in result documents with NOT null prices.info field
{
"took": 65,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 7,
"max_score": null,
"hits": [
{
"_index": "supnoms",
"_type": "doc",
"_id": "supnom-108340976",
"_score": null,
"_routing": "1",
"_source": {
"codes": null,
"folder": null,
"folder_hybrid": null,
"folder_hybrid_type": 0,
"folder_type": null,
"id": "108340976",
"id_supplier": 89634082,
"is_folder": false,
"is_folder_hybrid": null,
"is_linking_disabled": false,
"manufacturer": {
"id": null,
"title": null
},
"market_nom": {
"folder": null,
"id": null
},
"path_market": {
"parents": null,
"pids": null,
"strpath": null
},
"path_supnom": {
"parents": null,
"pids": null,
"strpath": null
},
"prices": [
{
"currency": "RUR",
"id_offer": 605966502,
"id_prcdoc": 42040,
"id_prcknd": 22215,
"info": {
"Price": "По запросу"
},
"min_order": null,
"sell_by": null,
"value": "По запросу"
}
],
"sup_code": "c39f81c9-1a69-3a7d-9e04-5f4e124242e7",
"title": "баклажан UPC",
"title_low": "баклажан upc",
"_join_field_name": "doc_type",
"doc_type": {
"name": "supnom",
"parent": "sup-89634082"
}
},
"sort": [
0,
"баклажан upc"
]
},
{
"_index": "supnoms",
"_type": "doc",
"_id": "supnom-108340975",
"_score": null,
"_routing": "1",
"_source": {
"codes": null,
"folder": null,
"folder_hybrid": null,
"folder_hybrid_type": 0,
"folder_type": null,
"id": "108340975",
"id_supplier": 89634082,
"is_folder": false,
"is_folder_hybrid": null,
"is_linking_disabled": false,
"manufacturer": {
"id": null,
"title": null
},
"market_nom": {
"folder": null,
"id": null
},
"path_market": {
"parents": null,
"pids": null,
"strpath": null
},
"path_supnom": {
"parents": null,
"pids": null,
"strpath": null
},
"prices": [
{
"currency": "RUR",
"id_offer": 605966498,
"id_prcdoc": 42040,
"id_prcknd": 22215,
"info": {},
"min_order": null,
"sell_by": null,
"value": "10.00"
}
],
"sup_code": "8be655dc-0873-3dfc-994b-602b2e4e0799",
"title": "Избранные для дубля без цены",
"title_low": "избранные для дубля без цены",
"_join_field_name": "doc_type",
"doc_type": {
"name": "supnom",
"parent": "sup-89634082"
}
},
"sort": [
0,
"избранные для дубля без цены"
]
},
{
"_index": "supnoms",
"_type": "doc",
"_id": "supnom-108340974",
"_score": null,
"_routing": "1",
"_source": {
"codes": null,
"folder": null,
"folder_hybrid": null,
"folder_hybrid_type": 0,
"folder_type": null,
"id": "108340974",
"id_supplier": 89634082,
"is_folder": false,
"is_folder_hybrid": null,
"is_linking_disabled": false,
"manufacturer": {
"id": null,
"title": null
},
"market_nom": {
"folder": null,
"id": null
},
"path_market": {
"parents": null,
"pids": null,
"strpath": null
},
"path_supnom": {
"parents": null,
"pids": null,
"strpath": null
},
"prices": [
{
"currency": "RUR",
"id_offer": 605966500,
"id_prcdoc": 42040,
"id_prcknd": 22215,
"info": {},
"min_order": null,
"sell_by": null,
"value": "10.00"
}
],
"sup_code": "867f138d-d8cc-37e7-b1b2-f07a4627ba49",
"title": "Избранные для дубля с ценой",
"title_low": "избранные для дубля с ценой",
"_join_field_name": "doc_type",
"doc_type": {
"name": "supnom",
"parent": "sup-89634082"
}
}
price field in index looks like this "prices": { "type": "nested", "properties": { "currency": { "type": "keyword" }, "id_offer": { "type": "integer" }, "id_prcdoc": { "type": "integer" }, "id_prcknd": { "type": "integer" }, "min_order": { "type": "keyword" }, "sell_by": { "type": "keyword" }, "value": { "type": "keyword", "index": false } } }
Upvotes: 0
Views: 53
Answers (1)
Kamal Kunjapur
Reputation: 8840
Looking from the query that you have, you want to view all documents which does not have prices.info value.
Just move the must_not section you have to the first level in the bool query you have. Please see the below for more clarity:
Original Query:
{
"query": {
"bool": {
"must": [
{ term }
{ term }
{ hasparent }
{ bool
- should
- must
- must
- must
- must_not <---- Remove this
- { exists } <---- Copy this
}
],
"must_not": [], <---- And add that here
"should": [
{}
]
}
}
}
Psuedo Code Solution:
{
"query": {
"bool": {
"must": [
{ term }
{ term }
{ hasparent }
{ bool
- should
- must
- must
- must
}
],
"must_not": [
{ exists } <--- Note this
],
"should": [
{}
]
}
}
}
Query DSL
Below is the query that you are looking for:
{
"query":{
"bool":{
"must":[
{
"term":{
"doc_type":"supnom"
}
},
{
"term":{
"is_linking_disabled":false
}
},
{
"has_parent":{
"parent_type":"sup",
"query":{
"bool":{
"must":[
{
"term":{
"id":89634082
}
}
]
}
}
}
},
{
"bool":{
"should":[
{
"bool":{
"must":[
{
"term":{
"folder_type":0
}
},
{
"term":{
"folder":"NULL"
}
},
{
"term":{
"is_folder":true
}
},
{
"term":{
"is_folder_hybrid":true
}
}
]
}
},
{
"bool":{
"must":[
{
"term":{
"folder_type":1
}
},
{
"term":{
"folder":"89634082-NULL"
}
},
{
"term":{
"is_folder":true
}
},
{
"term":{
"is_folder_hybrid":true
}
}
]
}
},
{
"bool":{
"must":[
{
"term":{
"is_folder":false
}
},
{
"term":{
"folder_hybrid":"NULL"
}
},
{
"nested":{
"path":"prices",
"query":{
"terms":{
"prices.id_prcknd":[
22215
]
}
}
}
}
]
}
}
]
}
}
],
"must_not":[
{
"nested":{
"path":"prices",
"query":{
"exists":{
"field":"prices.info"
}
}
}
}
],
"should":[
]
}
},
"size":70,
"from":0,
"_source":true,
"sort":[
{
"is_folder":{
"order":"desc"
}
},
{
"title_low.order":{
"order":"asc"
}
}
]
}
Note that you should not be seeing any docs which has prices.info field. Also note that I've simplified the query to keep it simple.
Let me know if this works!
Upvotes: 1
Related Questions
- Return Documents with Null Field in Multi Level Aggregation
- find documents with empty field / Attachment.Content in elastic search using Nest + C#
- Find documents with empty string value on elasticsearch
- Query elastic search documents, where there is null value for property in nested object
- How to ignore the nested objects that have null value or don't exist
- search document with null/empty object field in elasticsearch
- Nest Elasticsearch search for null value
- Elasticsearch 5.x find documents where nested object does not exist
- How to get documents with non-null field in Elastic search?
- ElasticSearch NEST Checking for null value