mysql json search_关于数值的MySQL json_search

我有一个像这样的json列表

[{

"something": "bla",

"id": 2

}, {

"something": "yes",

"id": 1

}]

我的id字段始终是数值.但是当我尝试找到id = 2时,MySQL返回NULL

select

json_search(

json_extract(

'[{"something": "bla" ,"id": 2}, {"something": "yes","id": 1}]',

"$[*].id"

),

'one',

2

) as json_search;

json_search |

------------|

|

当我在我的json id对象中使用字符串作为值而不是数值时,我得到了索引0的结果.

select

json_search(

json_extract(

'[{"something": "bla" ,"id": "2"}, {"something": "yes","id": 1}]',

"$[*].id"

),

'one',

"2"

) as json_search;

json_search |

------------|

"$[0]" |

我正在使用MySQL 5.7.17

@@version |

-----------|

5.7.17-log |

MySQL中没有提供json数组中的数字搜索吗？

解决方法:

你可以尝试一些复杂的,不直观的,可能有性能问题,但它是一个选择：

mysql> SELECT JSON_SEARCH(

-> REPLACE(

-> REPLACE(

-> REPLACE(

-> JSON_EXTRACT('[

'> {"something": "bla" ,"id": 2},

'> {"something": "yes","id": 1}

'> ]', "$[*].id"),

-> ', ', '","'),

-> '[', '["'),

-> ']', '"]'),

-> 'one', '2') `json_search`;

+-------------+

| json_search |

+-------------+

| "$[0]" |

+-------------+

1 row in set (0.00 sec)

标签：json,mysql,sql

来源： https://codeday.me/bug/20190705/1391100.html