MySQL 5.7.22:当它的键包含点字符时，如何选择它的值？

Question

因此，JSON结构如下所示：

{
  "pixel.uuid": "1ca86b7d7505c48363b2a5d9dde0c7e4",
  "innerWidth": "1440"
}

问:我如何选择"pixel.uuid"？我试过这些组合：

select json->"$.pixel.uuid" from TrackingData_experimental

select json->"$.pixel\.uuid" from TrackingData_experimental

select json->{"$.pixel.uuid"} from TrackingData_experimental

select JSON_EXTRACT(json, '$.pixel.uuid') from TrackingData_experimental

但运气不好。谢谢!

Answer 1

用双引号分隔键：

mysql> select json->'$."pixel.uuid"' from TrackingData_experimental;
+------------------------------------+
| json->'$."pixel.uuid"'             |
+------------------------------------+
| "1ca86b7d7505c48363b2a5d9dde0c7e4" |
+------------------------------------+

如果希望未引用的值返回，请执行以下操作：

mysql> select json->>'$."pixel.uuid"' from TrackingData_experimental;
+----------------------------------+
| json->>'$."pixel.uuid"'          |
+----------------------------------+
| 1ca86b7d7505c48363b2a5d9dde0c7e4 |
+----------------------------------+