solr join use case (not in instead of in)

Question

Solr has implemented from version 4 the !join query.

I'd like to know if the following case is possible.
For example, we have documents of the following form:
doc1:
    field1:123
    field2:A
    field3:456

doc2:
    field1:123
    field2:B
    field3:789

doc3:
    field1:23456
    field2:A
    field3:264

We need to retrieve all the documents that the field field2 equals to B and no other document with the same field field1 value containing the field field2 with value A.

In SQL this can be done with the operation "not in":
select * from doc where field2 = 'B' and field1 not in (select field1 from doc where field2 = 'A')

The join operator is the equivalent of the SQL in operator.

Can we use the solr join or another function to implements our needs?

Thanks

Answer 1

You need to use nested query for this purposes. Here's same question and answer for it:

https://stackoverflow.com/a/27191274/4304116

Here is query for your example:

q=-_query_:"{!join from=doc_id to=id}field2:'A'"

It will return all docs that not with "A" value for field2.

Also you can just add to q another condition(if you need only "B" value):

q={!join from=doc_id to=id}field2:'B'"

It will return all docs with 'B' value for field2.