mysql where子句上的json字段 [英] json field on mysql where clause

问题描述

如何获取包含apiData和"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"}}

此查询有效，但如果其他选项包含"method":"transfer"，则可以获取.它不应该获取数据.如果它具有apiData选项并且在"method":"transfer"内部，则应显示:

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\".*)'

推荐答案

这应该有效 如您所说，您的方法:"transfer"应该位于apiData中，我已经根据您给定的字符串进行了regexp.

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]*}'

这篇关于mysql where子句上的json字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！