모든 데이터 검색
SELECT * FROM TABLE
----------------------------------------------------------------------
{
"query": {
"match_all": {}
}
}
취득건수 와 취득개시위치 지정
SELECT * FROM TABLE LIMIT 10 OFFSET 5
----------------------------------------------------------------------
{
"from": 5,
"size": 10,
"query": {
"match_all": {}
}
}
취득하는 컬럼지정
SELECT TABLE_name, TABLE_count FROM TABLE
----------------------------------------------------------------------
{
"_source": [
"TABLE_name",
"TABLE_count"
],
"query": {
"match_all": {}
}
}
지정하는 값으로 검색
SELECT * FROM product WHERE product_name = 'apple'
----------------------------------------------------------------------
{
"query": {
"term": {
"product_name": {
"value": "apple"
}
}
}
}
지정하는 값에 일치하지 않음
SELECT * FROM product WHERE product_name <> 'apple'
----------------------------------------------------------------------
{
"query": {
"bool": {
"must_not": [
{
"term": {
"product_name": "りんご"
}
}
]
}
}
}
IN
SELECT * FROM product WHERE product_name IN ('apple', 'carret')
----------------------------------------------------------------------
{
"query": {
"terms": {
"product_name": [
"apple",
"carret"
]
}
}
}
LIKE
SELECT * FROM product WHERE product_name LIKE '%a%'
----------------------------------------------------------------------
{
"query": {
"wildcard": {
"product_name": {
"value": "*a*"
}
}
}
}
범위
>= gte Greater-Than or Equal to
> gt Greater-Than
<= lte Less-Than or Equal to
< lt Less-Than
SELECT * FROM product WHERE product_count >= 10
----------------------------------------------------------------------
{
"query": {
"range": {
"product_count": {
"gte" : 10
}
}
}
}
OR 조건
SELECT * FROM product WHERE product_name= 'apple' OR product_count >= 10
----------------------------------------------------------------------
{
"query": {
"bool": {
"should": [
{
"term": {
"product_name": "りんご"
}
},
{
"range": {
"product_count": {
"gte": 10
}
}
}
]
}
}
}
AND OR 조건
SELECT * FROM product WHERE product_count >= 10 AND (product_name = 'apple' OR product_price = 100) ----------------------------------------------------------------------
{
"query": {
"bool": {
"must": [
{
"range": {
"count": {
"gte": 10
}
}
},
{
"bool": {
"should": [
{
"term": {
"product_name": "りんご"
}
},
{
"term": {
"product_price": 100
}
}
]
}
}
]
}
}
}
중복행 삭제
SELECT DISTINCT product_name FROM product
----------------------------------------------------------------------
{
"collapse": {
"field": "product_name"
},
"query": {
"match_all": {}
}
}
삭제
DELETE /my-index-000001/_doc/1
ORDER BY
SELECT * FROM product ORDER BY product_name ASC
----------------------------------------------------------------------
{
"query": {
"match_all": {}
},
"sort": [
{
"product_name": {
"order": "asc"
}
}
]
}
GROUP BY
SELECT product_name FROM product GROUP BY product_name
----------------------------------------------------------------------
{
"aggs": {
"product_name_aggs": {
"terms": {
"field": "product_name",
"size": 10
}
}
}
}
복수조건 과 복수키
SELECT *
FROM product
WHERE
code IN ('11113', '2222')
AND type IN ('aaa更','bbb','ccc')
AND create_date between '2020-07-27' and '2021-07-27'
----------------------------------------------------------------------
GET text_search/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"code": ["11113","2222"]
}
}, {
"terms": {
"type": ["aaa","bbb","ccc"]
}
}, {
"range": {
"create_date": {
"gte": "2020-07-27",
"lte": "2021-07-27"
}
}
}]
}
},
"sort": {
"create_date": {
"order": "desc"
}
}
}