Elasticsearch 7でよく使うDSLとか

自分のメモ用

# 全部とる

```
{
  "query": {
        "match_all": {}
  }
}
```


# ランダム取得

```

{
  "query": {
    "function_score": {
      "query": {
        "match_all": {}
      },
      "random_score": {}
    }
  }
}

```

# ソートする

```
{
  "query": {
        "match_all": {}
  },
   "sort" : [
      {"price" : {"order" : "asc", "mode" : "avg"}}
   ]
}
```



# 特定の属性だけを取得

```
{
  "query": {
        "match_all": {}
  },
  "_source": [
    "items.quantity",
    "is_member"
  ]
}
```
nestedの場合は「.」で区切る

# とりあえず探す
https://www.elastic.co/guide/en/elasticsearch/reference/7.3/query-dsl-simple-query-string-query.html

```
{
  "query": {
    "simple_query_string": {
      "query": "犬",
      "default_operator": "and",
      "fields": [
        "item_name.*"
      ],
      "minimum_should_match": 1
    }
  }
}
```
default_operatorはスペースをどう取り扱うか。
fieldsの^5はブースト
"item_name.\*"の\*はマルチフィールドの場合はこれで全部指定


#検索結果のカウントを取る
_searchで検索した場合はなぜかhits,total,valueが10000以上にならないのでDSLに以下を追加する

```
{
  "track_total_hits": true,
  "query": {・・・・

```






# 探す

```

{
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "xxxxx": "yyyyy"
          }
        }
      ],
      "must": [
        {
          "query_string": {
            "analyze_wildcard": true,
            "query": "-属性: (列挙 aaa /bbb.*/ /ccc.*/ )"
          }
        },
        {
          "query_string": {
            "analyze_wildcard": true,
            "query": "name: (/ノートPC.*/)"
          }
        },
        {
          "range": {
            "balance": {
              "gte": 20000,
              "lte": 30000
            }
          }
        },
        {
          "match_all": {}
        },
        {
          "range": {
            "ordered_at": {
              "format": "strict_date_optional_time",<<だぶんなくてもいい
              "gte": "2019-01-05T00:00:00+0900",
              "lte": "2019-01-25T00:00:00+0900"
            }
          }
        },
        {
          "match_phrase": {
            "aaa": {
              "query": "bbb"
            }
          }
        }
      ]
    }
  },
  "size": 10,
  "_source": {
    "excludes": []
  }
}


```

_analyzeを確認する

```
curl -XGET  -H "Content-Type: application/json" -H "Accept: application/json" 'http://192.168.1.130:9200/fff/_analyze?pretty' -d '
{
  "analyzer" : "analyzer_kuromoji_search",
  "explain" : true,
  "text" : ["シャツ"]
}
'




```

nodeを確認する

```
curl -XGET -H "Content-Type: application/json" -H "Accept: application/json" 'http://192.168.1.130:9200/_cat/nodes?pretty'
```

# 集計

```json
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "stitename": {
      "terms": {
        "field": "site_name",
        "size": 100,
        "shard_size": 10000,
        "order": {
          "_count": "desc"
        }
      }
    }
  },
  "size": 0
}
```
"shard_size": 10000,は結構大きい値にしないと正しく集計されないことがある
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_shard_size_3


# 集計をさらに数える
```
curl -X POST http://localhost:9200/alias.order_search/order_search/_search -d '


{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "range": {
            "ordered_at": {
              "gte": "2016-01-01 00:00:00+0900",
              "lte": "2018-12-31 23:59:59+0900"
            }
          }
        }
      ],
      "must_not": []
    }
  },
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "ordered_at",
        "interval": "1M",
        "time_zone": "Asia/Tokyo",
        "min_doc_count": 1
      },
      "aggs": {
        "3": {
          "cardinality": {＜＜distinctみたいなやつ
            "field": "shop"
          }
        }
      }
    }
  }
}
'
```

# 時系列に集計

```json
{
        "query": {
            "bool": {
                "must": [
                    {
                        "match_all": {}
                    },
                    {
                        "range": {
                            "accessed_at": {
                                "gte": gte,
                                "lte": lte
                            }
                        }
                    },

                    {
                        "range": {
                            "processing_time": {
                                "gte": 3, 
                                "lte": 300
                            }
                        }
                    }
                ],
                "must_not": []
            }
        },
        "size": 0,
        "_source": {
            "excludes": []
        },
        "aggs": {
            "2": {
                "date_histogram": {
                    "field": "accessed_at",
                    "interval": "1m",
                    "time_zone": "Asia/Tokyo",
                    "min_doc_count": 1
                }
            }
        }
    }
```













# nestedしたdocumentの集計

普通に集計しただけたとsqlのorder byっぽく並ばないのでスクリプトでつなげて集計する

## documentの構成

``` json 
{
  "user_id":"12345",
  "name":"山田 太郎",
  "items":{
    {"name":"魚","price":250,"quantity":10},
    {"name":"肉","price":1550,"quantity":5},
    {"name":"洗剤","price":150,"quantity":2},
  }
}
```
こんな感じでたくさん入っているとする。

## DSL

``` json 
"aggs": {
    "1": {
        "nested": {
            "path": "items"
        },
        "aggs": {
            "items": {
                "terms": {
                    "field": "items.name",
                    "size": 100,
                    "order": {
                        "4": "desc"
                    }
                },
                "aggs": {
                    "amount": {
                        "sum": {
                            "script": {
                                "inline": "doc['items.price'].value * doc['items.quantity'].value"
                            }
                        }
                    },
                    "quantity": {
                        "sum": {
                            "terms": {"field": "quantity"}
                        }
                    },
                    "user_name": {
                        "reverse_nested": {},
                        "aggs": {
                            "user_name": {
                                "terms": {
                                    "field": "name"
                                }
                            }
                        }
                    }

                }
            }
        }
    }
}


```



# nestedしたdocumentの集計2
nested部分にて特定の属性の値でfilterして特定の値の数を数える(これは７じゃ動かないかも)

nestedのdoc
``` json
{
    "name":"かわいい付箋",
	"feature": [
		{
			"name": "color",
			"value": "紺" //<<これのカウントをしたい
		},
		{
			"name": "item",
			"value": "付箋"
		}
	]
}
```

DSL

``` json
{
  "query": {
    "match_all": {}
  },
  "size": 0,
  "aggs": {
    "1": {
      "nested": {
        "path": "feature"
      },
      "aggs": {
        "filter_reseller": {
          "filter": {
            "bool": {
              "filter": [
                {
                  "term": {
                    "feature.name": "color"
                  }
                }
              ]
            }
          },
          "aggs": {
            "value": {
              "terms": {
                "field": "feature.value",
                "size": 20
              }
            }
          }
        }
      }
    }
  }
}
```
結果
``` json
{
	"aggregations": {
		"1": {
			"doc_count": 331,
			"filter_reseller": {
				"doc_count": 61,
				"value": {
					"doc_count_error_upper_bound": 0,
					"sum_other_doc_count": 0,
					"buckets": [
						{
							"key": "黒",
							"doc_count": 17
						},
						{
							"key": "白",
							"doc_count": 6
						},
						{
							"key": "紺",
							"doc_count": 6
						},
						{
							"key": "茶",
							"doc_count": 6
						},
						{
							"key": "グレー",
							"doc_count": 4
						}
					]
				}
			}
		}
	}
}
```

# 時刻の指定
いつも混乱する

```
"time": "2022-12-22 09:37:33+0000"
~~~~~+この時間はUTCから＋NNNNした時間
2022-12-22 09:37:33から＋０時間した時刻＝UTC時刻+0時間


"time": "2022-12-22 09:37:33+0900"
~~~~~+この時間はUTCから＋NNNNした時間
2022-12-22 09:37:33から＋9時間した時刻＝UTC時刻+9時間

```