mysql where查询字段是json,mysql where子句上的json字段

How can I get data that contains apiData and inside "method":"transfer"

My field name is detail and it contains this data:

{"paymentMethodData":{"id":"3","api_id":"2","account_id":null,"account_ids":null,"installment_state":null,"name_1":"Havale"},"apiData":{"id":"2","method":"transfer","name":"Havale"}}

This query works but if other options contains "method":"transfer", it gets. It shouldn't get data. If it has apiData option and inside "method":"transfer", it should get:

SELECT * FROM task_payment_actions AS tpa

INNER JOIN task_payment_action_detail AS tpad ON tpad.task_payment_action_id=tpa.id

WHERE tpad.detail REGEXP '(.*\"method\":\"transfer\".*)'

解决方案

This should work

As you said your method:"transfer" should be inside apiData I have make regexp according to your given string.

REGEXP '.*"apiData":{[",a-zA-Z0-9]*"method":"transfer"'

REGEXP '.*"apiData":{[",a-zA-Z0-9]*"method":"transfer"[",a-zA-Z0-9]*}'