MySQL查询以JSON字符串搜索字段 [英] MySQL query to search a field with JSON string

问题描述

这对于基本的SQL来说可能足够简单，或者可能需要REGEXP，但我遇到了麻烦.

This might be simple enough with basic SQL or it might need a REGEXP but I've hit a brick wall.

我的数据像以下两个示例一样存储在JSON字符串中(每个字段为1个字段):

My data is stored in a JSON string like these two examples (each in 1 field):

[{"id":"2","value":["1","3"]},{"id":"3","value":["1","2"]}]

和:

[{"id":"3","value":["2"]},{"id":"3","value":["1","2","5"]}]

我想在最后一个括号之间搜索值，该值可能由许多数字["1","2","5"] or just a single on ["2"]组成.开头的数字对应2个类别-单个"id":"2"和"id":"3".

I want to search for values in between those last brackets which might consist of many numbers ["1","2","5"] or just a single on ["2"]. The beginning numbers correspond to 2 categories - the single "id":"2" and "id":"3".

使用%"2"%和简单的LIKE当然可以匹配所有内容.我可以按"id":"$var"进行查询以返回每个类别，然后在获得结果后使用PHP对其进行过滤，但是数据可能很大，对于SQL专家来说，我相信这很容易.

Using %"2"% with a simple LIKE of course matches everything. I can query by the "id":"$var" to return each category then use PHP to filter it through after we have the results, but the data can be quite large and I'm sure it's easy for a SQL guru.

我没有选择更改字段格式的选项，它必须保留为JSON.

I don't have the option to change the format of the field, it has to remain as JSON.

任何帮助表示赞赏！谢谢.

Any help appreciated! Thanks.

推荐答案

我想我使用以下方法解决了它:AND extra_fields REGEXP '(.*"id":"2".*)("\[.*"1".*\]")'.正则表达式比MySQL:P

I think I solved it by using this: AND extra_fields REGEXP '(.*"id":"2".*)("\[.*"1".*\]")'. It's more to do with regular expressions than it is to do with MySQL :P

评论:(我找不到评论按钮)
当您了解"extra_fields"是表中列的名称时，这种语法会变得更加清晰

comment: (I couldn't find comment button)
This syntax becomes clearer when you learn that "extra_fields" is the name of the column in the table

这篇关于MySQL查询以JSON字符串搜索字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！