Cross Document search and sort using Search API

Question

Using Search API, i have to join and search two XML documents in 2 different directories on a key element and sort the result based on a element in the 2nd XML.

I have defined the search options in XML with constraints on elements that I want to search and use sort operator options to define the sort order.

ML Version is 8 (so no Optic API) and don't have an option to denormalize the XML during ingestion as it is and existing system.

Sample file A

URI: /employee/123.xml

<employee>
    <empid>123</empid>
    <firstname>John</firstname>
    <lastname>Peter</lastname>
    <dept>sales</sales>
</employee>

Sample file B

URI: /contrib/123.xml

<contribution>
    <empid>123</empid>
    <count>2000</count>
<contribution>

search:search(searchgrmr,searchoptions,startidx,endidx);

I want to achieve something like what we achieve in the sql below

select * from a join b on a.col1 = b.col1 order by b.col2

Could you spell out the code a bit more? What search would you like to execute exactly, and could you align the sql to match the same names? I might have a suggestion, but it helps to get this cleared up. — grtjn

wst wst · Accepted Answer · 2019-04-30T14:59:00

Search API is a little too coarse-grained to do a join like that easily. I would recommend drilling down a level and use cts:search to perform a "shotgun" query, which is very similar to how a relational database join works.

Take a look at this section in the indexing documentation:

In XQuery code, get a set of ids matching the first query, then feed that set into a second query as a constraint. The ability to use range indexes on both ends makes the work efficient.

Cross Document search and sort using Search API

Sample file A

Sample file B

1 Answers