ElasticSearch order by number of matches in nested fields

Viewed 353

Complete beginner here, quite possibly trying to do the impossible. I have the following structure that I would like to store in Elasticsearch:

{
    "id" : 1,
    "code" : "03f3301c-4089-11e7-a919-92ebcb67fe33",
    "countries" : [
        {
            "id" : 1,
            "name" : "Netherlands"
        },
        {
            "id" : 2,
            "name" : "United Kingdom"
        }
    ],
    "tags" : [
        {
            "id" : 1,
            "name" : "Scanned"
        },
        {
            "id" : 2,
            "name" : "Secured"
        },
        {
            "id" : 3,
            "name" : "Cleared"
        }
    ]
}

I have complete control over how it will be stored, so the structure can change, but it should contain all these fields in some form. I’d like to be able to query this data over countries and tags in such way that all those items having at least one match are returned, ordered by number of matches. If at all possible I’d prefer not to do a full text search.

For example:

id, code, country ids, tag ids
1,  ...,  [1, 2, 3],   [1]
2,  ...,  [1],         [1, 2, 3]

For the question: "which of these was in country 1 or has tag 1 or has tag 2", should return:

2, ..., [1], [1, 2, 3]
1, ..., [1, 2, 3], [1]

In this order, because the second row matches more sub-queries in the above disjunction.

In essence, I’d like to replicate this SQL query:

SELECT p.id, p.code, COUNT(p.id) FROM packages p
LEFT JOIN tags t ON t.package_id = p.id
LEFT JOIN countries c ON c.package_id = p.id
WHERE t.id IN (1, 2, 3) OR c.id IN (1, 2, 3)
GROUP BY p.id
ORDER BY COUNT(p.id);

I’m using ElasticSearch 2.4.5 if that matters.

Hopefully I was clear enough. Thank you for your help!

1 Answers
Related