Siren Filter Join Query not working well with ES 2.3.4 and Siren Join Plugin 2.3.4

[hailima]

Hi,

For POC, ES 2.3.4 and Sire Join Plugin 2.3.4 were installed on MacBook Pro (10.9.4) with JVM 1.7. But, the filter join queries do not return the results I expect. The queries and source data are attached. Anything I could miss ? and your help and support is much appreciated.

Thanks,

Harry es_siren_join.zip

[rendel]

Hi @hailima, thanks for reporting this. I'll have to investigate it and will be back to you.

[rendel]

Would it be possible to have the mappings of your index ? or at least for the fields that are used in those queries. Thanks.

[almico]

Hello @hailima, you are joining on an analyzed field that contains the following product ids:

4935e1c3-3c80-476b-a1e3-4e946ee62d90-1 4935e1c3-3c80-476b-a1e3-4e946ee62d90-2

You didn't include any mapping, thus I expect you are using none. I replaced those two ids with two ids that wouldn't be tokenized ("product1" and "product2") and the join works as expected.

[fabiocorneti]

@hailima please have a look at the following script for an example on how the mapping should be set to get the expected result:

#!/bin/bash
ES=http://localhost:9200

{
curl -s -XDELETE $ES/exchangev3index
curl -s -XDELETE $ES/offerv1index

CURL -s XPUT $ES/offerv1index -d '
{
  "mappings": {
    "products": {
      "properties": {
        "cnt:auxiliaryFields": {
          "properties": {
            "productId": {
              "type": "string",
              "index": "not_analyzed"
            }
          }
        }
      }
    }
  }
}
'

CURL -s XPUT $ES/exchangev3index -d '
{
  "mappings": {
    "products": {
      "properties": {
        "schema:productId": {
          "type": "string",
          "index": "not_analyzed"
        }
      }
    }
  }
}
'

curl -s -XPOST $ES/offerv1index/products/4935e1c3-3c80-476b-a1e3-4e946ee62d90-1?pretty -d '{
  "cnt:dateCreated": "2016-08-16T06:29:39.308+0000",
  "schema:title": "Exchange - REVEL AutomationSAMPLE",
  "schema:offerId": "50317670-5efb-4912-ae63-0f03235d4ee5",
  "schema:orgId": "552abf2f30043f62232db54b",
  "schema:offerStartDate": "2015-08-21T18:02:52.249+0000",
  "schema:offerEndDate": "2017-08-21T18:02:52.249+0000",
  "offerStatus": "PENDING",
  "cnt:auxiliaryFields": {
    "dateModified": "2016-08-16T06:29:41.452+0000",
    "description": "Direct Bill for REVEL",
    "areasServed": {
      "countryCode": [
        "US"
      ]
    },
    "offerType": {
      "offerTypeCode": "DIRECT_BILL",
      "definition": {
        "isbn": {
          "hepmSync": true,
          "isbn13": "9780111111001"
        },
        "subscriptionDuration": 3
      }
    },
    "productId": "4935e1c3-3c80-476b-a1e3-4e946ee62d90-1",
    "sourceSystem": "HEPM",
    "migrated": false
  }
}'

curl -s -XPOST $ES/offerv1index/products/4935e1c3-3c80-476b-a1e3-4e946ee62d90-2?pretty -d '{
  "cnt:dateCreated": "2016-08-16T06:29:39.308+0000",
  "schema:title": "Exchange - REVEL AutomationSAMPLE",
  "schema:offerId": "50317670-5efb-4912-ae63-0f03235d4ee5",
  "schema:orgId": "552abf2f30043f62232db54b",
  "schema:offerStartDate": "2015-08-21T18:02:52.249+0000",
  "schema:offerEndDate": "2017-08-21T18:02:52.249+0000",
  "offerStatus": "APPROVED",
  "cnt:auxiliaryFields": {
    "dateModified": "2016-08-16T06:29:41.452+0000",
    "description": "Direct Bill for REVEL",
    "areasServed": {
      "countryCode": [
        "US"
      ]
    },
    "offerType": {
      "offerTypeCode": "DIRECT_BILL",
      "definition": {
        "isbn": {
          "hepmSync": true,
          "isbn13": "9780111111001"
        },
        "subscriptionDuration": 3
      }
    },
    "productId": "4935e1c3-3c80-476b-a1e3-4e946ee62d90-2",
    "sourceSystem": "HEPM",
    "migrated": false
  }
}'

curl -s -XPOST $ES/exchangev3index/products/4935e1c3-3c80-476b-a1e3-4e946ee62d90-1?pretty -d '{
  "cnt:slug": "exchange-revel-automationsample",
  "schema:name": "Exchange - REVEL AutomationSAMPLE",
  "cnt:productType": "SAMPLE",
  "schema:productId": "4935e1c3-3c80-476b-a1e3-4e946ee62d90-1",
  "cnt:productModel": {
    "cnt:name": "REVEL"
  },
  "schema:sourceIsbn": {
    "schema:isbn13": "9780111111112",
    "schema:region": "US"
  },
  "schema:productStage": "ACTIVE",
  "cnt:auxiliaryFields": {
    "dateCreated": "2016-08-16T06:27:11.802+0000",
    "format": "DIGITAL",
    "productPlatform": "REVEL",
    "createdBy": "1fe5ffd9a9694e2ca0db7727717438c6",
    "editable": true,
    "migrated": false
  }
}'

curl -s -XPOST $ES/exchangev3index/products/4935e1c3-3c80-476b-a1e3-4e946ee62d90-2?pretty -d '{
  "cnt:slug": "exchange-revel-automationsample",
  "schema:name": "Exchange - REVEL AutomationSAMPLE",
  "cnt:productType": "SAMPLE",
  "schema:productId": "4935e1c3-3c80-476b-a1e3-4e946ee62d90-2",
  "cnt:productModel": {
    "cnt:name": "REVEL"
  },
  "schema:sourceIsbn": {
    "schema:isbn13": "9780111111112",
    "schema:region": "US"
  },
  "schema:productStage": "ACTIVE",
  "cnt:auxiliaryFields": {
    "dateCreated": "2016-08-16T06:27:11.802+0000",
    "format": "DIGITAL",
    "productPlatform": "REVEL",
    "createdBy": "1fe5ffd9a9694e2ca0db7727717438c6",
    "editable": true,
    "migrated": false
  }
}'

curl -s -XPOST $ES/offerv1index/_refresh
curl -s -XPOST $ES/exchangev3index/_refresh

} > /dev/null

echo "QUERY 1"
curl -XPOST $ES/exchangev3index/products/_coordinate_search?pretty -d '
{
  "query": {
    "filtered": {
      "query": {
        "query_string": {
          "query": "schema\\:name:Revel"
        }
      },
      "filter": {
        "filterjoin": { 
          "schema:productId": {
            "indices": [
              "offerv1index"
            ],
            "types": [
              "products"
            ],
            "path": "cnt:auxiliaryFields.productId",
            "query": {
              "query_string": {
                "query": "offerStatus:PENDING"
              }
            }
          }
        }
      }
    }
  }
}
'

echo "QUERY 2"
curl -XPOST $ES/exchangev3index/products/_coordinate_search?pretty -d '
{
  "query": {
    "filtered": {
      "query": {
        "query_string": {
          "query": "schema\\:name:Revel"
        }
      },
      "filter": {
        "filterjoin": { 
          "schema:productId": {
            "indices": [
              "offerv1index"
            ],
            "types": [
              "products"
            ],
            "path": "cnt:auxiliaryFields.productId",
            "query": {
              "query_string": {
                "query": "offerStatus:APPROVED"
              }
            }
          }
        }
      }
    }
  }
}
'