MySql query to retrieve value of element attribute of value-CodePudding

I have mysql table (say payment), holding gateway callback log as content (although as long text) in gateway_log column,

["call_result",{"payment_id":"5917457","payment_status":"waiting","pay_address":"TPQs3925hQu5WATNz3gVMiRwzoPjnVRBCJ5","price_amount":400,"price_currency":"usd","pay_amount":"399.354663","amount_received":396.460139,"pay_currency":"usdttrc20","order_id":264,"order_description":null,"ipn_callback_url":,"created_at":"2022-04-01T14:54:40.151Z","updated_at":"2022-04-01T14:54:40.151Z","purchase_id":"4941944696","smart_contract":null,"network":"trx","network_precision":null,"time_limit":null,"burning_percent":null}]

I have two column 1- amount 2-gateway_log In this table I have many records I want to put the amount_received information in the amount with sql query and update all the rows of this table, which has about 1000 records.

now, I want to retrieve the value of attributes ("amount_received" in this case). I know how to do it in MS SQL e.g:

SELECT ExtractValue(amount_received,'//amount_received/@??') as amount as name FROM payment WHERE payment_id='368'

But not able to figure it out in MySQL. Note: Tried MySQL function ExtractValue() but no success on retrieving element attributes. Thanks

CodePudding user response：

This works for MySQL (5.7 and above)

SELECT JSON_EXTRACT('["call_result", {
  "payment_id": "5917457",
  "payment_status": "waiting",
  "pay_address": "TPQs3925hQu5WATNz3gVMiRwzoPjnVRBCJ5",
  "price_amount": 400,
  "price_currency": "usd",
  "pay_amount": "399.354663",
  "amount_received": 396.460139,
  "pay_currency": "usdttrc20",
  "order_id": 264,
  "order_description": null,
  "ipn_callback_url": null,
  "created_at": "2022-04-01T14:54:40.151Z",
  "updated_at": "2022-04-01T14:54:40.151Z",
  "purchase_id": "4941944696",
  "smart_contract": null,
  "network": "trx",
  "network_precision": null,
  "time_limit": null,
  "burning_percent": null
}]', '$[1].amount_received')

Note that your JSON was invalid.

In a very similar manner you can UPDATE a value:

SELECT JSON_SET('["call_result", {
  "pay_amount": "399.354663",
  "amount_received": 396.460139,
}]', '$[1].amount_received', 396.2)

Or replace, search delete etc... see more on https://dev.mysql.com/doc/refman/8.0/en/json-functions.html