---
title: ES|QL multivalued fields
description: ES|QL can read from multivalued fields: Multivalued fields come back as a JSON array: The relative order of values in a multivalued field is undefined...
url: https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-multivalued-fields
products:
  - Elasticsearch
applies_to:
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available
---

# ES|QL multivalued fields
ES|QL can read from multivalued fields:

```json

{ "index" : {} }
{ "a": 1, "b": [2, 1] }
{ "index" : {} }
{ "a": 2, "b": 3 }


{
  "query": "FROM mv | LIMIT 2"
}
```

Multivalued fields come back as a JSON array:
```json
{
  "took": 28,
  "is_partial": false,
  "columns": [
    { "name": "a", "type": "long"},
    { "name": "b", "type": "long"}
  ],
  "values": [
    [1, [1, 2]],
    [2,      3]
  ]
}
```

The relative order of values in a multivalued field is undefined. They’ll frequently be in ascending order but don’t rely on that.

## Duplicate values

Some field types, like [`keyword`](/elastic/docs-builder/docs/3028/reference/elasticsearch/mapping-reference/keyword#keyword-field-type) remove duplicate values on write:

```json

{
  "mappings": {
    "properties": {
      "b": {"type": "keyword"}
    }
  }
}


{ "index" : {} }
{ "a": 1, "b": ["foo", "foo", "bar"] }
{ "index" : {} }
{ "a": 2, "b": ["bar", "bar"] }


{
  "query": "FROM mv | LIMIT 2"
}
```

And ES|QL sees that removal:
```json
{
  "took": 28,
  "is_partial": false,
  "columns": [
    { "name": "a", "type": "long"},
    { "name": "b", "type": "keyword"}
  ],
  "values": [
    [1, ["bar", "foo"]],
    [2,          "bar"]
  ]
}
```

But other types, like `long` don’t remove duplicates.

```json

{
  "mappings": {
    "properties": {
      "b": {"type": "long"}
    }
  }
}


{ "index" : {} }
{ "a": 1, "b": [2, 2, 1] }
{ "index" : {} }
{ "a": 2, "b": [1, 1] }


{
  "query": "FROM mv | LIMIT 2"
}
```

And ES|QL also sees that:
```json
{
  "took": 28,
  "is_partial": false,
  "columns": [
    { "name": "a", "type": "long"},
    { "name": "b", "type": "long"}
  ],
  "values": [
    [1, [1, 2, 2]],
    [2,    [1, 1]]
  ]
}
```

This is all at the storage layer. If you store duplicate `long`s and then convert them to strings the duplicates will stay:

```json

{
  "mappings": {
    "properties": {
      "b": {"type": "long"}
    }
  }
}


{ "index" : {} }
{ "a": 1, "b": [2, 2, 1] }
{ "index" : {} }
{ "a": 2, "b": [1, 1] }


{
  "query": "FROM mv | EVAL b=TO_STRING(b) | LIMIT 2"
}
```

```json
{
  "took": 28,
  "is_partial": false,
  "columns": [
    { "name": "a", "type": "long"},
    { "name": "b", "type": "keyword"}
  ],
  "values": [
    [1, ["1", "2", "2"]],
    [2,      ["1", "1"]]
  ]
}
```


## `null` in a list

`null` values in a list are not preserved at the storage layer:

```json

{ "a": [2, null, 1] }


{
  "query": "FROM mv | LIMIT 1"
}
```

```json
{
  "took": 28,
  "is_partial": false,
  "columns": [
    { "name": "a", "type": "long"}
  ],
  "values": [
    [[1, 2]]
  ]
}
```


## Functions

Unless otherwise documented functions will return `null` when applied to a multivalued field.

```json

{ "index" : {} }
{ "a": 1, "b": [2, 1] }
{ "index" : {} }
{ "a": 2, "b": 3 }
```

```json

{
  "query": "FROM mv | EVAL b + 2, a + b | LIMIT 4"
}
```

```json
{
  "took": 28,
  "is_partial": false,
  "columns": [
    { "name": "a",   "type": "long"},
    { "name": "b",   "type": "long"},
    { "name": "b + 2", "type": "long"},
    { "name": "a + b", "type": "long"}
  ],
  "values": [
    [1, [1, 2], null, null],
    [2,      3,    5,    5]
  ]
}
```

Work around this limitation by converting the field to single value with one of:
- [`MV_AVG`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions/mv_avg)
- [`MV_CONCAT`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions/mv_concat)
- [`MV_COUNT`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions/mv_count)
- [`MV_MAX`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions/mv_max)
- [`MV_MEDIAN`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions/mv_median)
- [`MV_MIN`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions/mv_min)
- [`MV_SUM`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions/mv_sum)

To filter on individual values in a multivalued field (for example, to keep only rows where the field contains a given value), use the [`MV_EXPAND`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/mv_expand) command to expand the field into one row per value, then apply `WHERE` to the expanded column.
```json

{
  "query": "FROM mv | EVAL b=MV_MIN(b) | EVAL b + 2, a + b | LIMIT 4"
}
```

```json
{
  "took": 28,
  "is_partial": false,
  "columns": [
    { "name": "a",   "type": "long"},
    { "name": "b",   "type": "long"},
    { "name": "b + 2", "type": "long"},
    { "name": "a + b", "type": "long"}
  ],
  "values": [
    [1, 1, 3, 2],
    [2, 3, 5, 5]
  ]
}
```


## Filter pushdown may miss warnings

Touching a multivalued field with a function that only supports single valued
fields will normally produce a warning that looks like
`evaluation of [b > 1] failed, treating result as null`. You'll get it
consistently for queries like this:
```json

{
  "query": "FROM mv | EVAL gt_1 = b > 1 | LIMIT 4"
}
```

When a filter can be evaluated using the search index, ES|QL might miss the
warning for documents that contain a multivalued field where **none** of the values
match the filter. For example:
```json

{ "index" : {} }
{ "a": 1, "b": [2, 1] }
{ "index" : {} }
{ "a": 2, "b": 3 }


{
  "query": "FROM mv | WHERE b > 2 | LIMIT 4"
}
```