# Search combined/JOIN indexes

**URL:** <https://discuss.elastic.co/t/search-combined-join-indexes/318907>\
**Category:** Elasticsearch\
**Created:** [November 14, 2022, 10:26pm UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907 "2022-11-14T22:26:14Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Johanna12221](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@Johanna12221](https://discuss.elastic.co/u/Johanna12221)\
**Post date:** [November 14, 2022, 10:26pm UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907/1 "2022-11-14T22:26:14Z")

</div>

Hello,  
We are working on setting up a search with document contents (pdf/docx etc.) where permissions comes from a database.

I've gotten all the data into Elastic with the help of Logstash and FSCrawler.  
However they are two different indexes and I'm out of ideas how to "merge" them. From someone who comes from doing lots of SQL, I would use JOIN on those indexes to a new view to search on content (from the index created by FSCrawler) with the permissions coming from SQL server Logstash.

The Logstash index has a column "filename" which matches "path.virtual" from FSCrawler.

This is how I search a document contents:

```auto
GET /view-doc/_search
{
    "query": {
        "query_string" : {
            "query" : "Exam test",
            "default_field": "content"
        }
    }
}

```

This is how I search the index containing the permission array:

```auto
GET /db-view/_search
{
  "query": {
    "bool" : {
      "must": [
        {
          "query_string": {
            "query": "search term db view"
          }
        }
      ],
      "should" : [
        { "term" : { "role_ids": "305" } },
        { "term" : { "role_ids" : "306" } }
      ],
      "minimum_should_match" : 1,
      "boost" : 1.0
    }
  }
}

```

How can I query both indexes having them joined on the filename column, and filtered on the permissions array?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [November 14, 2022, 10:29pm UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907/2 "2022-11-14T22:29:41Z")

</div>

TLDR you cannot do this with a single query.

The better approach would be to reindex the data and merge it so that each doc contents also have all the permissions attached to it.

---

<div class="post-metadata">

**Author:** ![Johanna12221](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@Johanna12221](https://discuss.elastic.co/u/Johanna12221)\
**Post date:** [November 14, 2022, 10:37pm UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907/3 "2022-11-14T22:37:48Z")

</div>

Thanks for your fast reply! I've tried to look into how to do that as well and read about "denormalization".  
What could be a good approach for this with the current setup of Logstash with FSCrawler?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [November 15, 2022, 12:12am UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907/4 "2022-11-15T00:12:14Z")

</div>

You could store the permissions data like you have, then ingest the documents via fscrawler and an enrich policy - [Create enrich policy API | Elasticsearch Guide [8.5] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/put-enrich-policy-api.html)

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 16, 2022, 12:39pm UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907/5 "2022-11-16T12:39:59Z")

</div>

It looks like a great idea as you can [define an ingest pipeline in FScrawler](https://fscrawler.readthedocs.io/en/latest/admin/fs/elasticsearch.html#using-ingest-node-pipeline).

@Johanna12221 If you make it work, could you update this thread please and ideally send a PR to the FSCrawler project so this trick is documented?

This should go in [Tips and tricks — FSCrawler 2.10-SNAPSHOT documentation](https://fscrawler.readthedocs.io/en/latest/user/tips.html)

---

<div class="post-metadata">

**Author:** ![Johanna12221](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@Johanna12221](https://discuss.elastic.co/u/Johanna12221)\
**Post date:** [November 17, 2022, 9:19am UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907/6 "2022-11-17T09:19:15Z")

</div>

Thanks for your reply, if I manage to solve it I will post the solution, but I'm stuck on that FSCrawler and Logstash stores the data differently. I made another post about it: [Logstash escaping characters, want to disable - Elastic Stack / Logstash - Discuss the Elastic Stack](https://discuss.elastic.co/t/logstash-escaping-characters-want-to-disable/318984/2)

The result is I can't match with the enrich/ingest/pipeline.  
This is the difference:  
Logstash JDBC plugin "path": "\"\\publicerat\\IN0010.pdf\"",  
FSCrawler: "virtual": """\publicerat\IN0010.pdf""",

---

<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:** [December 15, 2022, 9:19am UTC](https://discuss.elastic.co/t/search-combined-join-indexes/318907/7 "2022-12-15T09:19:18Z")

</div>

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