JSON attribute returning incorrect value because of change in position

The obvious question is why does the data keep changing.

What does a typical JSON data value look like?

You cannot and should not rely on the ordering of elements within a JSON object.

From the JSON specification at http://www.json.org/:

"An object is an unordered set of name/value pairs"

As a consequence, JSON libraries are free to rearrange the order of the elements as they see fit. This is not a bug.

Hi Julian,
Sometimes there may be a "S" field JSON included in the dataset under certain rules within the code

week 1 for example :
query CAST(JSON_UNQUOTE(JSON_EXTRACT(`PF`.`PD`,  '$.UPA[3].amount'))  AS DECIMAL (10 , 2 )) AS `CMA`

"UPA":[
      {
         "name":"C",
         "amount":43.07
      },
      {
         "name":"A",
         "amount":"4.29"
      },
      {
         "name":"CM",
         "amount":3.2
      },
      {
         "name":"EI",
         "amount":"0.00"
      }
   ]

Select allOpen in new window

The value in the query CAST(JSON_UNQUOTE(JSON_EXTRACT(`PF`.`PD`,  '$.UPA[3].amount'))  AS DECIMAL (10 , 2 )) AS `CMA`
returns 3.2
week 2 for example :

"UPA":[
   {
      "name":"C",
      "amount":43.07
   },
   {
      "name":"S",
      "amount":0
   },
   {
      "name":"A",
      "amount":"4.29"
   },
   {
      "name":"CM",
      "amount":3.2
   },
   {
      "name":"EI",
      "amount":"0.00"
   }
]

Select allOpen in new window

returns 4.29 which is incorrect and is caused by the addition of the S element.

Is there a way of referencing the "CM:=" element and not its position?

Thanks

Formatted your JSON and added CODE tags - no point wasting time trying to see what's what in the post.

I would say that your JSON is not well constructed. You are not benefiting from named values - something like this might be better

"UPA":{
  "C":"43.07",
  "S":"0",
  "A":"4.29",
  "CM":"3.2",
  "EI":"0.00"
}

Select allOpen in new window

Hi Julian,
So what you are saying is that the field name can't be used as a reference?

It is not the field name you are asking about - it is the field value.
name is the field name 'A' and 'S' are values.

OK Julian thanks for the clarification.  
I would like to read the "CM" value independently of where it is in the JSON field.
Is this possible in MYSQL syntax ?
Thanks

Hi Julian, I managed to get the data out of the Json using your advice thank you

You are welcome.