Kibana search nested array of json objects

[shrikant0013 (Shrikant Pandhare)]

I have documents indexed in ElasticSearch like
doc1

{
   "_index":"logs_2017-07-22",
   "_type":"audit",
   "_id":"K",
   "_score":null,
   "_source":{
      "logName":"",
      "payload":{
         "methodName":"policy",
         "response":{
            "sdata":{
               "pDelta":{
                  "bDeltas":[
                     {
                        "action":"Remove",
                        "member":"user:abc",
                        "role":"viewer"
                     },
                     {
                        "action":"Remove",
                        "member":"user:xyz",
                        "role":"admin"
                     },
                     {
                        "action":"Add",
                        "member":"user:pqr",
                        "role":"deployer"
                     },
                     {
                        "action":"Remove",
                        "member":"user:jkl",
                        "role":"admin"
                     }
                  ]
               }
            }
         }
      }
   }
}

doc2

{
   "_index":"logs_2017-07-22",
   "_type":"audit",
   "_id":"K",
   "_score":null,
   "_source":{
      "logName":"",
      "payload":{
         "methodName":"policy",
         "response":{
            "sdata":{
               "pDelta":{
                  "bDeltas":[
                     {
                        "action":"Remove",
                        "member":"user:abc",
                        "role":"viewer"
                     },
                     {
                        "action":"Add",
                        "member":"user:xyz",
                        "role":"admin"
                     },
                     {
                        "action":"Add",
                        "member":"user:pqr",
                        "role":"deployer"
                     },
                     {
                        "action":"Remove",
                        "member":"user:jkl",
                        "role":"admin"
                     }
                  ]
               }
            }
         }
      }
   }
}

How can I get documents which only has

            {
              "action": "Remove",
              "member": "user:xyz",
              "role": "admin"
            }

I tried this query in Kibana, but I get both the documents. I am expecting only first document
payload.sdata.pDelta.bDeltas.action:Remove AND payload.sdata.pDelta.bDeltas.member:"user:xyz"

[tsullivan (Tim Sullivan)]

Hi, it's quite hard to give advice on this since the blob of data you pasted isn't valid JSON. Feel free to add an update that has valid JSON and is formatted with the markup tools available in this forum.

Based on the topic of your question, it looks like you are dealing with the fact that Elasticsearch flattens the arrays in your data objects, which is explained here: https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html

Using a nested data type might give you better searching ability, as it would get Elasticsearch to index the arrays independently of each other, but you should be aware that Kibana doesn't have support to do any aggregations on data that has nested type.

[shrikant0013 (Shrikant Pandhare)]

Thanks for the comment @tsullivan. You are right, it was issue of flattening the arrays in data objects.
I decided to flatten it within my service which processes the json before sending to ElasticSearch.

Btw, about formatting, sorry about that, I have fixed it. I tried earlier and the default editor option '</>' menu button is not working. I noticed today that it also supports markdown, hence was able to use it.

[tsullivan (Tim Sullivan)]

Interesting to know about the menu button not working. I usually wrap my code sections in triple backticks (```) Anyway, the above code is readable now

Glad your searches working now!