github.com/bingoohuang/gg@v0.0.0-20240325092523-45da7dee9335/pkg/elasticsql/readme.md (about)

     1  # elasticsql
     2  
     3  forked from https://github.com/cch123/elasticsql
     4  
     5  This tool converts sql where clause to elasticsearch dsl
     6  
     7  Currently support:
     8  
     9  - [x] sql and expression
    10  - [x] sql or expression
    11  - [x] equal(=) support
    12  - [x] not equal(!=) support
    13  - [x] gt(>) support
    14  - [x] gte(>=) support
    15  - [x] lt(<) support
    16  - [x] lte(<=) support
    17  - [x] sql in (eg. id in (1,2,3) ) expression
    18  - [x] sql not in (eg. id not in (1,2,3) ) expression
    19  - [x] paren bool support (eg. where (a=1 or b=1) and (c=1 or d=1))
    20  - [x] sql like expression (currently use match phrase, perhaps will change to wildcard in the future)
    21  - [x] sql order by support
    22  - [x] sql limit support
    23  - [x] sql not like expression
    24  - [x] field missing check
    25  - [x] support aggregation like count(\*), count(field), min(field), max(field), avg(field)
    26  - [x] support aggregation like stats(field), extended_stats(field), percentiles(field) which are not standard sql
    27    function
    28  - [ ] null check expression(is null/is not null)
    29  - [ ] join expression
    30  - [ ] having support
    31  
    32  Usage
    33  -------------
    34  
    35  > go get -u github.com/cch123/elasticsql
    36  
    37  Demo :
    38  
    39  ```go
    40  package main
    41  
    42  import (
    43  	"fmt"
    44  
    45  	"github.com/bingoohuang/gg/pkg/elasticsql"
    46  )
    47  
    48  var where = `
    49  a=1 and x = '三个男人'
    50  and create_time between '2015-01-01T00:00:00+0800' and '2016-01-01T00:00:00+0800'
    51  and process_id > 1 order by id desc limit 100,10
    52  `
    53  
    54  func main() {
    55  	dsl, _ := elasticsql.Convert(where)
    56  	fmt.Println(dsl)
    57  }
    58  
    59  ```
    60  
    61  will produce :
    62  
    63  ```json
    64  {
    65    "query": {
    66      "bool": {
    67        "must": [
    68          {
    69            "match": {
    70              "a": {
    71                "query": "1",
    72                "type": "phrase"
    73              }
    74            }
    75          },
    76          {
    77            "match": {
    78              "x": {
    79                "query": "三个男人",
    80                "type": "phrase"
    81              }
    82            }
    83          },
    84          {
    85            "range": {
    86              "create_time": {
    87                "from": "2015-01-01T00:00:00+0800",
    88                "to": "2016-01-01T00:00:00+0800"
    89              }
    90            }
    91          },
    92          {
    93            "range": {
    94              "process_id": {
    95                "gt": "1"
    96              }
    97            }
    98          }
    99        ]
   100      }
   101    },
   102    "from": 100,
   103    "size": 10,
   104    "sort": [
   105      {
   106        "id": "desc"
   107      }
   108    ]
   109  }
   110  
   111  ```
   112  
   113  If your sql contains some keywords, eg. order, timestamp, don't forget to escape these fields as follows:
   114  
   115  ```
   116  select * from `order` where `timestamp` = 1 and `desc`.id > 0
   117  ```
   118  
   119  Warning
   120  ------------
   121  To use this tool, you need to understand the term query and match phrase query of elasticsearch.
   122  
   123  Setting a field to analyzed or not analyzed will get different results.
   124  
   125  Details
   126  ------------
   127  For more details of convertion, please refer to the [wiki](https://github.com/cch123/elasticsql/wiki)
   128  
   129  Other info
   130  ------------
   131  When writing this tool, I tried to avoid the deprecated dsl filters and aggregations, so it is compatible with most
   132  versions of the elasticsearch
   133  
   134  If you have any advices or ideas, welcome to submit an issue or Pull Request!
   135  
   136  License
   137  -----------
   138  MIT