CODEWITHSUNDEEP
sqlelasticsearch
Churro
Churro

Reputation: 4366

How to properly implement boolean logic

I'm using ElasticSearch for logging within an application. I need to write a log viewer that filters on all the fields of my document.

My documents look like this:

    "_source": {
       "timestamp": 1373502012000,
       "userId": 6,
       "paId": 56331,
       "lId": 6,
       "prId": 2,
       "vId": 6336,
       "actionType": "LOAD_DATA"
    }

actionType is an enum (Java).

I need to write a ElasticSearch equivalent to the following SQL query:

SELECT * FROM snapshot.action_log_item 

WHERE timestamp BETWEEN 1372718783286 AND 1372718783286

AND userId=6 
AND paId=56331 
AND lId=6 
AND prId=2 
AND vId=6336
AND (
    actionType='LOAD_DATA' OR 
    actionType='SAVE_DATA' OR 
    actionType='LOG_IN'
);

Please help me write a properly nested query and/or filter to get a result equivalent to my SQL statement.

EDIT Here's my current code (that works without the { "or"... portion).

{
  "query" : {
    "bool" : {
      "must" : [ {
        "term" : {
          "userId" : 6
        }
      }, {
        "term" : {
          "lId" : 6
        }
      }, {
        "term" : {
          "vId" : 6336
        }
      } ]
    }
  },
  "filter" : {
    "and" : {
      "filters" : [ {
        "term" : {
          "paId" : 56331
        }
      }, {
        "range" : {
          "timestamp" : {
            "from" : 1372718783286,
            "to" : 1377643583286,
            "include_lower" : true,
            "include_upper" : true
          }
        }
      }, {
        "or" : {
          "filters" : [ {
            "term" : {
              "actionType" : "LOAD_DATA"
            }
          }, {
            "term" : {
              "actionType" : "SAVE_DATA"
            }
          }, {
            "term" : {
              "actionType" : "LOG_IN"
            }
          } ]
        }
      } ]
    }
  }
}

EDIT: The following query works. It's not the same query as above, but it returns the expected result. It seems that these filters/queries don't work on the actionType field.

{
  "size": 30, 
  "query": {
    "filtered": {
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "uId": 6
              }
            },
            {
              "term": {
                "loId": 6
              }
            },
            {
              "term": {
                "prId": 2
              }
            },
            {
              "terms": {
                "paId": [
                  56331,
                  56298
                ],
                "minimum_should_match": 1
              }
            }
          ]
        }
      },
      "filter": {
        "range": {
          "timestamp": {
            "from": 1372718783286,
            "to": 1377643583286,
            "include_lower": true,
            "include_upper": true
          }
        }
      }
    }
  }
}

Upvotes: 0

Views: 117

Answers (1)

moliware
moliware

Reputation: 10278

The {or... portion should like this:

{
  "or": [
    {
      "term": {
        "actionType": "LOAD_DATA"
      }
    },
    {
      "term": {
        "actionType": "SAVE_DATA"
      }
    },
    {
      "term": {
        "actionType": "LOG_IN"
      }
    }
  ]
}

You can check the doc for that filter here

Edit

As I see you are having problems I rewrote your query. I hope it helps

{
  "query": {
    "filtered": {
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "userId": 6
              }
            },
            {
              "term": {
                "paId": 56331
              }
            },
            {
              "term": {
                "lId": 6
              }
            },
            {
              "term": {
                "prId": 2
              }
            },
            {
              "term": {
                "vId": 6336
              }
            },
            {
              "terms": {
                "actionType": [
                  "LOAD_DATA",
                  "SAVE_DATA",
                  "LOG_IN"
                ],
                "minimum_should_match": 1
              }
            }
          ]
        }
      },
      "filter": {
        "range": {
          "timestamp": {
            "from": 1372718783286,
            "to": 1377643583286,
            "include_lower": true,
            "include_upper": true
          }
        }
      }
    }
  }
}

Basically I put the date range as filter and the other conditions are term queries inside the must clause of the boolean query. You can see that the or part is now inside the must clause as a terms query that act as or between those 3 values.

Upvotes: 1

Related Questions