Multiple SQL statements in query

[antongb]

Hi,
I would like to design a query which finds the subset of field values based on the same values in a different document.
Here is the document structure for context:

TABLE = FRUIT_QUESTIONS
{"ID": 1, "question": "Do you like fruits?", "answer":"yes"}
{"ID": 1, "question": "Do you like vegetables?", "answer":"no"}
{"ID": 2, "question": "Do you like fruits?", "answer":"yes"}
{"ID": 2, "question": "Do you like vegetables?", "answer":"yes"}

In SQL this would look somethinglike this:

CREATE TABLE FIRST_QUERY AS SELECT ID FROM FRUIT_QUESTIONS WHERE answer='yes' AND question='Do you like fruits?';
SELECT count(*) FROM FRUIT_QUESTIONS WHERE answer='no' AND Q='Do you like vegetables?' AND ID in FIRST_QUERY ;

or all as one statement

SELECT count(*) FROM FRUIT_QUESTIONS WHERE A='yes' AND question='Do you like fruits?' AND id in (SELECT id FROM FRUIT_QUESTIONS WHERE answer='no' AND Q='Do you like vegetables?')

The query effectively is "how many people like fruid but don't like vegetables?" In this example the example is one person.

My problem is I'm not sure how to do this as a query in Kibana. Is there a way to translate this to query DSL?

Thanks

edit:
So elastic doesn't support IN but is there another way?

[tsullivan (Tim Sullivan)]

The way to do this would be to start before adding the data to Elasticsearch. The documents need to decorated with fields that help searching for what you want:

{"ID": 2, "question": "Do you like vegetables?", "answer":"yes", "likes_veg":true,"likes_fruit":false}