# Convert string to object and finding average

**URL:** <https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714>\
**Category:** Elasticsearch\
**Created:** [March 13, 2018, 10:31am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714 "2018-03-13T10:31:27Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![aneesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aneesh/32/37819_2.png) [@aneesh](https://discuss.elastic.co/u/aneesh)\
**Post date:** [March 13, 2018, 10:31am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/1 "2018-03-13T10:31:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 13, 2018, 10:46am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/2 "2018-03-13T10:46:15Z")

</div>

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?

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

```

---

<div class="post-metadata">

**Author:** ![aneesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aneesh/32/37819_2.png) [@aneesh](https://discuss.elastic.co/u/aneesh)\
**Post date:** [March 13, 2018, 10:57am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/3 "2018-03-13T10:57:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 13, 2018, 10:58am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/4 "2018-03-13T10:58:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![aneesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aneesh/32/37819_2.png) [@aneesh](https://discuss.elastic.co/u/aneesh)\
**Post date:** [March 13, 2018, 11:15am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/5 "2018-03-13T11:15:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 13, 2018, 11:24am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/6 "2018-03-13T11:24:42Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![aneesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aneesh/32/37819_2.png) [@aneesh](https://discuss.elastic.co/u/aneesh)\
**Post date:** [March 13, 2018, 11:41am UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/7 "2018-03-13T11:41:17Z")

</div>

Hope the same.  
Thanks Christian\_Dahlqvist

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 13, 2018, 12:14pm UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/8 "2018-03-13T12:14:10Z")

</div>

Do what @Christian_Dahlqvist wrote at [Convert string to object and finding average](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/2?u=dadoonet)

That's really the way to solve this problem.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 10, 2018, 12:14pm UTC](https://discuss.elastic.co/t/convert-string-to-object-and-finding-average/123714/9 "2018-04-10T12:14:12Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.