---
title: ES|QL and flattened fields
description: How ES|QL queries flattened fields and extracts sub-fields using FIELD_EXTRACT.
url: https://www.elastic.co/elastic/docs-builder/docs/3888/reference/query-languages/esql/esql-flattened-fields
products:
  - Elasticsearch
applies_to:
  - Elastic Cloud Serverless: Preview
  - Elastic Stack: Preview since 9.5
---

# ES|QL and flattened fields
ES|QL can read [`flattened`](https://www.elastic.co/elastic/docs-builder/docs/3888/reference/elasticsearch/mapping-reference/flattened) fields directly
and extract their sub-fields using the [`FIELD_EXTRACT`](https://www.elastic.co/elastic/docs-builder/docs/3888/reference/query-languages/esql/functions-operators/string-functions/field_extract) function.
A `flattened` field maps an entire object as a single field, indexing all leaf values as [`keywords`](https://www.elastic.co/elastic/docs-builder/docs/3888/reference/elasticsearch/mapping-reference/keyword).
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](https://github.com/elastic/elasticsearch/issues/152537).
</note>


## Extract a sub-field

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:
```esql
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.

## Key behaviors

- [Flattened fields only contain keywords](#esql-flattened-fields-keywords): `FIELD_EXTRACT` always returns `keyword` values, so numbers and booleans come back as their string representation.
- [Keys are always in the collapsed dotted form](#esql-flattened-fields-dotted-keys): nested objects are stored as dotted keys, and `FIELD_EXTRACT` resolves a key the same way regardless of how the document was written.
- [Extracting an object returns null](#esql-flattened-fields-object-null): pointing `FIELD_EXTRACT` at an object instead of a leaf returns `null`.
- Missing keys and JSON `null` return `null`: `FIELD_EXTRACT` returns `null` if the key does not exist, if the stored value is JSON `null`, or if either argument is `null`.


## Flattened fields only contain keywords

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:
```json

{
  "mappings": {
    "properties": {
      "attrs": { "type": "flattened" }
    }
  }
}

{
  "attrs": { "b": false, "d": 1.2, "l": 123 } <1>
}

{
  "query": """
FROM flattened-cast-demo
| EVAL b = FIELD_EXTRACT(attrs, "b")::BOOLEAN, <2>
       d = FIELD_EXTRACT(attrs, "d")::DOUBLE,
       l = FIELD_EXTRACT(attrs, "l")::LONG
"""
}
```

This query returns the following:
```json
{
  "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]
  ]
}
```


## Keys are always in the collapsed dotted form

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:
```json

{
  "mappings": {
    "properties": {
      "name":  { "type": "keyword" },
      "attrs": { "type": "flattened" }
    }
  }
}


{ "name": "nested", "attrs": { "a": { "b": "something" } } } <1>


{
  "query": "FROM flattened-keys-demo"
}
```

This query returns the following:
```json
{
  "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" } }`:
```json

{ "name": "pre-dotted","attrs": { "a.b": "something" } } <1>


{
  "query": """
    FROM flattened-keys-demo
    | EVAL ab = FIELD_EXTRACT(attrs, "a.b") <2>
    | SORT name ASC
  """
}
```

This query returns the following:
```json
{
  "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"]
  ]
}
```


## Extracting an object with FIELD_EXTRACT returns NULL

`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:
```json

{
  "mappings": {
    "properties": {
      "attrs": { "type": "flattened" }
    }
  }
}


{ "attrs": { "a": { "b": "something" } } } <1>


{
  "query": """
FROM flattened-object-demo
| EVAL a = FIELD_EXTRACT(attrs, "a") <2>
"""
}
```

This query returns the following, with `a` set to `null` because `a` is an object:
```json
{
  "columns": [
    {"name": "attrs", "type": "flattened"},
    {"name": "a",     "type": "keyword"}
  ],
  "values": [
    [{"a.b": "something"}, null]
  ]
}
```


## Related resources

- [`FIELD_EXTRACT`](https://www.elastic.co/elastic/docs-builder/docs/3888/reference/query-languages/esql/functions-operators/string-functions/field_extract): the function reference, including null handling and JSONPath restrictions.
- [`flattened` field type](https://www.elastic.co/elastic/docs-builder/docs/3888/reference/elasticsearch/mapping-reference/flattened): mapping parameters, typed sub-fields with `properties`, and `passthrough`.
- [Supported field types](/elastic/docs-builder/docs/3888/reference/query-languages/esql/limitations#esql-supported-types): the full list of field types ES|QL can read.
- [Multi-value functions](https://www.elastic.co/elastic/docs-builder/docs/3888/reference/query-languages/esql/functions-operators/mv-functions): functions for working with multi-valued columns.