ElasticSearch: find multiple unique values in array with complex objects

Question

Suppose there is an index with documents following a structure like:

{
    "array": [
        {
            "field1": 1,
            "field2": 2
        },
        {
            "field1": 3,
            "field2": 2
        },
        {
            "field1": 3,
            "field2": 2
        },
        ...
    ]
}

Is it possible to define a query that returns documents having multiple unique values for a field?

For the example above, the query searching on field2 would not return the document because all have the same value, but searching on field1 would return it because it has values 1 and 3.

The only thing I can think of is to store the unique values in the parent object and then query for its length, but, as it seems trivial, I'd hope to solve it without having to change the structure to something like:

{
    "arrayField1Values" : [1, 3],
    "arrayField2Values" : [2]
    "array": [
        {
            "field1": 1,
            "field2": 2
        },
        {
            "field1": 3,
            "field2": 2
        },
        {
            "field1": 3,
            "field2": 2
        },
        ...
    ]
}

Thanks for anybody that can help!

You want to return a field if it value appears only once in a document. So if a field is present in multiple document don't return it or if it is present in all documents then don't return? — jaspreet chahal
– jaspreet chahal, Commented Apr 16, 2020 at 6:55

Jozef - Spatialized.io · Accepted Answer · 2020-04-16 09:55:18Z

My hunch was to go with a nested datatype but then I realized you could do a simple distinct count on the array-values of fields 1 and 2 using query scripts and top_hits:

PUT array

POST array/_doc
{
  "array": [
    {
      "field1": 1,
      "field2": 2
    },
    {
      "field1": 3,
      "field2": 2
    },
    {
      "field1": 3,
      "field2": 2
    }
  ]
}

GET array/_search
{
  "size": 0,
  "aggs": {
    "field1_is_unique": {
      "filter": {
        "script": {
          "script": {
            "source": "def uniques = doc['array.field1'].stream().distinct().sorted().collect(Collectors.toList()); return uniques.length > 1 ;",
            "lang": "painless"
          }
        }
      },
      "aggs": {
        "top_hits_field1": {
          "top_hits": {}
        }
      }
    },
    "field2_is_unique": {
      "filter": {
        "script": {
          "script": {
            "source": "def uniques = doc['array.field2'].stream().distinct().sorted().collect(Collectors.toList()); return uniques.length > 1 ;",
            "lang": "painless"
          }
        }
      },
      "aggs": {
        "top_hits_field2": {
          "top_hits": {}
        }
      }
    }
  }
}

yielding separate aggregations for whether field1 or field2 included unique value counts > 1:

 "aggregations" : {
    "field1_is_unique" : {
      "doc_count" : 1,
      "top_hits_field1" : {
        "hits" : {
          "total" : {
            "value" : 1,
            "relation" : "eq"
          },
          "max_score" : 1.0,
          "hits" : [
            {
              "_index" : "array",
              "_type" : "_doc",
              "_id" : "WbJhgnEBVBaNYdXKNktL",
              "_score" : 1.0,
              "_source" : {
                "array" : [
                  {
                    "field1" : 1,
                    "field2" : 2
                  },
                  {
                    "field1" : 3,
                    "field2" : 2
                  },
                  {
                    "field1" : 3,
                    "field2" : 2
                  }
                ]
              }
            }
          ]
        }
      }
    },
    "field2_is_unique" : {
      "doc_count" : 0,
      "top_hits_field2" : {
        "hits" : {
          "total" : {
            "value" : 0,
            "relation" : "eq"
          },
          "max_score" : null,
          "hits" : [ ]
        }
      }
    }
  }

Hope it helps.

Collectives™ on Stack Overflow

ElasticSearch: find multiple unique values in array with complex objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related