Convert string to object and finding average

[aneesh (Arcadix Infotech Pvt.Ltd)]

Hi

Having a data as follows

[
  {
    "StudentId": 1,
    "Marks": [
      {
        "SubjectId": 1,
        "vResultAttribute": """{\"TotalTasksCount\":15,\"Result\":73.3,\"TestObjectsCorrect\":64,\"TestMaxPoint\":15}"""
      },
      {
        "SubjectId": 2,
        "vResultAttribute": """{\"TotalTasksCount\":15,\"Result\":73.3,\"TestObjectsCorrect\":64,\"TestMaxPoint\":15}"""
      }
    ]
  },
  {
    "StudentId": 2,
    "Marks": [
      {
        "SubjectId": 1,
        "vResultAttribute": """{\"TotalTasksCount\":15,\"Result\":73.3,\"TestObjectsCorrect\":64,\"TestMaxPoint\":15}"""
      },
      {
        "SubjectId": 2,
        "vResultAttribute": """{\"TotalTasksCount\":15,\"Result\":73.3,\"TestObjectsCorrect\":64,\"TestMaxPoint\":15}"""
      }
    ]
  }
]

I need to find the average of Result (vResultAttribute column is saved in elastic as a string) of each subject for each student.
How can we do this in elastic?

Thanks
Aneesh L

[Christian_Dahlqvist]

I would recommend restructuring how you model your data. Maybe breaking up the example you provided into multiple documents with the following structure during indexing could make querying easier?

{
  "StudentId": 1,
  "Marks": {
    "SubjectId": 1,
    "TotalTasksCount":15,
    "Result":73.3,
    "TestObjectsCorrect":64,
    "TestMaxPoint":15
  }
}

[aneesh (Arcadix Infotech Pvt.Ltd)]

Can I use JSON processor which convert to json and then apply aggregations.
All these Need to do in a single search instance.

[Christian_Dahlqvist]

I am not sure I understand. You need to do this transformation before you index the data. You will therefore need to reindex existing data.

[aneesh (Arcadix Infotech Pvt.Ltd)]

I mean, without restructuring is it possible (With string data)
with the help of JSON processor to convert string to json object and then apply aggregations

[Christian_Dahlqvist]

Given that you likely need to use a script to parse the JSON string and also have a nested structure, I suspect it would not be straightforward, but I can't say whether it is possible or not. Maybe someone else have done something similar in the past and can assist?

[aneesh (Arcadix Infotech Pvt.Ltd)]

Hope the same.
Thanks Christian_Dahlqvist

[dadoonet (David Pilato)]

Do what @Christian_Dahlqvist wrote at Convert string to object and finding average

That's really the way to solve this problem.