表:信息
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)
参见数据库小提琴。