狮身人面像搜索 json 值比较

Question

我正在使用狮身人面像2.2.2.INDEX表中的JSON功能，如下所示：

mysql> SELECT subsite_min_cnt, version_content_cnt FROM mobile_collection;
+------------------------+-----------------------------------------------------------+
| subsite_min_cnt        | version_content_cnt                                       |
+------------------------+-----------------------------------------------------------+
| {"85":3,"75":4,"65":5} | {"10003":4,"10008":5,"10009":5,"11000":7,"1":1,"10000":3} |
| {"85":6,"75":4,"65":5} | {"46":1,"201":1,"11000":1,"10010":1}                      |
+------------------------+-----------------------------------------------------------+

我使用来自 JSON 的两个值：

mysql> SELECT subsite_min_cnt.85 as a, version_content_cnt.10008 as b 
       FROM mobile_collection;
+------+------+
| a    | b    |
+------+------+
| 3    | 5    |
| 6    | NULL |
+------+------+

我尝试比较这两个值，这是我得到的（json_autoconv_numbers等于 1）：

mysql> SELECT subsite_min_cnt.85 as a, version_content_cnt.10008 as b 
       FROM mobile_collection WHERE b IS NOT NULL and a < b;
ERROR 1064 (42000): sphinxql: syntax error, unexpected IDENT, expecting CONST_INT (or 3 other tokens) near 'b'

或：

mysql> SELECT subsite_min_cnt.85 < version_content_cnt.10008 as b 
       FROM mobile_collection;
+------+
| b    |
+------+
| 0    |
| 0    |
+------+

所以，问题是：两个 json 值的比较在狮身人面像中有效吗？或者，也许，我以错误的方式比较项目...

Answer 1

尽管json_autoconv_numbers = 1，但类型转换解决了我的问题：

mysql> SELECT integer(subsite_min_cnt.85) < integer(version_content_cnt.10008) as c, subsite_min_cnt.85, version_content_cnt.10008 
       FROM mobile_collection where c > 0;
+------+--------------------+---------------------------+
| c    | subsite_min_cnt.85 | version_content_cnt.10008 |
+------+--------------------+---------------------------+
|    1 | 3                  | 5                         |
+------+--------------------+---------------------------+