Use HttpURLConnection to send a "GET" command to Elastic Search with a json body (enum "-d" option in curl's GET)

Recently I was working on implementing a java equivalence using HttpURLConnection to the following curl query which sends a "GET" command to elastic search with a json body specified in the "-d" option of curl command, something like the one below:

curl -XGET "http://127.0.0.1:9200/messages/_search?pretty" -d '
{
  "size" : 10,
  "query" : {
    "bool" : {
      "must" : [ {
        "match" : {
          "id" : {
            "query" : "[some id]",
            "type" : "boolean"
          }
        }
      },  {
        "nested" : {
          "query" : {
            "bool" : {
              "must" : {
                "match" : {
                  "agent" : {
                    "query" : "[some agent name]",
                    "type" : "boolean"
                  }
                }
              }
            }
          },
          "path" : "agents"
        }
      } ]
    }
  }
  }
}

The command requires a json body to be sent to the elastic search via the "GET" restful call. After some trial and error, I got this to work, below is the method implemented in Java.

 public static String httpGet(String urlToRead, String data) {
        URL url;
        HttpURLConnection conn;
        BufferedReader rd;
        String line;
        StringBuilder result = new StringBuilder();
        try {
            url = new URL(urlToRead);
            conn = (HttpURLConnection) url.openConnection();
            conn.setDoOutput(true);
            conn.setRequestMethod("GET");
            BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(conn.getOutputStream()));
            writer.write(data);
            writer.flush();

            rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
            while ((line = rd.readLine()) != null) {
                result.append(line);
            }
            rd.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result.toString();
}

To call the above method and realize the curl query to ES above. just implement the following:

 
int size = 10;
String ipAddress = "127.0.0.1";
String url = "http://"+ipAddress+":9200/messages/_search?pretty";
String data = " { \"size\" : "+size+", \"query\" : { \"bool\" : { \"must\" : [ { \"match\" : { \"id\" : { \"query\" : \"[some id]\", \"type\" : \"boolean\" } } }, { \"nested\" : { \"query\" : { \"bool\" : { \"must\" : { \"match\" : { \"agent\" : { \"query\" : \"[some agent name]\", \"type\" : \"boolean\" } } } } }, \"path\" : \"agents\" } } ] } } } ";
String response = httpGet(url, data);

ElasticSearch: query with AND and OR criteria

Frequently when we will need to construct query to elasticsearch which bear AND and OR criteria similar to the following SQL statements:

Statement 1:
SELECT * FROM tblOrder WHERE orderDate='2015-01-01 14:00:00' AND customerID=29439;

Statement 2:
SELECT * FROM tblOrder WHERE orderDate='2015-01-01 14:00:00' OR customerID=29439;

Statement 3:
SELECT * FROM tblOrder WHERE orderDate <= '2015-01-01 14:00:00' AND customerID=29439;

Statement 4:
SELECT * FROM tblOrder WHERE (orderDate='2015-01-01 14:00:00' AND customerID=29439) OR customerID = 20991;

Statement 5:
SELECT * FROM tblOrder WHERE orderDate='2015-01-01 14:00:00' AND (customerID=29439 OR customerID = 20991);

In ElasticSearch, we use "must" and "should" in place of AND and OR and "bool" in place of WHERE.

Suppose in our ElasticSearch (at 179.168.0.1:9200), we have indexed documents having the following structures at index myOrder/myOrder:

{
"orderID" : xxxxx,
"customerID"  : xxxxx,
"orderDate" : "yyyyMMdd'T'HHmmss",
"itemLines" [
  {
   "itemLineID" : xxxx,
   "productID" : yyyyy,
   "quantity" : xxxxx
  },
  ...
]
}

Below is the translation of the above SQL statements to equivalent ElasticSearch query, in the following example we use curl, and we want to get a max of 100 records in each query from start, i.e., 0):

Statement 1:

curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'

{

"from" : 0,

"size" : 100,

"query": {

   "bool" : {

       "must" : [

          "match" : {"orderDate" : "20150101T140000" },

          "match" : {"customerID" : 29439 }

       ]

   }

}

}'

Statement 2:

curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'

{

"from" : 0,

"size" : 100,

"query": {

   "bool" : {

       "should" : [

          "match" : {"orderDate" : "20150101T140000" },

          "match" : {"customerID" : 29439 }

       ]

   }

}

}'

Statement 3:

curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'

{

"from" : 0,

"size" : 100,

"query": {

   "bool" : {

       "must" : [

          "range" : {"orderDate" : { "lte" : "20150101T140000" } },

          "match" : {"customerID" : 29439 }

       ]

   }

}

}'

Statement 4:

curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'

{

"from" : 0,

"size" : 100,

"query": {

   "bool" : {

       "should" : [

          "bool" : {

              "must" : [

                 "match" : {"orderDate" : "20150101T140000" },

                 "match" : {"customerID" : 29439 }

              ]

          },

          "match" : { "customerID " :  20991}

       ]

   }

}

}'

Statement 5:

curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'

{

"from" : 0,

"size" : 100,

"query": {

   "bool" : {

       "must" : [

          "match" : {"orderDate" : "20150101T140000" },

          "bool" : {

              "should" : [

                   "match" : { "customerID " :  20991}, 

                   "match" : {"customerID" : 29439 }

              ]

          }

       ]

   }

}

}'

Setup and run ElasticSearch on Ubuntu 14.04 LTS

Run the following command to download the ElasticSearch 1.4.0:

> wget https://download.elasticsearch.org/elasticsearch/elasticsearch/elasticsearch-1.4.0.deb

Setup ElasticSearch

After download, run the following command to install the debian package:

> sudo dpkg -i elasticsearch-1.4.0.deb

After installation, the configuration files for ElasticSearch are stored in the following directory:

/etc/elasticsearch/

The bin folder for ElasticSearch can be found in the following directory:

/usr/share/elasticsearch/

Now navigate to the user root folder, and open the .bashrc for editing:

> sudo gedit $HOME/.bashrc

In the .bashrc file, add in the following lines to the end

export ES_HOME=/usr/share/elasticsearch
export ES_CONF_HOME=/etc/elasticsearch

Save and close the .bashrc file.

Check whether the elasticsearch is running as a service on Ubuntu:

> sudo service elasticsearch status

if not, start the elasticsearch service by running the following commands:

> sudo service elasticsearch start

Run the following command to test the ElasticSearch:

> curl localhost:9200

You can also open a web browser and enter "localhost:9200" to see ElasticSearch in action.

Create a document

From the terminal, execute a PUT call such as the following:

> curl -XPUT http://127.0.0.1:9200/myindex/mytype/1 -d '{
"start_time" : "2014-11-26T14:00:00",
"end_time" : "2014-11-26T17:30:00",
"subject" : "Elastic Search Tryout",
"agenda" : {
 "item 1" : "Create an index",
 "item 2" : "Delete an index",
 "item 3" : "Open an index",
 "item 4" : "Close an index" }
}'

The _index is specified as "myindex", _type is specified as "mytype", and _id is specified as "1". After the index has been created, it can be accessed by running a GET call

> curl http://127.0.0.1/myindex/mytype/1

We can execute another PUT call as below to generate one more document under myindex/mytype:

> curl -XPUT http://127.0.0.1:9200/myindex/mytype/2 -d '{
"start_time" : "2014-11-27T14:00:00",
"end_time" : "2014-11-27T17:30:00",
"subject" : "Elastic Search Tryout 2",
"agenda" : {
 "item 1" : "Create a document",
 "item 2" : "Delete a document",
 "item 3" : "Execute a search",
 "item 4" : "Delete by search" }
}'

After the index has been created, it can be accessed by running a GET call

> curl http://127.0.0.1/myindex/mytype/2

To create an index document with an auto-generated id, we can do the following:

We can execute another PUT call as below to generate one more document under myindex/mytype:

> curl -XPUT http://127.0.0.1:9200/myindex/mytype/11122 -d '{
"start_time" : "2014-11-28T14:00:00",
"end_time" : "2014-11-28T17:30:00",
"subject" : "Elastic Search Tryout 2",
"agenda" : {
 "item 1" : "Create a document with auto-generated  index",
 "item 2" : "Delete a document",
 "item 3" : "Execute a search",
 "item 4" : "Delete by search" }
}'

The _id should not be omitted, but can be generated using UUID from a programming language.

Create an index

Creating an index is the same as creating a document under an index. From the terminal, execute a PUT call such as the following:

> curl -XPUT http://127.0.0.1:9200/myindex/ -d '{
"settings"  {
 "item 1" : "true",
 "item 2" : "false",
 "item 3" : "1",
 "item 4" : "2" }
}'

Update a document by id

The following commands can be used to update the document with id=11122:

> curl -XPUT http://127.0.0.1:9200/myindex/mytype/11122 -d '{
"start_time" : "2014-11-21T14:00:00",
"end_time" : "2014-11-21T17:30:00",
"subject" : "Elastic Search Tryout 4",
"agenda" : {
 "item 1" : "Create a document with auto-generated  index",
 "item 2" : "Delete a document",
 "item 3" : "Execute a search",
 "item 4" : "Delete by search" }
}'

> curl -XPOST http://127.0.0.1:9200/myindex/mytype/11122 -d '{
"start_time" : "2014-11-24T14:00:00",
"end_time" : "2014-11-24T17:30:00",
"subject" : "Elastic Search Tryout 4",
"agenda" : {
 "item 1" : "Create a document with auto-generated  index",
 "item 2" : "Delete a document",
 "item 3" : "Execute a search",
 "item 4" : "Delete by search" }
}'

Delete a document

Deleting an document will remove the document from the ElasticSearch nodes. From the terminal, execute a DELETE call such as the following:

> curl -XDELETE http://127.0.0.1:9200/myindex/mytype/1

Delete an index

Deleting an index will remove the index from the ElasticSearch nodes. From the terminal, execute a DELETE call such as the following:

> curl -XDELETE http://127.0.0.1:9200/myindex/

Close an index

Closing an index is an alternative to the deleting the index as ElasticSearch put it in an offline mode. From the terminal, execute a POST call such as the following:

> curl -XPOST http://127.0.0.1:9200/myindex/_close

After this, if you run the "curl http://127.0.0.1:9200/myindex", you will get a "not found" message

Open an index

Opening an index will put the offline index online again. From the terminal, execute a POST call such as the following:

> curl -XPOST http://127.0.0.1:9200/myindex/_open

Execute an search

Suppose we want to retrieve a list of documents under an index having a particular word (e.g. "elastic") in their content, we can run a GET call from the terminal:

> curl -XGET http://127.0.0.1:9200/myindex/_search?q=elastic

Suppose we want to search in under a _type = "mytype":

> curl -XGET http://127.0.0.1:9200/myindex/mytype/_search?q=elastic

The query can be written in more details by specifying search in the "subject" field of the document:

> curl -XGET http://127.0.0.1:9200/myindex/mytype/_search -d '
{"query":{"match": { "subject" : "elastic" }}}'

To search all documents under myindex/mytype:

> curl -XGET http://127.0.0.1:9200/myindex/mytype/_search -d '
{"query":{"match_all":{}}}'

Sort a search

Suppose we want the json results returned in certain sorting order (e.g. sort by start_time asc):

> curl -XGET http://127.0.0.1:9200/myindex/mytype/_search -d '
{"query":{"match_all":{}}, "sort":[{"start_time": "asc" }]}'

Filtering

Suppose we want to search records filtered by a range of a values in one its parameter (said, age), and have a particular match query (said, "name" must be "James"), and we want the results to be returned sorted by a field (said, age). Furthermore, we also want the total number of records returned to be limited to 20:

> curl -XGET http://127.0.0.1:9200/myindex/mytype/_search -d'
{
"from": 0, "size": 20,
"query" : {
  "filtered" :
    "query" : {
      "match" : { "name" : "James" }
     },
     "filter": {
       "range" : {
          "age" : { "lte" : 20, "gte" : 10 }
        }
     }
"sort" : { "age" : {"order" : "asc" }}
};

Count by Query

To count the number of documents in myindex/mytype matching a particular query, from the terminal, run the following command:

> curl -XGET http://127.0.0.1:9200/myindex/mytype/_count -d '
{"query":{"match": { "subject" : "elastic" }}}'

Delete by Query

To delete all documents in myindex/mytype matching a particular query,  from the terminal, run the following command:

> curl -XDELETE http://127.0.0.1:9200/myindex/mytype/_query -d '
{"query":{"match": { "subject" : "elastic" }}}'