mysql json 数组_在MYSQL中读取JSON数组

mysql> SET @`json` :=

-> '[

'> {

'> "id": 1, "type": 2

'> },

'> {

'> "id": 2, "type": 1

'> }

'> ]';

Query OK, 0 rows affected (0.00 sec)

您可以在数组中获取所有ID：

mysql> SELECT JSON_EXTRACT(@`json` ,'$[*].id');

+----------------------------------+

| JSON_EXTRACT(@`json` ,'$[*].id') |

+----------------------------------+

| [1, 2] |

+----------------------------------+

1 row in set (0.00 sec)

可以访问每个JSON ID：

mysql> SELECT JSON_EXTRACT(@`json` ,'$[0].id');

+----------------------------------+

| JSON_EXTRACT(@`json` ,'$[0].id') |

+----------------------------------+

| 1 |

+----------------------------------+

1 row in set (0.00 sec)

尝试：

mysql> DROP PROCEDURE IF EXISTS `new_procedure`;

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `new_procedure`(`json` JSON)

-> BEGIN

-> DECLARE `json_items` BIGINT UNSIGNED DEFAULT JSON_LENGTH(`json`);

-> DECLARE `_index` BIGINT UNSIGNED DEFAULT 0;

->

-> DROP TEMPORARY TABLE IF EXISTS `jsonTemporary`;

->

-> CREATE TEMPORARY TABLE IF NOT EXISTS `jsonTemporary`

-> (`id` BIGINT UNSIGNED NOT NULL);

->

-> WHILE `_index` < `json_items` DO

-> INSERT INTO `jsonTemporary` (`id`)

-> VALUES (JSON_EXTRACT(`json`, CONCAT('$[', `_index`, '].id')));

-> SET `_index` := `_index` + 1;

-> END WHILE;

->

-> SELECT `id` FROM `jsonTemporary`;

-> DROP TEMPORARY TABLE IF EXISTS `jsonTemporary`;

-> END//

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `new_procedure`(@`json`);

+----+

| id |

+----+

| 1 |

| 2 |

+----+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)