mysql - 如何在mysql中搜索json数据？

Question

我有描述字段。该字段为json

描述字段的内容如下:

{
    "address": 
    {
        "street": "street 123", 
        "information": 
        {
            "province": "province 123",
            "regency": "regency 123", 
            "district": "district 123"
        }
    }, 
    "mobileNumber": "123"
}

我想在json数据中搜索省份

我尝试这样:

SELECT * FROM orders WHERE LOWER(JSON_EXTRACT(description, "$.address.information.province")) LIKE "%prov%"

结果为空

如何解决这个问题？

Answer 1

问题是您的 JSON 无效。

后面不能有逗号

"district": "district 123", 

和

"mobileNumber": "123",

如果您尝试使用有效的 JSON，您将得到预期的结果:

select json_extract('{ "address": { "street": "street 123",         "information": { "province": "province 123",  "regency": "regency 123",             "district": "district 123" } }, "mobileNumber": "123"}', '$.address.information.province')

"province 123"

我创建了一个db-fiddle这似乎适合您的数据:

create table x (id int, description json);
insert into x values(1, '{    "address":     {        "street": "street 123",         "information":         {            "province": "province 123",            "regency": "regency 123",             "district": "district 123"         }    },     "mobileNumber": "123"}');
select * from x where json_extract(description, '$.address.information.province') like '%prov%'

输出

id  description
1   {"address": {"street": "street 123", "information": {"regency": "regency 123", "district": "district 123", "province": "province 123"}}, "mobileNumber": "123"}