Querying across a composite join of multiple database tables using a search engine index

A method performed by a system comprising a storage apparatus and a processing apparatus having one or more computer processors, the method comprising:

• storing, on the storage apparatus, searchable content for a plurality of records, wherein each record is associated with a unique identifier, with a table containing the record, and with a join key based upon which the record can be associated with other records having an identical join key;
• the processing apparatus receiving a composite join query comprising:
• a specification of a user query comprising one or more unitary queries and one or more Boolean operations applied to the one or more unitary queries,
• a specification of a plurality of join tables, and
• a specification of a join field;
• for each of the unitary queries, the processing apparatus:
• executing the unitary query against the searchable content,
• filtering results of the execution for records contained in any of the plurality of join tables,
• identifying join keys from the join field that correspond to the filtered results, and
• aggregating the identified join keys in a set of matching join keys on the join field;
• for each of the Boolean operations, the processing apparatus applying the Boolean operation according to the user query to one or more sets of matching join keys, wherein the one or more sets of matching join keys are obtained from executed unitary queries, from other applied Boolean operations or from both, to create a set of matching join keys, until all of the one or more Boolean operations have been applied;
• and
• the processing apparatus storing the set of matching join keys created from an application of a last one of the one or more Boolean operations.

A system comprising:

• a processing apparatus comprising one or more computer processors;
• a storage apparatus comprising computer memory and storing:
• a search engine index including searchable content for a plurality of documents, wherein each document is associated in the index with a unique identifier, with a table containing the document, and with a join key based upon which the document can be associated with other documents having an identical join key;
• and
• a search engine operating on the one or more processors, wherein the search engine is configured to:
• receive a composite join query comprising:
• a specification of a user query comprising one or more unitary queries and one or more Boolean operations applied to the one or more unitary queries,
• a specification of a plurality of join tables, and
• a specification of a join field;
• for each of the unitary queries,
• execute the unitary query against the search engine index using the search engine,
• filter results of the execution of the unitary query for documents contained in any of the plurality of join tables,
• identify join keys from the join field that correspond to the filtered results, and
• aggregate the identified join keys in a set of matching join keys;
• for each of the Boolean operations, apply the Boolean operation according to the user query to one or more sets of matching join keys, wherein the one or more sets of matching join keys are obtained from executed unitary queries, from other applied Boolean operations or from both, to create a set of matching join keys, until all of the one or more Boolean operations have been applied;
• and
• store the set of matching join keys created from an application of a last one of the one or more Boolean operations.

A set of one or more non-transitory computer-readable media storing instructions that when executed by a processing apparatus comprising one or more computer processors cause the processing apparatus to:

• store on a storage apparatus comprising computer memory:
• a search engine index including searchable content for a plurality of documents, wherein each document is associated with a unique identifier, with a table containing the document, and with a join key based upon which the document can be associated with other documents having an identical join key;
• operate a search engine to execute queries against the search engine index;
• receive a composite join query comprising:
• a specification of a user query comprising one or more unitary queries and one or more Boolean operations applied to the one or more unitary queries,
• a specification of a plurality of join tables, and
• a specification of a join field;
• for each of the unitary queries:
• execute the unitary query against the search engine index using the search engine,
• filter results of the execution of the unitary query for documents contained in any of the plurality of join tables,
• identify join keys from the join field that correspond to the filtered results, and
• aggregate the identified join keys in a set of matching join keys;
• for each of the Boolean operations, apply the Boolean operation according to the user query to one or more sets of matching join keys, wherein the one or more sets of matching join keys are obtained from executed unitary queries, from other applied Boolean operations or from both, to create a set of matching join keys, until all of the one or more Boolean operations have been applied;
• and
• store the set of matching join keys created from an application of a last one of the one or more Boolean operations.