Nested field total at document level

[Vinutha]

We are facing issue with below requirement, it would be great if you could suggest us with a solution.

We have nested data in a document . Below is the part of document JSON structure
{
"contents": {
"type": "nested",
"properties": {

                        "id": {
                            "type": "long"
                        },
                        "sentiments": {
                            "type": "nested",
                            "properties": {
                                "categoryId": {
                                    "type": "long"
                                },
                                "id": {
                                    "type": "long"
                                },
                                "score": {
                                    "type": "double"
                                },
                            }
                        }
                    }
                }

}
We want to calculate Sum of "score" at "content" level and "document" level for each "categoryId" and for each document, based on Sum value we want to categorize it as (+ve, -ve and neutral )and count from list of queried documents.

Example for single document which has 2 entries in "contents" element and "sentiments" are having 4 and 3 entries respectively :
{
"contents": [
{
"id": 13878804,
"sentiments": [
{
"score": -1,
"categoryId": 50026,
"id": 7461537
},
{
"score": -1,
"categoryId": 50026,
"id": 7461578
},
{
"score": -1,
"categoryId": 50052,
"id": 7461579
},
{
"score": 1,
"categoryId": 50012,
"id": 7461580
}
]
},
{
"id": 13878805,
"sentiments": [
{
"score": -1,
"categoryId": 50026,
"id": 7461537
},
{
"score": 0,
"categoryId": 50052,
"id": 7461578
},
{
"score": -1,
"categoryId": 50052,
"id": 7461579
}
]
}
]
}

content level count :
for id - 13878804

• categoryId - 50026 (total score : -2)
• categoryId - 50052 (total score : -1)
• categoryId - 50012 (total score : 1)

for id - 13878805

• categoryId - 50026 (total score : -1)
• categoryId - 50052 (total score : -1)

Expected Result :
50026 - (-ve count : 2 ,+ve count :0,neutral : 0) -ve count is 2 the category is having total score -ve in both the contents
50052 - (-ve count : 2 ,+ve count :0,neutral : 0)
50012 - (-ve count : 0 ,+ve count :1,neutral : 0)

document level count :
Count needs to be taken at document level

• categoryId - 50026 (total score : -3)
• categoryId - 50052 (total score : -2)
• categoryId - 50012 (total score : 1)

Expected Result :
50026 - (-ve count : 1 ,+ve count :0,neutral : 0) -ve count is 1 the category is having total score -ve
50052 - (-ve count : 1 ,+ve count :0,neutral : 0)
50012 - (-ve count : 0 ,+ve count :1,neutral : 0)
We want count of documents which are having the no of +ve,-ve and neutral for each category based on sum of scores