# How to "join" two different types of documents on the closest value of a common integer key

**URL:** <https://discuss.elastic.co/t/how-to-join-two-different-types-of-documents-on-the-closest-value-of-a-common-integer-key/333226>\
**Category:** Elasticsearch\
**Created:** [May 11, 2023, 4:16pm UTC](https://discuss.elastic.co/t/how-to-join-two-different-types-of-documents-on-the-closest-value-of-a-common-integer-key/333226 "2023-05-11T16:16:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mathemaphysics](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathemaphysics/32/120925_2.png) [@Mathemaphysics](https://discuss.elastic.co/u/Mathemaphysics)\
**Post date:** [May 11, 2023, 4:16pm UTC](https://discuss.elastic.co/t/how-to-join-two-different-types-of-documents-on-the-closest-value-of-a-common-integer-key/333226/1 "2023-05-11T16:16:34Z")

</div>

I have a problem. I've inherited legacy code for which ELK stack is now being used to capture and detect problems. Logstash + Filebeat are being used and an index template is being used to correctly map WGS84 points.

I have data that looks like this:

```json
[
    "survey_item": {
        "geo": "POINT (-87.382741 38.411667)",
        "pos_index": 17382
    },
    "survey_item": {
        "geo": "POINT (-87.382738 38.411678)"
        "pos_index": 17470
    },
    ...
]

```

```json
[
    "traj_pos": {
        "pos_index": 17488,
        "density": 0.38215
    },
    "traj_pos": {
        "pos_index": 17468,
        "density": 0.97231
    },
    ...
]

```

I have been trying to come up with a way to "join" `traj_pos` documents with `survey_item` documents on the `pos_index` field within Elasticsearch so I can plot them on a map as the field `survey_item.geo` colored according to `traj_pos.density`.

But as you can see from my sample data, any given `pos_index` value may not exist in both `survey_item` and `traj_pos` documents. So I'm looking to do a join to the _closest value_ of `pos_index`. Is this possible via a query (in some language)?

I know this can be done with some python. I know how to do it. But I want the pipeline from Filebeat to Logstash to Elasticsearch to Kibana to stand alone without requiring manipulation if at all possible.

Alternatively, I could do some Logstash manipulation with the ruby filter to get what I want, but I'd rather find a way to do this that doesn't require setting the number of Logstash workers to 1 and forcing serial execution.

---

<div class="post-metadata">

**Author:** ![Mathemaphysics](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathemaphysics/32/120925_2.png) [@Mathemaphysics](https://discuss.elastic.co/u/Mathemaphysics)\
**Post date:** [May 26, 2023, 5:51pm UTC](https://discuss.elastic.co/t/how-to-join-two-different-types-of-documents-on-the-closest-value-of-a-common-integer-key/333226/2 "2023-05-26T17:51:14Z")

</div>

I'm getting the feeling there's no way to do this with Elasticsearch.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 26, 2023, 6:15pm UTC](https://discuss.elastic.co/t/how-to-join-two-different-types-of-documents-on-the-closest-value-of-a-common-integer-key/333226/3 "2023-05-26T18:15:53Z")

</div>

The lack of responses likely indicate that is the case. I can personally not think of any way to do it.

---

<div class="post-metadata">

**Author:** ![Mathemaphysics](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathemaphysics/32/120925_2.png) [@Mathemaphysics](https://discuss.elastic.co/u/Mathemaphysics)\
**Post date:** [June 6, 2023, 9:27pm UTC](https://discuss.elastic.co/t/how-to-join-two-different-types-of-documents-on-the-closest-value-of-a-common-integer-key/333226/4 "2023-06-06T21:27:32Z")

</div>

Wouldn't an enrich pipeline work for this? This is almost exactly what I need for this, but there's no way to force the pipeline query to use the custom score I'm using because I don't know how to reference the search term within the script or pass it via `params`.

See the "???" in the script part below. I'm guessing access to the actual `match_field` value is hidden from this enrich/ingest pipeline interface.

The query works to find the points I need, and I'm using the ES Rust client library implementation to make this "join". I just wonder if I could do this entirely within the ingest pipeline mechanism.

```auto
PUT /_enrich/policy/add-geodata-policy
{
  "match": {
    "indices": "survey-data-XXXX-XX-XX",
    "match_field": "position_index",
    "enrich_fields": [
      "geo_point"
    ],
    "query": {
      "script_score": {
        "query": {
          "match_all": {}
        }
      },
      "script": {
        "source": "decayNumericLinear(???, params.scale, params.offset, params.decay, doc['position_index'].value)",
        "params": {
          "bucket_pos": 120004,
          "scale": 1,
          "offset": 0,
          "decay": 0.5
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 4, 2023, 9:28pm UTC](https://discuss.elastic.co/t/how-to-join-two-different-types-of-documents-on-the-closest-value-of-a-common-integer-key/333226/5 "2023-07-04T21:28:26Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.