Loading

ES|QL multivalued fields

ES|QL is fine reading from multivalued fields:

 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, "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.

Some field types, like keyword remove duplicate values on write:

 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, "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.

 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, "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 longs and then convert them to strings the duplicates will stay:

 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, "is_partial": false, "columns": [ { "name": "a", "type": "long"}, { "name": "b", "type": "keyword"} ], "values": [ [1, ["1", "2", "2"]], [2, ["1", "1"]] ] }

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

 POST /mv/_doc?refresh { "a": [2, null, 1] } POST /_query { "query": "FROM mv | LIMIT 1" } 
{ "took": 28, "is_partial": false, "columns": [ { "name": "a", "type": "long"}, ], "values": [ [[1, 2]], ] }

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

 POST /mv/_bulk?refresh { "index" : {} } { "a": 1, "b": [2, 1] } { "index" : {} } { "a": 2, "b": 3 } 
 POST /_query { "query": "FROM mv | EVAL b + 2, a + b | LIMIT 4" } 
{ "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:

 POST /_query { "query": "FROM mv | EVAL b=MV_MIN(b) | EVAL b + 2, a + b | LIMIT 4" } 
{ "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] ] }