Thanks, but as far as I understand this is "just" a different, Json-based syntax for the same underlying feature (join queries, no support for parent/child block joins). So alas! my questions remain open. I have been unable so far to find any comprehensive example I could relate to, and I have read the docs and looked hard.
A. On 2021/04/22 20:45:37, Timothy Potter <thelabd...@gmail.com> wrote: > Might take a look at the domain join stuff in JSON facets too: > https://solr.apache.org/guide/8_8/json-faceting-domain-changes.html#join-query-domain-changes > ... I've seen this work where you index a common ID field in each > document (different document types in the same collection each having > a common join key field, in your case the customer ID) > > Tim > > On Thu, Apr 22, 2021 at 1:55 PM Eran Buchnick <buchni...@gmail.com> wrote: > > > > Read this before going deeper > > https://lucidworks.com/post/solr-and-joins/ > > > > Good luck > > > > On Wed, 21 Apr 2021, 13:55 Alain Rogister, <alain.rogis...@gmail.com> wrote: > > > > > Hi, > > > > > > Imagine that you have been tasked with designing a Solr-based system that > > > indexes and queries data aggregated from several relational databases. All > > > the data items are ultimately related to a common object type, so the > > > logical data schema looks like : > > > > > > Customer > > > Profile > > > Events > > > Interactions > > > Steps > > > ActionPlans > > > Objectives > > > Actions > > > (and quite a few more, with up to 3 or 4 levels of nesting) > > > > > > Each of these has a number of attributes. What you need to do is the Solr > > > equivalent of a SQL query that would find all Customers where > > > Profile.item1='...' and there is some Event where event.item1='..' and > > > event.item2='..' and there is some ActionPlan that has an Objective where > > > Objective.item3='..' and this Objective has an Action where > > > Action.item1='..' and Action.item2='..' > > > > > > This is fairly trivial in a relational DB. But is it achievable in Solr ? > > > Needless to say, there is no way a flat schema can work here. So I have > > > assumed that nested documents were the only way to go. I have created a > > > schema that includes these prerequisites, where "docType" is meant to > > > contain the document type i.e. Profile, Event, Interaction etc : > > > > > > <field name="_version_" type="plong" indexed="false" stored="false"/> > > > <field name="_nest_path_" type="_nest_path_" indexed="true" stored="true" > > > docValues="true"/> > > > <fieldType name="_nest_path_" class="solr.NestPathField" /> > > > <field name="_nest_parent_" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <field name="_root_" type="string" indexed="true" stored="true" > > > docValues="true" /> > > > <field name="docType" type="string" indexed="true" stored="true" > > > docValues="true" required="true"/> > > > <field name="id" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <uniqueKey>id</uniqueKey> > > > > > > and I have used naming conventions on the other fields to reflect the > > > structure, e.g. > > > > > > <field name="customerId" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <field name="customerName" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <field name="interaction.code" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <field name="interaction.result" type="string" indexed="true" > > > stored="true" docValues="true"/> > > > <field name="step.code" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <field name="step.result" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <field name="event.type" type="string" indexed="true" stored="true" > > > docValues="true"/> > > > <field name="event.date" type="pdate" indexed="true" stored="true" > > > docValues="true"/> > > > > > > I have been able to index my documents correctly, i.e. retrieving a > > > Customer by id with fl=*,[child limit=999] returns the customer and all > > > dependent objects in the correct hierarchy. > > > > > > I have also been able to express queries on two levels, such as: find all > > > Customers that have an Interaction with code='..' and result='..' and that > > > have an Event with type='..' and date before '...'. The syntax looks like > > > this, using two filter queries and block joins : > > > > > > q= docType:Customer AND (<simple filtering clauses>) > > > fq= {!parent which=docType:Customer} +docType:Interaction > > > +interaction.code:C01 +interaction.result:COMPLETE > > > fq= {!parent which=docType:Customer} +docType:Event +event.type:EVTTYPE1 > > > +event.date:[NOW-1YEAR TO NOW] > > > > > > That seems to return correct results. However, I can't quite figure out > > > the proper way to take it one level deeper e.g. add filtering conditions > > > on > > > the Steps of the Interactions selected by the first filter query. > > > > > > For the record, I am able to do so using streaming expressions, but these > > > bring their own complexities so I am trying to find out first if something > > > simpler can save me. > > > > > > Thanks ! > > > > > > >
