ES|QL and flattened fields

ES|QL can read flattened fields directly and extract their sub-fields using the FIELD_EXTRACT function.

A flattened field maps an entire object as a single field, indexing all leaf values as keywords. It is commonly used for objects with a large or unpredictable set of keys, for example OpenTelemetry resource.attributes, where each service contributes its own set of attributes.

Note

Currently ES|QL can access only the root flattened field directly and you must use FIELD_EXTRACT to get the subfield. This will change in a future release. See elasticsearch/issues/152537.

Use FIELD_EXTRACT to pull a single sub-field out of a flattened root as a keyword column. The function takes the flattened field and the path as its arguments:

FROM flattened_otel_logs
| WHERE @timestamp == "2020-01-01T00:02:48.461Z"
| EVAL host.name = field_extract(resource.attributes, "host.name")
| KEEP @timestamp, host.name
@timestamp:date host.name:keyword
2020-01-01T00:02:48.461Z infra-filebeat-6vjxr

The second argument is the dotted path within the flattened field.

Numbers and booleans come back as their string representation, for example: "184896", "true". Use casts to get other types. Create an index, index a document, and run a query that casts the extracted values:

				PUT /flattened-cast-demo
					{
  "mappings": {
    "properties": {
      "attrs": { "type": "flattened" }
    }
  }
}
				POST /flattened-cast-demo/_doc?refresh
					{
  "attrs": { "b": false, "d": 1.2, "l": 123 }
}
				POST /_query
					{
  "query": """
FROM flattened-cast-demo
| EVAL b = FIELD_EXTRACT(attrs, "b")::BOOLEAN,
       d = FIELD_EXTRACT(attrs, "d")::DOUBLE,
       l = FIELD_EXTRACT(attrs, "l")::LONG
"""
}
  1. The document stores a boolean, a double, and a long.
  2. FIELD_EXTRACT returns each value as a keyword, so cast it to the type you want.

This query returns the following:

{
  "columns": [
    {"name": "attrs", "type": "flattened"},
    {"name": "b",     "type": "boolean"},
    {"name": "d",     "type": "double"},
    {"name": "l",     "type": "long"}
  ],
  "values": [
    [{"b": "false", "d": "1.2", "l": "123"}, false, 1.2, 123]
  ]
}

When you index a flattened field Elasticsearch "flattens" it. {"a": {"b": "v"}} becomes {"a.b": "v"}. When you load the flattened with ES|QL, you get the flattened result:

				PUT /flattened-keys-demo
					{
  "mappings": {
    "properties": {
      "name":  { "type": "keyword" },
      "attrs": { "type": "flattened" }
    }
  }
}
				POST /flattened-keys-demo/_doc?refresh
					{ "name": "nested", "attrs": { "a": { "b": "something" } } }
				POST /_query
					{
  "query": "FROM flattened-keys-demo"
}
  1. The document nests b inside a. Elasticsearch stores it as the dotted key a.b.

This query returns the following:

{
  "columns": [
    {"name": "attrs", "type": "flattened"},
    {"name": "name",  "type": "keyword"}
  ],
  "values": [
    [{"a.b": "something"}, "nested"]
  ]
}

The path parameter of FIELD_EXTRACT operates on exactly that normalized path. So { "a.b": "something" } is the same as { "a": { "b": "something" } }:

				POST /flattened-keys-demo/_doc?refresh
					{ "name": "pre-dotted","attrs": { "a.b": "something" } }
				POST /_query
					{
  "query": """
    FROM flattened-keys-demo
    | EVAL ab = FIELD_EXTRACT(attrs, "a.b")
    | SORT name ASC
  """
}
  1. This document uses a literal dotted key instead of a nested object.
  2. The same path resolves both documents, since they collapse to the same key.

This query returns the following:

{
  "columns": [
    {"name": "attrs", "type": "flattened"},
    {"name": "name",  "type": "keyword"},
    {"name": "ab",    "type": "keyword"}
  ],
  "values": [
    [{"a.b": "something"}, "nested",    "something"],
    [{"a.b": "something"}, "pre-dotted","something"]
  ]
}

FIELD_EXTRACT can only extract leaf fields. If you point it at an object, it returns null. Address the leaf directly. For example, use "http.request.body.size" rather than "http.request". Create an index, index a document whose value is an object, and extract that object key:

				PUT /flattened-object-demo
					{
  "mappings": {
    "properties": {
      "attrs": { "type": "flattened" }
    }
  }
}
				POST /flattened-object-demo/_doc?refresh
					{ "attrs": { "a": { "b": "something" } } }
				POST /_query
					{
  "query": """
FROM flattened-object-demo
| EVAL a = FIELD_EXTRACT(attrs, "a")
"""
}
  1. a holds an object, not a leaf value.
  2. Extracting the object returns null. Extract the leaf a.b instead.

This query returns the following, with a set to null because a is an object:

{
  "columns": [
    {"name": "attrs", "type": "flattened"},
    {"name": "a",     "type": "keyword"}
  ],
  "values": [
    [{"a.b": "something"}, null]
  ]
}

KQL and QSTR require a field that is directly mapped in the index, so they don't accept a FIELD_EXTRACT expression as their argument. Copying the extracted value into a new column with EVAL does not help, because the result is still not an index-mapped field. To filter on an extracted value with those, use ==, LIKE, RLIKE, or WILDCARD instead.

MATCH, MATCH_PHRASE, and the : operator accept a FIELD_EXTRACT expression. There is no indexed field behind the extracted value, so the match is evaluated at runtime, row by row, rather than pushed to the index.

KNN also works on values extracted with FIELD_EXTRACT after you convert them to a dense_vector. Use TO_DENSE_VECTOR to cast a hex-encoded byte vector, or the DENSE_VECTOR command to generate embeddings from the extracted text.

In 9.6.0, passing an inline TO_DENSE_VECTOR conversion as the KNN field argument doesn't return correct results. As a workaround, use EVAL to create the dense_vector column first and pass that column to KNN. For details, refer to known issues.