-
-
Save robfaraj/f8a124e09faf61cff35b to your computer and use it in GitHub Desktop.
// No Term Provided | |
// | |
// SELECT id FROM type | |
// WHERE site_id IN (1,7) AND status_id=2 AND branch_id=1651 | |
// ORDER BY branch_id DESC | |
// | |
curl -XPOST 'http://localhost:9200/index/type/_search?pretty=true&fields=_id' -d ' | |
{ | |
"query" : { | |
"constant_score" : { | |
"filter" : { | |
"and" : [ | |
{ | |
"query" : { | |
"field": { | |
"site_id" : "1 OR 7" | |
} | |
} | |
}, | |
{ | |
"term": { | |
"status_id": 2 | |
} | |
}, | |
{ | |
"term": { | |
"branch_id": 1651 | |
} | |
} | |
] | |
} | |
} | |
}, | |
"sort" : [ | |
{ "branch_id" : {"reverse" : false} }, | |
"_score" | |
], | |
"facets" : { | |
"sites" : { | |
"terms" : { "field" : "type_id" } | |
}, | |
"status" : { | |
"terms" : { "field" : "status_id" } | |
} | |
} | |
} | |
' | |
// Term Provided | |
// | |
// SELECT id FROM type | |
// WHERE _all LIKE (%foobar%) AND site_id IN (1,7) AND status_id=2 AND branch_id=1651 | |
// ORDER BY branch_id DESC | |
// | |
curl -XPOST 'http://localhost:9200/index/type/_search?pretty=true&fields=_id' -d ' | |
{ | |
"query" : { | |
"filtered" : { | |
"query" : { | |
"query_string" : { | |
"query" : "foobar" | |
} | |
}, | |
"filter" : { | |
"and" : [ | |
{ | |
"query" : { | |
"field": { | |
"site_id" : "1 OR 7" | |
} | |
} | |
}, | |
{ | |
"term": { | |
"status_id": 2 | |
} | |
}, | |
{ | |
"term": { | |
"branch_id": 1651 | |
} | |
} | |
] | |
} | |
} | |
}, | |
"sort" : [ | |
{ "branch_id" : {"reverse" : true} }, | |
"_score" | |
], | |
"facets" : { | |
"sites" : { | |
"terms" : { "field" : "type_id" } | |
}, | |
"status" : { | |
"terms" : { "field" : "status_id" } | |
} | |
} | |
} | |
' |
Your second query could look like:
curl -XGET 'http://127.0.0.1:9200/index/type/_search?pretty=1' -d '
{
"fields" : [
"_id"
],
"query" : {
"query" : {
"field" : {
"_all" : "*foobar*"
}
},
"filtered" : {
"filter" : {
"and" : [
{
"terms" : {
"site_id" : [1,7]
}
},
{
"term" : {
"status_id" : 2
}
},
{
"term" : {
"branch_id" : 1651
}
}
]
}
}
}
}
'
Note: it doesn't make sense to sort on branch_id, because you're saying it should be exactly equal to 1651. Instead, by leaving out sort
it sorts by _score
: desc
(ie the most relevant first). This wouldn't have any meaning in the first query, because you don't have a score (because you're just using filters, no query string)
Thanks. The 'terms' filter is awesome. I actually tried the array syntax with 'term', but didn't notice 'terms'.
If I only want to search the string fields (title, description, site and item), then is that what _all does?
"query" : {
"field" : {
"_all" : "foobar"
}
},
Here is my mapping
"_source" : {"enabled" : false},
"_id" : {"store" : "yes"},
"properties" : {
"title" : {"type" : "string", "store": "yes", "boost" : 5.0},
"description" : {"type" : "string", "store": "no"},
"item" : {"type" : "string", "store": "no", "boost" : 3.0 },
"site" : {"type" : "string", "store": "no", "boost": 2.0, "index" : "not_analyzed"},
"status_id" : {"type" : "integer", "store" : "no"},
"site_id" : {"type" : "integer", "store" : "yes"},
"is_visible" : {"type" : "integer", "store": "no"},
"item_id" : {"type" : "integer", "store" : "yes"}
}
}
Not quite - each of your fields are stored in _all
unless you set it to include_in_all: false
(see http://www.elasticsearch.org/guide/reference/mapping/core-types.html ).
Btw, you don't need to store each field separately. You can access it through _source
. (there may be valid reasons for storing them though)
I'm not storing _source
because I have set "_source" : {"enabled" : false}
in my mapping. I'm only retrieving certain fields from elastic search. I've marked those with "store": "yes"
in my mapping. The rest are there for searching or filtering.
In my example above does _all
map to all 8 fields or just the 4 fields of string type?
Right, I didn't notice that. The one good thing about using _source
instead of storing each field separately, is that it makes it easy to reindex your data when you decide to change your mapping, or you upgrade and something core has changed which requires reindexing.
_all
maps to all 8 fields - its includes all fields, exceptany which have include_in_all
set to false
. The value of store
has no impact on inclusion in _all
Hiya
Your first query could be better written:
(i've also ignored the facets)