# Does OpenSearch provide the query functionality for join in Solr?

**URL:** <https://forum.opensearch.org/t/does-opensearch-provide-the-query-functionality-for-join-in-solr/17151>\
**Category:** OpenSearch\
**Tags:** feature-request\
**Created:** [December 19, 2023, 6:44am UTC](https://forum.opensearch.org/t/does-opensearch-provide-the-query-functionality-for-join-in-solr/17151 "2023-12-19T06:44:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![xxzure](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/xxzure/32/6668_2.png) [@xxzure](https://forum.opensearch.org/u/xxzure)\
**Post date:** [December 19, 2023, 6:44am UTC](https://forum.opensearch.org/t/does-opensearch-provide-the-query-functionality-for-join-in-solr/17151/1 "2023-12-19T06:44:29Z")

</div>

I am trying to migrate Solr to OpenSearch on AWS, but I have found that OpenSearch does not support join queries like Solr. I would like to inquire whether OpenSearch has similar functionality.

I am trying to use [SQL](https://opensearch.org/docs/latest/search-plugins/sql/sql/complex/) to fix this problem. But it has a lot of limitations. For example, I can use over 2 join queries in Solr, but only 1 subQuery is supported.

Actually, Join parser in Solr is not like Join in SQL. It looks like a filter to filter the result. So I am confused about how to fix this type of query in OpenSearch. They are all based on Lucene.

For example, the Solr query:  
`{!join method='crossCollection' fromIndex=company from=id to=advertiser_id v='assignee:("user_1")'} OR {!join method='crossCollection' fromIndex=company from=id to=agency_id v='assignee:("user_1")'}`  
It is difficult to convert to an OpenSearch query.

Solr Join/CrossCollection Join : [Join Query Parser :: Apache Solr Reference Guide](https://solr.apache.org/guide/solr/latest/query-guide/join-query-parser.html)

---

<div class="post-metadata">

**Author:** ![radu.gheorghe](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/radu.gheorghe/32/3516_2.png) [@radu.gheorghe](https://forum.opensearch.org/u/radu.gheorghe)\
**Post date:** [December 19, 2023, 7:11am UTC](https://forum.opensearch.org/t/does-opensearch-provide-the-query-functionality-for-join-in-solr/17151/2 "2023-12-19T07:11:14Z")

</div>

Unfortunately, there’s no equivalent of CrossCollection join in OpenSearch. I can think of two alternatives that are somewhat close:

- parent-child queries, but this requires parents and children to be in the same index (they’ll be routed to the same shards, actually)
- application-side joins, where you query the children, get the list of IDs, then query the parents with a `terms` query containing children IDs. If you have a few thousand matching IDs at most, it should work decently well