ES|QL metadata fields

ES|QL can access metadata fields.

To access these fields, use the METADATA directive with the FROM source command. For example:

FROM index METADATA _index, _id

The following fields identify the source that produced each row:

The following fields describe index and document metadata:

• Most index metadata fields are available only for index sources. When a field is unavailable for a given source, its value is null. For source-specific behavior, refer to views, subqueries, and datasets.
• The _source type is not supported by functions
• Only the FROM command supports the METADATA directive
• Once enabled, metadata fields work like regular index fields

Once enabled, metadata fields are available to subsequent processing commands, just like other index fields:

FROM ul_logs, apps METADATA _index, _version
| WHERE id IN (13, 14) AND _version == 1
| EVAL key = CONCAT(_index, "_", TO_STR(id))
| SORT id, _index
| KEEP id, _index, _version, key

Similar to index fields, once an aggregation is performed, a metadata field will no longer be accessible to subsequent commands, unless used as a grouping field:

FROM employees METADATA _index, _id
| STATS max = MAX(emp_no) BY _index

FROM products METADATA _score
| WHERE MATCH(description, "wireless headphones")
| SORT _score DESC
| KEEP name, description, _score

A subquery or view returns null for _score when its output does not contain that field. This example combines rows from a subquery over languages with rows read directly from the same index:

FROM (FROM languages), languages METADATA _score
| WHERE MATCH(language_name, "English") OR MATCH(language_name, "French")
| EVAL has_score = _score > 0.0
| SORT has_score DESC NULLS LAST, language_code
| KEEP language_code, language_name, has_score

The WHERE filter applies to both sources. The index rows return a score from MATCH. The subquery rows return null, because the subquery does not produce _score. The example only checks that each score is positive, because the score values depend on how many shards the index has. A descending sort places null values first by default, so use DESC NULLS LAST to keep rows without scores after scored rows, as shown in the example.

Using _source is useful when you want to retrieve most or all fields from a document.

You should consider retrieving _source instead of individual fields when:

• You need several fields from a document, and most of them are text fields.
• You have long text fields or geoshape fields in your documents.
• You want the original document instead of the indexed values for your fields.
• You have nested objects or arrays that you want to preserve in their original structure.

You might want to avoid retrieving _source when:

• Your fields are stored as doc_values. doc_values access is faster than _source.
• Your index uses synthetic source. Accessing _source in synthetic source mode has a performance penalty.
• The text fields you need are stored fields.

Using _source or selecting fields are both valid options, but can have performance and data format implications that you should consider based on your use case.