Query a field in json string

Question

I'm trying to query the field value.value which is a json string from this struct in ElasticSearch.

I wrote this query to narrow down the search:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "valueType": {
              "value": "VARIABLE",
              "boost": 1
        }
      }
    },
    {
      "term": {
        "intent": {
          "value": "CREATED",
          "boost": 1
        }
      }
    },
  
    {
      "term": {
        "recordType": {
          "value":"EVENT",
          "boost": 1
        }
      }
    },
    {
      "term": {
        "position": {
          "value":16185492,
          "boost": 1
        }
      }
    }
  ]
}
  }
  "_source": true
}

But I cannot figure out how to get only record with, as example, ID_TRANSACTION=304 from field value.value. This field is a type keyword. Here the mapping:

Answer 1

Indeed, ideally we should have an ingest pipeline with a json processor for parsing the value.value content into specific fields that you can query.

The problem, however, is that the mapping is defined with dynamic: strict by Zeebe, which means that it won't be possible to add new fields in your document. Also it is not possible to use value.value as an object containing the parsed JSON fields, because value.value is defined as a keyword and that cannot be changed.

The only way to make this happen is to change the record-variable index template present in Zeebe in order to allow new fields to be created.

If you have the rights to do it, or if you know someone who can do it for you, here is how to do it.

First, create an ingest pipeline to parse the JSON content in value.value into separate fields that you can query:

PUT _ingest/pipeline/value-json-pipeline
{
  "processors": [
    {
      "json": {
        "field": "value.value",
        "target_field": "value.valueJson"
      }
    }
  ]
}

Then, modify the zeebe-record-variable-template.json file like this:

{
  "index_patterns": [
    "zeebe-record_variable_*"
  ],
  "composed_of": ["zeebe-record"],
  "priority": 20,
  "version": 1,
  "template": {
    "settings": {
      "number_of_shards": 1,
      "number_of_replicas": 0,
      "index.queries.cache.enabled": false,
      "index.default_pipeline": "value-json-pipeline"   <--- add this new setting
    },
    "aliases": {
      "zeebe-record-variable": {}
    },
    "mappings": {
      "properties": {
        "value": {
          "dynamic": "strict",
          "properties": {
            "name": {
              "type": "keyword"
            },
            "value": {
              "type": "keyword",
              "ignore_above": 8191
            },
            "valueJson": {                             <--- add this new field
              "type": "object"
            },
            "scopeKey": {
              "type": "long"
            },
            "processInstanceKey": {
              "type": "long"
            },
            "processDefinitionKey": {
              "type": "long"
            },
            "bpmnProcessId": {
              "type": "keyword"
            },
            "tenantId": {
              "type": "keyword"
            }
          }
        }
      }
    }
  }
}

When the next zeebe-record_variable_8.2.12_* daily index will be created, it will have the new mapping using the new ingest pipeline that will parse the value.value content and put it in value.valueJson that you will be able to query.