This documentation contains work-in-progress information for future Elastic Stack and Cloud releases. Use the version selector to view supported release docs. It also contains some Elastic Cloud serverless information. Check out our serverless docs for more details.
« ES|QL metadata fields Data processing with DISSECT and GROK »
Elastic Docs ›Elasticsearch Guide [8.x] ›ES|QL ›ES|QL reference

ES|QL multivalued fields

edit

ES|QL multivalued fields

edit

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

edit

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

edit

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

edit

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]
  ]
}
« ES|QL metadata fields Data processing with DISSECT and GROK »