通过主查询值提取 MySQL JSON 路径

Question

表格:信息

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

这可能吗？

Answer 1

您可以尝试以下方法(根据需要进行调整):

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)

参见db-fiddle .