SQL 转换 elasticsearch

Dec 28, 2018

select * from visits where visitable_id=22362 or os is not null;

{
  "query": {
    "bool": {
      "must": [{
        "term": {
          "visitable_id": "22362"
        }
      }],
      "must": [{
        "exists": {
          "field": "os"
        }
      }],
      "should": []
    }
  },
  "from": 0,
  "size": 10,
  "sort": [],
  "aggs": {}
}

select * from visits where visitable_id=22362 AND os is not null;

{
  "query": {
    "bool": {
      "must": [{
        "term": {
          "visitable_id": "22362"
        }
      },{
        "exists": {
          "field": "os"
        }
      }],
      "should": []
    }
  },
  "from": 0,
  "size": 10,
  "sort": [],
  "aggs": {}
}

select os,count(os)  from visits where visitable_id = 22362 and os is not null group by os
select browser,count(browser) from visits where visitable_id = 22362 and os is not null group by browser
select device_type,count(device_type) from visits where visitable_id = 22362 and os is not null  group by device_type


{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "visitable_id": "22362"
          }
        },
        {
          "exists": {
            "field": "os"
          }
        }
      ],
      "should": []
    }
  },
  "aggs": {
    "os": {
      "terms": {
        "field": "os.keyword"
      }
    },
    "browsers": {
      "terms": {
        "field": "browser.keyword"
      }
    },
    "device_type": {
      "terms": {
        "field": "device_type.keyword"
      }
    }
  }
}

ref: https://stackoverflow.com/questions/39113842/elastic-search-count-with-group-by-and-where-condition

Ref: https://stackoverflow.com/questions/14745210/create-elasticsearch-curl-query-for-not-null-and-not-empty http://railscasts.com/episodes/307-elasticsearch-part-2

此文章短链接: http://dlj.bz/DuZ8Yr

Posted by grayson Dec 28, 2018 rails elasticsearch sql

« rails-5-2-credentials

Ruby's Array and Array.wrap的区别»