The semantics of Elasticsearch’s nested datatype and query operator
Tags: elasticsearch, sql, semantics
Published: 2018-12-31 21:18:47

---
title: The semantics of Elasticsearch’s nested datatype and query operator
published: true
description: 
tags: elasticsearch,sql,semantics
---

The basic question is whether predicates in a [`nested` query operator](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html) considers one document at a time (it should according to the docs) and more importantly whether multiple conjunctive (AND) or disjunctive (OR) operations are evaluated and composed on independent records.

Here is the mapping we will test. Let’s assume the index is called `shapes`. We are only concerned about the points that make up the shape so other properties are not included in this example.

```json
{
  "mappings": {
    "_doc": {
      "properties": {
        "points": {
          "type": "nested",
          "properties": {
            "x": {
              "type": "integer"
            },
            "y": {
              "type": "integer"
            }
          }
        }
      }
    }
  }
}
```

Here are a few shape records we will use to test.

```json
{"points": [
  {"x": 1, "y": 1},
  {"x": 1, "y": 2},
  {"x": 2, "y": 1},
  {"x": 2, "y": 2}
]}
{"points": [
  {"x": 1, "y": 1},
  {"x": 1, "y": 2}
]}
{"points": [
  {"x": 1, "y": 1},
  {"x": 1, "y": 2},
  {"x": 2, "y": 1}
]}
```

To re-iterate, the question is whether `nested` queries (which `points` is a [nested datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) in this case) considers *all* sub-records in an array.

To get started, consider the following query:

```json
{
  "query": {
    "nested": {
      "path": "points",
      "query": {
        "bool": {
          "must": [
            {"term": {"points.x": 2}}
          ]
        }
      }
    }
  }
}
```

This translates to "find me all the shapes having at least one point where `x = 2`". In the records above, the second shape record does not have a point where `x = 2` and Elasticsearch (ES) correctly excludes this from the result.

The second test added a requirement that the point must *also* have `y = 2` rather than any value.

```json
{
  "query": {
    "nested": {
      "path": "points",
      "query": {
        "bool": {
          "must": [
            {"term": {"points.x": 2}},
            {"term": {"points.y": 2}}
          ]
        }
      }
    }
  }
}
```

The first shape satisfies this requirement and ES correctly returns only this record.

Now we will try a seemingly, subtly different query. The `must` terms will be put in separate `nested` expressions.

```json
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "points",
            "query": {
              "term": {"points.x": 2}
            }
          }
        },
        {
          "nested": {
            "path": "points",
            "query": {
              "term": {"points.y": 2}
            }
          }
        }        
      ]
    }
  }
}
```

The distinction here is that each record is evaluated against each `nested` operand *independently* whereas the above query required a single point to satisfy both conditions.

We can further confirm this *independent* behavior through the following query which requests shapes "having a point `x = 1, y = 1` and at least one point where `x = 2`, but not the point `x = 2, y = 2`".

```json
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "points",
            "query": {
              "bool": {
                "must": [
                  {"term": {"points.x": 1}},
                  {"term": {"points.y": 1}}
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "points",
            "query": {
              "bool": {
                "must": [
                  {"term": {"points.x": 2}}
                ]
              }
            }
          }
        }
      ],
      "must_not": {
        "nested": {
          "path": "points",
          "query": {
            "bool": {
              "must": [
                {"term": {"points.x": 2}},
                {"term": {"points.y": 2}}
              ]
            }
          }
        }
      }
    }
  }
}
```

Note the last two `nested` operands overlap in that both will match on points where `x = 2`. All three shapes satisfy the first `must` condition, but only shapes 1 and 3 satisfy the second `must` condition. The `must_not` condition excludes shape 1 since it contains the point `x = 2, y = 2`. Therefore the ES result only contains shape 3.

## Comparison with SQL

For those familiar with relational databases and how these kinds of queries are expressed in SQL, conditioning on more than one record of a one-to-many relationship is not intuitive at first, but is easy to learn and becomes obvious.

If we were to model this data in a relational form, we would have the following tables:

```sql
create table "shapes" (
  id integer primary key
);

create table "points" (
  shape_id integer references "shapes" ("id"),
  x integer,
  y integer
);
```

Since a shape can have many points, it needs to be modeled as a relation that references the shape record the point pertains to (this could be further normalized to not duplicate any unique point, but this optimization is not important to this post).

The insert statements are as follows which utilizes Postgres' multi-row `VALUES` syntax.

```sql
insert into "shapes" (id) values
  (1), (2), (3);

insert into "points" (shape_id, x, y) values
  (1, 1, 1), (1, 1, 2), (1, 2, 1), (1, 2, 2),
  (2, 1, 1), (2, 1, 2),
  (3, 1, 1), (3, 1, 2), (3, 2, 1);
```

If we were to express the first ES query above in SQL, we would write:

```sql
select
  shapes.id
from
  shapes
  inner join points
    on shapes.id = points.shape_id
where
  points.x = 2
```

This would return shapes 1 and 3 as ES did

The second query is equally simple by adding the condition on `y` for the same point.

```sql
select
  shapes.id
from
  shapes
  inner join points
    on shapes.id = points.shape_id
where
  points.x = 2 and
  points.y = 2
```

What about the third query? As a reminder, we split these two conditions into separate ES `nested` queries which, based on the documented semantics, should operate independently across records. Modeling this in SQL requires expressing independent relations between shapes and points.

```sql
select
  shapes.id
from
  shapes
  inner join points p1
    on shapes.id = p1.shape_id
  inner join points p2
    on shapes.id = p2.shape_id
where
  p1.x = 2 and
  p2.y = 2
```

The difference here is that each condition (in the `where` clause) applies to a different `points` relation and thus does not require a single record in either relation to satisfy both conditions, but rather at least one record in both relations to satisfy one of the conditions.

What about the third query?

```sql
select
  shapes.id
from
  shapes
  inner join points p1
    on shapes.id = p1.shape_id
  inner join points p2
    on shapes.id = p2.shape_id
  inner join points p3
    on shapes.id = p3.shape_id
where
  p1.x = 1 and p1.y = 1 and
  p2.y = 2 and
  not (p3.x = 2 and p3.y = 2)
```

Another join is made to the `points` table yielding a new relation, but again this means that any conditions on this relation apply independently from the other joined relations. In this case, the third relation asserts that a shape does *not* contain a point `x = 2, y = 2`.

Given this comparison we can assert that conditions in a `nested` operand operate over the collection of records independently from other `nested` operands and thus are semantically equivalent to independent joins as represented in the SQL statement above.