请教一个 JSON_EXTRACT 的问题

[meathill (Meathill Zhai)]

请教大家一个 SQL 里使用 JSON_EXTRACT 的问题：为啥这条 sql 能选到第二条数据呢？

[wangccsy (Ti D Ber Wc Tp L Gn I)]

你的前面有个.啊。

[meathill (Meathill Zhai)]

不好意思，请问 . 具体指什么？是 $.name 么？

[小龙虾爱大龙虾 (Minghao Ren)]

隐式转换的问题，你不加coalesce比默认是吧后边字符串转为json类型比的，在你的SQL里是转为varchar比的，但是两边还有引号，就比不上，把引号去掉就比上了

MySQL [test]> explain analyze select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where json_extract(info,'$.name')='Barney';
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                        | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                      | operator info                                                                                                                                    | memory    | disk |
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_4              | 0.80    | 1       | root      |               | time:688µs, loops:2, RU:0.475726, Concurrency:OFF                                                                                                                                                                                   | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info | 636 Bytes | N/A  |
| └─TableReader_7           | 0.80    | 1       | root      |               | time:649.9µs, loops:2, cop_task: {num: 1, max: 572.1µs, proc_keys: 0, tot_proc: 2.18µs, tot_wait: 56.6µs, rpc_num: 1, rpc_time: 542.8µs, copr_cache_hit_ratio: 1.00, build_task_duration: 8.57µs, max_distsql_concurrency: 1}       | data:Selection_6                                                                                                                                 | 297 Bytes | N/A  |
|   └─Selection_6           | 0.80    | 1       | cop[tikv] |               | tikv_task:{time:12ms, loops:1}, scan_detail: {get_snapshot_time: 21.3µs, rocksdb: {block: {}}}                                                                                                                                      | eq(json_extract(test.t5.info, "$.name"), cast("Barney", json BINARY))                                                                            | N/A       | N/A  |
|     └─TableFullScan_5     | 1.00    | 1       | cop[tikv] | table:t5      | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                        | keep order:false, stats:pseudo                                                                                                                   | N/A       | N/A  |
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
4 rows in set (0.01 sec)

MySQL [test]> select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(json_extract(info,'$.name'),'') not in ('Barney');
+-----------------------------+------------------------------------------+-------------------------------+
| json_extract(info,'$.name') | coalesce(json_extract(info,'$.name'),'') | info                          |
+-----------------------------+------------------------------------------+-------------------------------+
| "Barney"                    | "Barney"                                 | {"id": "3", "name": "Barney"} |
+-----------------------------+------------------------------------------+-------------------------------+
1 row in set (0.01 sec)

MySQL [test]> explain select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(json_extract(info,'$.name'),'') not in ('Barney');
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id                        | estRows | task      | access object | operator info                                                                                                                                    |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4              | 0.80    | root      |               | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info |
| └─TableReader_7           | 0.80    | root      |               | data:Selection_6                                                                                                                                 |
|   └─Selection_6           | 0.80    | cop[tikv] |               | ne(coalesce(cast(json_extract(test.t5.info, "$.name"), var_string(16777216)), ""), "Barney")                                                     |
|     └─TableFullScan_5     | 1.00    | cop[tikv] | table:t5      | keep order:false, stats:pseudo                                                                                                                   |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)


MySQL [test]> select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(JSON_UNQUOTE(json_extract(info,'$.name')),'') not in ('Barney');
Empty set (0.00 sec)


MySQL [test]> explain select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(JSON_UNQUOTE(json_extract(info,'$.name')),'') not in ('Barney');
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id                        | estRows | task      | access object | operator info                                                                                                                                    |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4              | 0.80    | root      |               | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info |
| └─TableReader_7           | 0.80    | root      |               | data:Selection_6                                                                                                                                 |
|   └─Selection_6           | 0.80    | cop[tikv] |               | ne(coalesce(json_unquote(cast(json_extract(test.t5.info, "$.name"), var_string(16777216))), ""), "Barney")                                       |
|     └─TableFullScan_5     | 1.00    | cop[tikv] | table:t5      | keep order:false, stats:pseudo                                                                                                                   |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

[有猫万事足]

[forever]

牛，这个没想到

[meathill (Meathill Zhai)]

@小龙虾爱大龙虾

MySQL [test]> explain select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(JSON_UNQUOTE(json

感谢指教，原来还有这一层，我以为提取出来就是普通字符了，因为肉眼看起来没有引号。

[小于同学]

学到了