JSON attribute returning incorrect value because of change in position

chrislindsay
chrislindsay used Ask the Experts™
on
Hi Experts,

I am trying to extract a specific attribute form a JSON field.  The problem I am having is that the attribute index occasionally flips to the [2] in the array.
CAST(JSON_UNQUOTE(JSON_EXTRACT(`PF`.`PD`,  '$.UP_A[3].amount'))  AS DECIMAL (10 , 2 )) AS `CMA`
So the question is.. Is there any way of referencing the JSON field name and not its position in the UP_A element of the array.
Thanks

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The obvious question is why does the data keep changing.

What does a typical JSON data value look like?
NoahHardware Tester and Debugger

Commented:
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.

Author

Commented:
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"
      }
   ]

Open 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"
   }
]

Open 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
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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"
}

Open in new window

Author

Commented:
Hi Julian,
So what you are saying is that the field name can't be used as a reference?
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
You can try the JSON_CONTAINS function

Author

Commented:
Hi Julian, I managed to get the data out of the Json using your advice thank you
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You are welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial