Elasticsearch aggs filter with condition

Elastic Stack Elasticsearch
1

Elasticsearch v 7.5

Hello and good day!

I have an index named articles , and has the following sample contents:

{
    "_id" : 1,
    "title" : "Title 1"
    "cms" : [
        {
          "cli_id" : 0,
          "cmx_sentiment" : "Negative"
        },
        {
          "cli_id" : 4599,
          "cmx_sentiment" : "Positive"
        }
    ]
},
{
    "_id" : 2,
    "title" : "Title 2"
    "cms" : [
        {
          "cli_id" : 0,
          "cmx_sentiment" : "Positive"
        }
    ]
},
{
    "_id" : 3,
    "title" : "Title 3"
    "cms" : [
        {
          "cli_id" : 0,
          "cmx_sentiment" : "Neutral"
        }
    ]
},
{
    "_id" : 4,
    "title" : "Title 4"
    "cms" : [
        {
          "cli_id" : 0,
          "cmx_sentiment" : "Positive"
        },
        {
          "cli_id" : 12,
          "cmx_sentiment" : "Negative"
        }
    ]
}

I want to create an ES query with aggs to display the cms.cmx_sentiment for a report, but with a condition

I have this skeleton query:

GET socialmedia/_search
{
  "size" : 1,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "CMS": {
      "nested": {
        "path": "cms"
      }
    }
  }
}

this is the output:

"aggregations" : {
    "CMS" : {
      "doc_count" : 6
    }
  }

doc_count is 6 because

  • article 1 has 2 cms nested elements
  • article 2 has 1 nested element
  • article 3 has 1 nested element and
  • article 4 has 2 nested elements

Now, I want to display the cms.cmx_sentiment on a filter basis to produce a report BUT I must apply a condition

IMPROVING THE QUERY ABOVE

GET socialmedia/_search
{
  "size" : 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "CMS": {
      "nested": {
        "path": "cms"
      },
      "aggs": {
        "FILTER": {
          "filter": {
            "bool": {
              "must" : [
                {
                  "match" : {
                    "cms.cli_id" : 0 <--- I need a query to make this dynamic
                  }
                }
              ]
            }
          },
          "aggs": {
            "SENTIMENT": {
              "terms": {
                "field" : "cms.cmx_sentiment"
              }
            }
          }
        }
      }
    }
  }
}

OUTPUT

"aggregations" : {
  "CMS" : {
    "doc_count" : 6,
    "FILTER" : {
      "doc_count" : 1,
      "SENTIMENT" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : "Positive",
            "doc_count" : 2
          },
          {
            "key" : "Negative",
            "doc_count" : 1
          },
          {
            "key" : "Neutral",
            "doc_count" : 1
          }
        ]
      }
    }
  }
}

OBJECTIVE

Is there a way for me to perform an ES script with this PHP logic?

  • if the cli_id of the logged-in user is in_array of the cms.cli_id nested field, fetch its corresponding cmx_sentiment
  • if the cli_id of the logged-in user IS NOT in the nested elements of cms , fetch the elements with cli_id : 0 , then get its cmx_sentiment

USE CASES

with the given examples above of the articles index

IF cli_id 4599 logs in to the app

"SENTIMENT" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : "Positive",
      "doc_count" : 3
    },
    {
      "key" : "Neutral",
      "doc_count" : 1
    }
  ]
}

IF cli_id 12 logs in to the app

"SENTIMENT" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : "Negative",
      "doc_count" : 2
    },
    {
      "key" : "Positive",
      "doc_count" : 1
    },
    {
      "key" : "Neutral",
      "doc_count" : 1
    }
  ]
}

IF any cli_id logs in to the app

"SENTIMENT" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : "Positive",
      "doc_count" : 2
    },
    {
      "key" : "Negative",
      "doc_count" : 1
    },
    {
      "key" : "Neutral",
      "doc_count" : 1
    }
  ]
}

How can improve my query so I could place a conditional statement in the FILTER aggs of my query?

2

Any ideas about this, ES admins and devs?

3

up, anyone please?

4

Anyone from the respected devs / admins please?

5

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