SphinxQL比较json属性

Question

我有一个json字符串，如下所示：

{"mo":[{"from":800,"to":1100},{"from":1400,"to":1700}],"di":[{"from":800,"to":1100},{"from":1400,"to":1700}],"do":[{"from":800,"to":1100},{"from":1400,"to":1700}],"mi":[{"from":800,"to":1100}],"fr":[{"from":800,"to":1300}],"so":[],"sa":[]}

包含此json字符串的mysql字段在配置中声明为sql_attr_json。

以下WHERE条件运行良好：

WHERE json.mo[0].from < 1100

然而，以下并不是：

WHERE json.mo[0].from < json.mo[0].to

其结果是：

sphinxql: syntax error, unexpected IDENT near 'json.mo[0].to

为什么会这样呢？我正在努力实现对营业时间的检查，但不幸的是，我很难做到这一点，我尝试了十几种不同的方法，但似乎没有任何效果。

更新：

我也在努力：

WHERE (DATE_FORMAT(NOW(),'%H%i') between json.mo[0].from and json.mo[0].to) 

这会导致

sphinxql: syntax error, unexpected '(' near '(NOW(),'%H%i')) between json.mo[0].from and json.mo[0].to)

甚至是一个基本的

SELECT DATE_FORMAT(NOW(),'%H%i') < 1050

结果相同的错误

 Sphinx expr: syntax error, unexpected '(', expecting $end near '(NOW(),'%H%i') < 1050'

作为一种解决办法，我也尝试过

SELECT CONCAT(HOUR(NOW()), MINUTE(NOW())) currentTime;

但这会导致

sphinxql: syntax error, unexpected $end, expecting FROM or ',' near 'currentTime'

在MySQL中，它工作得很好

Answer 1

只是还没实现。尝试以下解决方法：

mysql> select *, INTEGER(j.mo[0].to) - INTEGER(j.mo[0].from) diff from rt where diff > 0\G
*************************** 1. row ***************************
  id: 1
   s:
   j: {"mo":[{"from":800,"to":1100},{"from":1400,"to":1700}],"di":[{"from":800,"to":1100},{"from":1400,"to":1700}],"do":[{"from":800,"to":1100},{"from":1400,"to":1700}],"mi":[{"from":800,"to":1100}],"fr":[{"from":800,"to":1300}],"so":[],"sa":[]}
diff: 300
1 row in set (0.00 sec)

在Manticore搜索(狮身人面像叉)中我工作得很好，在狮身人面像中也应该很好。