Mysql的JSON路径提取
问题描述:
表:相关信息Mysql的JSON路径提取
id | info
------ | ------
1 | {"v1": "test", "v2": "work"}
表:my_data
id | name | info_id
------ | ------ | ------
1 | john | 2
2 | peter | 1
3 | luc | 2
我想有以下输出:
name | art
------ | ------
john | work
peter | test
luc | work
我的问题是我不知道如何使用主查询的值作为json路径。
现在,我有这样的:
SELECT a.name, (SELECT info->>"$.v1" AS art FROM infos AS b) FROM my_data AS a
可以肯定这显示了所有 “测试”
的查询必须是这样的:
SELECT a.name, (SELECT info->>CONCAT("$.v", a.info_id) AS art FROM infos AS b) FROM my_data AS a
这可能吗?
答
你可以试试以下(根据需要调整):
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `my_data`, `infos`;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `infos` (
-> `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> `info` JSON NOT NULL
->);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `my_data` (
-> `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> `name` VARCHAR(255) NOT NULL,
-> `info_id` BIGINT UNSIGNED NOT NULL
->);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `infos`
-> (`info`)
-> VALUES
-> ('{"v1": "test", "v2": "work"}');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `my_data`
-> (`name`, `info_id`)
-> VALUES
-> ('john', 2),
-> ('peter', 1),
-> ('luc', 2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT
-> `md`.`name`,
-> JSON_UNQUOTE(
-> JSON_EXTRACT(
-> `i`.`info`,
-> CONCAT('$.v', `md`.`info_id`)
-> )
-> ) `art`
-> FROM
-> `my_data` `md`
-> INNER JOIN `infos` `i` ON `i`.`id` = 1;
+-------+------+
| name | art |
+-------+------+
| john | work |
| peter | test |
| luc | work |
+-------+------+
3 rows in set (0.00 sec)
+0
哇,这就是它。相当简单的方法。 不错。 – BobderHund
我们可以检查你正在使用的是哪个版本的MySQL吗?可能我是'旧学校'(我是),但我会非常想要*不*存储json数据。 – Strawberry
当然,版本是5.7.19 – BobderHund