JSON attribute returning incorrect value because of change in position

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
chrislindsayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

What does a typical JSON data value look like?
0
An Average Forum Participant Just For FunHardware Tester and DebuggerCommented:
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.
0
chrislindsayAuthor 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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Julian HansenCommented:
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

1
chrislindsayAuthor Commented:
Hi Julian,
So what you are saying is that the field name can't be used as a reference?
0
Julian HansenCommented:
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.
0
chrislindsayAuthor 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
0
Julian HansenCommented:
You can try the JSON_CONTAINS function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chrislindsayAuthor Commented:
Hi Julian, I managed to get the data out of the Json using your advice thank you
0
Julian HansenCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSON

From novice to tech pro — start learning today.