Трактовка значений JSON

[KarelWintersky]

Индекс определен так (незначимые поля опущены):

index test {  
        type                    = rt  
        rt_attr_json            = meta  
}  

В атрибут meta записывается такая структура:

$meta = [  
            'flags'    =>  [  
                'is_draft'      =>  (int)$item['s_draft'], // 0 или 1   
                'is_hidden'     =>  (int)$item['s_hidden'], // 0 или 1  
            ]  
        ];  

(потом json_encode INSERT)

А теперь попробуем поискать:

Manticore> select id, meta.flags from test where meta.flags = 1 limit 5;  
Empty set (0.023 sec)  
  
Manticore> show warnings;  
Query OK, 0 rows affected (0.000 sec)  

Предсказуемый результат

А так?

Manticore> select id, meta.flags from test where meta.flags = 0 limit 5;  
 ------- ------------------------------   
| id    | meta.flags                   |  
 ------- ------------------------------   
| 12621 | {"is_draft":0,"is_hidden":0} |  
|  4472 | {"is_draft":0,"is_hidden":0} |  
|  2032 | {"is_draft":0,"is_hidden":0} |  
|  8851 | {"is_draft":0,"is_hidden":0} |  
|   812 | {"is_draft":0,"is_hidden":0} |  
 ------- ------------------------------   
5 rows in set (0.042 sec)  
  
Manticore> show warnings;  
Query OK, 0 rows affected (0.000 sec)  

WTF?

[KarelWintersky]

Ожидаемое поведение во втором случае: empty set + warning типа "json set can't be compared with ordinal value"

P.S. Либо объявление этого фичей, упоминание в документации и возможность строить на этом определенную функциональность...

[sanikolaev]

MRE:

mysql> drop table if exists t; create table t(f text, j json); insert into t(j) values('{"a":{"b":0}}'); select * from t where j.a = 0; select * from t where j.a = 1;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t(f text, j json)
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
insert into t(j) values('{"a":{"b":0}}')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from t where j.a = 0
--------------

+---------------------+---------------+------+
| id                  | j             | f    |
+---------------------+---------------+------+
| 1514425997171097641 | {"a":{"b":0}} |      |
+---------------------+---------------+------+
1 row in set (0.00 sec)

--------------
select * from t where j.a = 1
--------------

Empty set (0.00 sec)

[githubmanticore]

➤ Sergey Nikolaev commented:

FFR: It works differently in MySQL and Postgres. TL;DR: json.a = 0 is different from json.a is null. 0 doesn't mean null.

mysql

mysql> use test; drop table if exists t; CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, json_data JSON NOT NULL); INSERT INTO t (json_data) VALUES ('{"a": 123}'); SELECT * FROM t WHERE json_data->>'$.a 
' = 123; SELECT * FROM t WHERE json_data->>'$.b' = 0; SELECT * FROM t WHERE json_data->>'$.b' is null; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Database changed 
-------------- 
drop table if exists t 
-------------- 
 
Query OK, 0 rows affected (0.13 sec) 
 
-------------- 
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, json_data JSON NOT NULL) 
-------------- 
 
Query OK, 0 rows affected (0.41 sec) 
 
-------------- 
INSERT INTO t (json_data) VALUES ('{"a": 123}') 
-------------- 
 
Query OK, 1 row affected (0.01 sec) 
 
-------------- 
SELECT * FROM t WHERE json_data->>'$.a' = 123 
-------------- 
 
+----+------------+ 
| id | json_data  | 
+----+------------+ 
|  1 | {"a": 123} | 
+----+------------+ 
1 row in set (0.00 sec) 
 
-------------- 
SELECT * FROM t WHERE json_data->>'$.b' = 0 
-------------- 
 
Empty set (0.00 sec) 
 
-------------- 
SELECT * FROM t WHERE json_data->>'$.b' is null 
-------------- 
 
+----+------------+ 
| id | json_data  | 
+----+------------+ 
|  1 | {"a": 123} | 
+----+------------+ 
1 row in set (0.00 sec) 

postgres

sn=# DROP TABLE IF EXISTS t; 
CREATE TABLE t ( 
  id SERIAL PRIMARY KEY, 
  json_data JSON NOT NULL 
); 
NOTICE:  table "t" does not exist, skipping 
DROP TABLE 
CREATE TABLE 
sn=# INSERT INTO t (json_data) 
VALUES ('{"a": 123}'); 
INSERT 0 1 
sn=# SELECT * FROM t WHERE json_data->>'a' = '123'; 
 id | json_data 
----+------------ 
  1 | {"a": 123} 
(1 row) 
 
sn=# SELECT * FROM t WHERE json_data->>'b' = '0'; 
 id | json_data 
----+----------- 
(0 rows) 
 
sn=# SELECT * FROM t WHERE json_data->>'b' IS NULL; 
 id | json_data 
----+------------ 
  1 | {"a": 123} 
(1 row)