Searching multiple indices, trying to filter out specific values in specific indices


(C M) #1

I need to query several indices, and filter out certain results based on values within each index.

It's forum software, so I want to search in forums, individual posts, and users for a keyword. But I need to filter for specific forums and posts the user has access to.

I am specifying the indices in the URL, like so: http://hostname:port/forum,post,user/_search

And then the query is something like:

POST _search { "query": { "match": { "_all": { "query": "flying house lizard", "fuzziness":"1", "operator":"and" } } } }

edit: I don't know why the preformatting isn't working for me, sorry about the jumbled query

Anyway, I want to pass in some _id values for the forum index, and some _id values for the post index to keep the search within those. Seems like I'd want a filter.

I read this: https://www.elastic.co/guide/en/elasticsearch/reference/1.4/query-dsl-indices-filter.html but find it pretty unhelpful because the context isn't quite clear (which is a problem I seem to be having in general with the documentation).

I've searched the web and this forum and cannot find anything that looks like my problem. What am I doing wrong?


(Mark Walkom) #2

Are you on 1.4?


(C M) #3

Sorry for the delay, mind has been elsewhere for the last few days.

I am using v2.1.0


(Mark Walkom) #4

Ok, cause you're looking at 1.4 docs there. You may want to head to https://www.elastic.co/guide/en/elasticsearch/reference/2.1/query-dsl-indices-query.html


(C M) #5

Right you are, must have followed a link to that. In general I have been looking at 2.1.0 docs. Let me go back and doublecheck that I am following the current docs for this specific thing before proceeding. Thank you for pointing that out.


(C M) #6

Alright, I read the correct docs. Recall I am trying to search 3 indices, and for each one filter out certain results.

here's what I wrote:

POST _search { "query": { "indices": { "indices": ["post"], "query": { "term": {"message":"test"} } } } }

This returns results, of course, but it doesn't seem to be very useful. 1) , it doesn't allow me to specify different fields in each index, and 2) this doesn't really address filtering out specific things.

I want to search across 3 indices, and filter out certain docs from each index based on a field within each index. Is there not a way to do that?

I know ES query DSL doesn't directly translate to SQL, but this should help illustrate what I need:

`
SELECT *
FROM table1
WHERE table1.col1 = 'value to search for'
and table1.col2 != 100

UNION

SELECT *
FROM table2
WHERE table2.col1 = 'value to search for'
and table2.col2 != 1

UNION

SELECT *
FROM table3
WHERE table3.col1 = 'value to search for'
and table3.col2 != 55
`

I have been combing though the documentation for weeks now and just can't seem to piece together how to do this.

PS: the preformatted text isn't working on that SQL query. I've edited several times and don't know why it won't work. Sorry for the poor formatting.


(Mark Walkom) #7

Do all three indices have the same mapping?


(C M) #8

no, they do not.


(C M) #9

forgive me for being dense, but does your question imply that you cannot perform this operation against indices with different mappings?


(Mark Walkom) #10

Basically you cannot do this, any filters will apply to all indices you want to query on.


(C M) #11

That helps, now I can stop barking up the wrong tree. Thank you.

So there is no way to achieve this using a query (as opposed to filters) either?


(C M) #12

I guess I mean I don't really care about the method as much as the end product. So if there's another way, I would appreciate being pointed in the right direction.


(system) #13