https://www.elastic.co/

Retrieve

Multiple where condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
GET /index/type/_search
{
"query": {
"bool": {
"must": [
{
"term": {
"field1": {
"value": "foo"
}
}
},
{
"term": {
"field2": {
"value": "bar"
}
}
}
]
}
},
"from": 0,
"size": 20
}

equivalent to

1
2
3
4
5
SELECT *
FROM index
WHERE field1 = 'foo'
AND field2 = 'bar'
LIMIT 20 OFFSET 0;

Update

Bulk update

1
2
3
4
5
6
7
8
9
10
11
POST /index/type/_update_by_query
{
"conflicts": "proceed",
"script": {
"source": "ctx._source['field'] = 'some value'"
},
"query": {
"term": {
"user": "kimchy"
}
}

equivalent to

1
2
3
UPDATE index
SET field = 'some value'
WHERE user = 'kimchy';

OR if want to add json value

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
POST /index/type/_update_by_query
{
"conflicts": "proceed",
"script": {
"source": "ctx._source.field = params.some_json_field",
"params": {
"some_json_field": {
"sub_field_1": "Foo",
"sub_field_2": "Bar"
}
}
},
"query": {
"term": {
"user": "kimchy"
}
}
}

After run update, must flush it

1
GET /index/type/_flush
References:

GROUP BY query

Translate

1
2
3
4
5
SELECT COUNT(*) as doc_count
FROM index
GROUP BY field_name
ORDER BY doc_count DESC
LIMIT 10

equivalent to

1
2
3
4
5
6
7
8
9
10
11
12
GET /index/type/_mapping
{
"aggregations": {
"some_name": {
"terms": {
"field": "field_name",
"size": 10
}
}
},
"size": 0
}
References:

Add new field

Update the mapping first

1
2
3
4
5
6
7
8
PUT /index/type/_mapping
{
"properties": {
"new_field": {
"type": "keyword"
}
}
}