Kibana Query DSL: How to apply a regexp filter on a text value field which contains JSON syntax

Question

I am having access to data of an elasticsearch instance using Kibana.

Within the data there is a text field which contains a string.

This string (Textfield) looks like JSON but it is not.

Here comes the difference (PLEASE FOCUS ON THE "message" key in the JSON):

If you expand the document to see the "expanded document" view with the little "opened folder" icon you can choose two tabs "Table" and "JSON".

Select "JSON".

Then you would see the follwing:

{
    "_index": "kibana_sample_data",
    "_type": "_doc",
    "_id":"someString",
    "_version": 1,
    "_score": null,
    "_source":{
        "first_key":"some string",
        "message": 
        "{
          \"length\": 11.99,
          \"percentage\": 0,
          \"name\": \"foo\",
          \"category\": \"bar\",
          \"sub_msg\": \"M001: This is a message-code.\"
        }"
    }                
}

As you can see the message-key value which should be JSON is just a string and all key and value informations are escaped (except for number values).

What I should see to select it easily using the filter of the GUI is a JSON like this:

{
    "_index": "kibana_sample_data",
    "_type": "_doc",
    "_id":"someString",
    "_version": 1,
    "_score": null,
    "_source":{
        "first_key":"some string",
        "message": 
        {
          "length": 11.99,
          "percentage": 0,
          "name": "foo",
          "category": "bar",
          "sub_msg": "M001: This is a message-code"
        }
    }                
}

What I am trying to do now is to extract the value of the key "sub_msg" using Query DSL and a regex. But I am not even able to find all matches with the correct beginning character of the message-key value (string).

In more detail the Elastic Search Query DSL part:

{
  "query": {
    "regexp": {
      "message": "{.*"
    }
  }
}

But the search does not match anything.

What it does match is if I try to search for the keys in the message like this:

{
  "query": {
    "regexp": {
      "message": "sub_msg"
    }
  }
}

What I can not do is to search for a respective value within the message like this:

{
  "query": {
    "regexp": {
      "message": "M001: This is a message-code"
    }
  }
}

My actual Goal is to query for the log's message code like "M001:.*" within the value of the "sub_msg"-key. With those query-results I would like to create a simple dashboard.

Unfortunately I feel I am at a dead end right now.

I also tried stuff like escaping the escape characters of the value by adding some "\" to the actual backslashes within the Query DSL. This throws an exception in Kibana.

Joe - GMapsBook.com · Answer 1 · 2020-07-14T18:33:40.247

The issue is that your json-like string was stripped of special chars as it was indexed, presumably due to the default standard analyzer which is applied on text fields unless you explicitly provide another analyzer. More info here and here.

You can check what becomes of your message after the standard analyzer using this:

GET _analyze
{
  "text": "{\"length\": 11.99, \"percentage\": 0, \"name\": \"foo\", \"category\": \"bar\",\"sub_msg\": \"M001: This is a message-code.\"}",
  "analyzer": "standard"
}

Quick fix:

GET kibana_sample_data/_search
{
  "query": {
    "regexp": {
      "message.keyword": "{.*"
    }
  }
}

or

{
  "query": {
    "regexp": {
      "message.keyword": ".*\"M001: This is a message-code.\".*"
    }
  }
}

Long-term fix: figure out a way to ingest the json as it should be ingested w/o having to go to the extreme of regexing a json-like string.

Kibana Query DSL: How to apply a regexp filter on a text value field which contains JSON syntax

1 Answers1