I've found documentation on joins and documentation on subqueries, but I'm not sure how to combine them.
I want to search for something by id (this is a variable that's passed in). That result will have a sectionpath. I then want to find all the documents that start with that sectionpath. Can I do this in one query?
Here's what I've tried so far:
sectionpath:_query_:"{!fl=sectionpath}id:<variable>"*
I know this doesn't work, but here's the intent of this: give me results where sectionpath starts with (that's what the * is for) the sectionpath of a document that has the id of <variable>. I know this is far from working, but I'm not sure how to get close to my intent.
I've also tried something like this:
{!join from=sectionpath to=sectionpath}_query_:"{!fl=sectionfullpath}id:<variable>"
But this seems to be returning close to everything. It doesn't seem to be restricting by sectionpath.
There are multiple phases to achieve desired result but in short; yes it's possible to use a field's value in a single query.
We are going to use [subquery]* transformer and after that {!prefix}* query parser. Also, we will use a field value as input in subquery.
First, query the documents whose values you will use in subquery:
q=id:123
or if you will only use id to filter main documents you can use filter query;
q=*:*
fq=id:123
Specify subquery and choose the fields you want to retrieve for main documents:
fl=id,section_path,childpaths:[subquery]
Provide query for subquery, also using main document field (in raw query params):
childpaths.q={!term f=section_path v=$row.section_path}
Here we are using !term query parser to indicate the field we want to search on. Also by using v we are referencing an auto variable named $row to get the main document and its section_path value.
Remember, this will construct the query like section_path:xyz but we want to get documents whose section_path values start with main records'. Basically we want to construct a query like section_path:xy*. Luckily we have a !prefix query parser for that. So change the childpaths.q to;
childpaths.q={!prefix f=name_en v=$row.name_en}
Specify child document fields you want to retrieve (also in raw query params):
childpaths.fl=id,section_path
You can also limit child documents to get only 5 rows like so;
childpaths.rows=5
And you can modify other query parameters too, just like a regular query.
Fire the request and voila!
Here is the full query:
q=*:*&fq=id:123&fl=id,section_path,childpaths:[subquery]&childpaths.q={!prefix f=name_en v=$row.name_en}&childpaths.fl=id,section_path