一、简介
JSON_EXTRACT() : Mysql 根据 Json 中指定的 key 取出对应的 value
JSON_UNQUOTE() : 取消引用 Json 值,返回字符串,如果参数为 null,则返回 null 值
二、使用案例
1、表结构 (F_transaction_data 字段类型为 JSON)
2、查询 SQL
SELECT
t1.F_merchant_id AS merchantId,
t1.F_transaction_data,
IFNULL(JSON_UNQUOTE(json_extract(t1.F_transaction_data, '$.t0')), 0) settleType,
JSON_UNQUOTE(json_extract(t1.F_transaction_data, '$.source_type')) AS sourceType,
JSON_UNQUOTE(json_extract(t1.F_transaction_data, '$.order_amount')) AS orderAmount,
JSON_UNQUOTE(json_extract(t1.F_transaction_data, '$.channel_order_id')) AS channelOrderId
FROM
lepay3.t_operation_info_10 t1
WHERE
t1.F_merchant_id = '4811013310'
AND F_trade_type = 1
AND F_operation_type = 2;
3、查询结果
4、F_transaction_data 的值