› › ›

ES|QL multivalued fields

ES|QL is fine reading from multivalued fields:

resp = client.bulk(
    index="mv",
    refresh=True,
    operations=[
        {
            "index": {}
        },
        {
            "a": 1,
            "b": [
                2,
                1
            ]
        },
        {
            "index": {}
        },
        {
            "a": 2,
            "b": 3
        }
    ],
)
print(resp)

resp1 = client.esql.query(
    query="FROM mv | LIMIT 2",
)
print(resp1)

const response = await client.bulk({
  index: "mv",
  refresh: "true",
  operations: [
    {
      index: {},
    },
    {
      a: 1,
      b: [2, 1],
    },
    {
      index: {},
    },
    {
      a: 2,
      b: 3,
    },
  ],
});
console.log(response);

const response1 = await client.esql.query({
  query: "FROM mv | LIMIT 2",
});
console.log(response1);

POST /mv/_bulk?refresh
{ "index" : {} }
{ "a": 1, "b": [2, 1] }
{ "index" : {} }
{ "a": 2, "b": 3 }

POST /_query
{
  "query": "FROM mv | LIMIT 2"
}

Multivalued fields come back as a JSON array:

{
  "took": 28,
  "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 remove duplicate values on write:

resp = client.indices.create(
    index="mv",
    mappings={
        "properties": {
            "b": {
                "type": "keyword"
            }
        }
    },
)
print(resp)

resp1 = client.bulk(
    index="mv",
    refresh=True,
    operations=[
        {
            "index": {}
        },
        {
            "a": 1,
            "b": [
                "foo",
                "foo",
                "bar"
            ]
        },
        {
            "index": {}
        },
        {
            "a": 2,
            "b": [
                "bar",
                "bar"
            ]
        }
    ],
)
print(resp1)

resp2 = client.esql.query(
    query="FROM mv | LIMIT 2",
)
print(resp2)

const response = await client.indices.create({
  index: "mv",
  mappings: {
    properties: {
      b: {
        type: "keyword",
      },
    },
  },
});
console.log(response);

const response1 = await client.bulk({
  index: "mv",
  refresh: "true",
  operations: [
    {
      index: {},
    },
    {
      a: 1,
      b: ["foo", "foo", "bar"],
    },
    {
      index: {},
    },
    {
      a: 2,
      b: ["bar", "bar"],
    },
  ],
});
console.log(response1);

const response2 = await client.esql.query({
  query: "FROM mv | LIMIT 2",
});
console.log(response2);

PUT /mv
{
  "mappings": {
    "properties": {
      "b": {"type": "keyword"}
    }
  }
}

POST /mv/_bulk?refresh
{ "index" : {} }
{ "a": 1, "b": ["foo", "foo", "bar"] }
{ "index" : {} }
{ "a": 2, "b": ["bar", "bar"] }

POST /_query
{
  "query": "FROM mv | LIMIT 2"
}

And ES|QL sees that removal:

{
  "took": 28,
  "columns": [
    { "name": "a", "type": "long"},
    { "name": "b", "type": "keyword"}
  ],
  "values": [
    [1, ["bar", "foo"]],
    [2,          "bar"]
  ]
}

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

resp = client.indices.create(
    index="mv",
    mappings={
        "properties": {
            "b": {
                "type": "long"
            }
        }
    },
)
print(resp)

resp1 = client.bulk(
    index="mv",
    refresh=True,
    operations=[
        {
            "index": {}
        },
        {
            "a": 1,
            "b": [
                2,
                2,
                1
            ]
        },
        {
            "index": {}
        },
        {
            "a": 2,
            "b": [
                1,
                1
            ]
        }
    ],
)
print(resp1)

resp2 = client.esql.query(
    query="FROM mv | LIMIT 2",
)
print(resp2)

const response = await client.indices.create({
  index: "mv",
  mappings: {
    properties: {
      b: {
        type: "long",
      },
    },
  },
});
console.log(response);

const response1 = await client.bulk({
  index: "mv",
  refresh: "true",
  operations: [
    {
      index: {},
    },
    {
      a: 1,
      b: [2, 2, 1],
    },
    {
      index: {},
    },
    {
      a: 2,
      b: [1, 1],
    },
  ],
});
console.log(response1);

const response2 = await client.esql.query({
  query: "FROM mv | LIMIT 2",
});
console.log(response2);

PUT /mv
{
  "mappings": {
    "properties": {
      "b": {"type": "long"}
    }
  }
}

POST /mv/_bulk?refresh
{ "index" : {} }
{ "a": 1, "b": [2, 2, 1] }
{ "index" : {} }
{ "a": 2, "b": [1, 1] }

POST /_query
{
  "query": "FROM mv | LIMIT 2"
}

And ES|QL also sees that:

{
  "took": 28,
  "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:

resp = client.indices.create(
    index="mv",
    mappings={
        "properties": {
            "b": {
                "type": "long"
            }
        }
    },
)
print(resp)

resp1 = client.bulk(
    index="mv",
    refresh=True,
    operations=[
        {
            "index": {}
        },
        {
            "a": 1,
            "b": [
                2,
                2,
                1
            ]
        },
        {
            "index": {}
        },
        {
            "a": 2,
            "b": [
                1,
                1
            ]
        }
    ],
)
print(resp1)

resp2 = client.esql.query(
    query="FROM mv | EVAL b=TO_STRING(b) | LIMIT 2",
)
print(resp2)

const response = await client.indices.create({
  index: "mv",
  mappings: {
    properties: {
      b: {
        type: "long",
      },
    },
  },
});
console.log(response);

const response1 = await client.bulk({
  index: "mv",
  refresh: "true",
  operations: [
    {
      index: {},
    },
    {
      a: 1,
      b: [2, 2, 1],
    },
    {
      index: {},
    },
    {
      a: 2,
      b: [1, 1],
    },
  ],
});
console.log(response1);

const response2 = await client.esql.query({
  query: "FROM mv | EVAL b=TO_STRING(b) | LIMIT 2",
});
console.log(response2);

PUT /mv
{
  "mappings": {
    "properties": {
      "b": {"type": "long"}
    }
  }
}

POST /mv/_bulk?refresh
{ "index" : {} }
{ "a": 1, "b": [2, 2, 1] }
{ "index" : {} }
{ "a": 2, "b": [1, 1] }

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

{
  "took": 28,
  "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:

resp = client.index(
    index="mv",
    refresh=True,
    document={
        "a": [
            2,
            None,
            1
        ]
    },
)
print(resp)

resp1 = client.esql.query(
    query="FROM mv | LIMIT 1",
)
print(resp1)

const response = await client.index({
  index: "mv",
  refresh: "true",
  document: {
    a: [2, null, 1],
  },
});
console.log(response);

const response1 = await client.esql.query({
  query: "FROM mv | LIMIT 1",
});
console.log(response1);

POST /mv/_doc?refresh
{ "a": [2, null, 1] }

POST /_query
{
  "query": "FROM mv | LIMIT 1"
}

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

Functions

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

resp = client.bulk(
    index="mv",
    refresh=True,
    operations=[
        {
            "index": {}
        },
        {
            "a": 1,
            "b": [
                2,
                1
            ]
        },
        {
            "index": {}
        },
        {
            "a": 2,
            "b": 3
        }
    ],
)
print(resp)

response = client.bulk(
  index: 'mv',
  refresh: true,
  body: [
    {
      index: {}
    },
    {
      a: 1,
      b: [
        2,
        1
      ]
    },
    {
      index: {}
    },
    {
      a: 2,
      b: 3
    }
  ]
)
puts response

const response = await client.bulk({
  index: "mv",
  refresh: "true",
  operations: [
    {
      index: {},
    },
    {
      a: 1,
      b: [2, 1],
    },
    {
      index: {},
    },
    {
      a: 2,
      b: 3,
    },
  ],
});
console.log(response);

POST /mv/_bulk?refresh
{ "index" : {} }
{ "a": 1, "b": [2, 1] }
{ "index" : {} }
{ "a": 2, "b": 3 }

resp = client.esql.query(
    query="FROM mv | EVAL b + 2, a + b | LIMIT 4",
)
print(resp)

const response = await client.esql.query({
  query: "FROM mv | EVAL b + 2, a + b | LIMIT 4",
});
console.log(response);

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

{
  "took": 28,
  "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:

resp = client.esql.query(
    query="FROM mv | EVAL b=MV_MIN(b) | EVAL b + 2, a + b | LIMIT 4",
)
print(resp)

const response = await client.esql.query({
  query: "FROM mv | EVAL b=MV_MIN(b) | EVAL b + 2, a + b | LIMIT 4",
});
console.log(response);

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

{
  "took": 28,
  "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]
  ]
}

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/8.17/esql-multivalued-fields.html

ES|QL multivalued fields

Duplicate values

null in a list

Functions

`null` in a list