Relational Querying of the Data
We will now show you how to execute a relational query across the two indices. For example, we would like to retrieve all the articles that mention companies whose name matches orient
. This relational query can be decomposed in two search queries: the first one to find all the companies whose name matches orient
, and a second query to filter out all articles that do not mention a company from the first result set. The Siren Federate plugin introduces a new Elasticsearch’s filter, named join
, that allows to define such a query plan and a new search API siren/<index>/_search
that allows to execute this query plan. Below is the command to run the relational query:
$ curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/articles/_search?pretty' -d '{ "query" : { "join" : { 1 "indices" : ["companies"], 2 "on" : ["mentions", "id"], 3 "request" : { 4 "query" : { "term" : { "name" : "orient" } } } } } }'
The | |
The source indices (i.e., | |
The clause specifying the paths for join keys in both source and target indices | |
The search request that will be used to filter out companies |
The command should return you the following response with two search hits:
{ "hits" : { "total" : 2, "max_score" : 1.0, "hits" : [ { "_index" : "articles", "_type" : "article", "_id" : "1", "_score" : 1.0, "_source":{ "title" : "The NoSQL database glut", "mentions" : ["1", "2"] } }, { "_index" : "articles", "_type" : "article", "_id" : "3", "_score" : 1.0, "_source":{ "title" : "How to determine which NoSQL DBMS best fits your needs", "mentions" : ["2", "4"] } } ] } }
You can also reverse the order of the join, and query for all the companies that are mentioned in articles whose title matches nosql
:
$ curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/companies/_search?pretty' -d '{ "query" : { "join" : { "indices" : ["articles"], "on": ["id", "mentions"], "request" : { "query" : { "term" : { "title" : "nosql" } } } } } }'
The command should return you the following response with three search hits:
{ "hits" : { "total" : 3, "max_score" : 1.0, "hits" : [ { "_index" : "companies", "_type" : "company", "_id" : "4", "_score" : 1.0, "_source":{ "id": "4", "name" : "MapR" } }, { "_index" : "companies", "_type" : "company", "_id" : "1", "_score" : 1.0, "_source":{ "id": "1", "name" : "Elastic" } }, { "_index" : "companies", "_type" : "company", "_id" : "2", "_score" : 1.0, "_source":{ "id": "2", "name" : "Orient Technologies" } } ] } }