Does OpenSearch provide the query functionality for join in Solr?

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 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

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