How to filter null objects?

I have to filter payloads like this on an ElasticSearch query:

{
  "bestPrices": {
    "cia1": {},
    "cia2": {}
  }
}

I must return only results like:

{
  "bestPrices": {
    "cia1": {
      "gol": {
        "price1": 799,
        "price2": null,
        "miles": 25000
      }
    },
    "cia2": {
      "gol": {
        "price1": null,
        "price2": null,
        "miles": null
      }
    }
  }
}

I'm trying exists query, but seems that it do not apply to this particular situation:

{
  "exists": {
    "field": "searchIntention.bestSalePrices.cia1"
  }
}

I'm using ElasticSearch 6.1

This is working well:

DELETE test
PUT test/doc/1
{
  "bestPrices": {
    "cia1": {
      "gol": {
        "price1": 799,
        "price2": null,
        "miles": 25000
      }
    },
    "cia2": {
      "gol": {
        "price1": null,
        "price2": null,
        "miles": null
      }
    }
  }
}
PUT test/doc/2
{
  "bestPrices": {
    "cia2": {
      "gol": {
        "price1": null,
        "price2": null,
        "miles": null
      }
    }
  }
}
GET test/_search
{
  "query": {
    "exists": {
      "field": "bestPrices.cia1"
    }
  }
}
{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "test",
        "_type": "doc",
        "_id": "1",
        "_score": 1,
        "_source": {
          "bestPrices": {
            "cia1": {
              "gol": {
                "price1": 799,
                "price2": null,
                "miles": 25000
              }
            },
            "cia2": {
              "gol": {
                "price1": null,
                "price2": null,
                "miles": null
              }
            }
          }
        }
      }
    ]
  }
}

Thanks @dadoonet! Actually, the test was something like

PUT test/doc/2
    {
      "bestPrices": {
        "cia2": {
          "gol": {}
        }
      }
    }

But works anyway... :slight_smile: Now I have another problem...

My documents may have this mapping:

PUT test/doc/3
{
  "bestPrices": {
    "cia2": {
      "other_name": {
        "price1": 200,
        "price2": 300,
        "miles": null
      }
    }
  }
}

And I must filter prices greater than 0. Something like that, does not work :confused: :

GET test/_search
{
  "query": {
    "range": {
      "bestPrices.cia2.*.price1": {
        "gte": 1
      }
    }
  }
}

Can I filter inner fields like these? Should I open a new topic?

Thanks again :+1:

If possible I'd solve that at index time using copy_to or by computing a sum which you can also do with an ingest pipeline with a script processor.

Yeah... I gonna implement some transformation to aggregate this result before indexing the document. Thanks David :slight_smile:

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.