在 MYSQL 中读取 JSON 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46218021/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read JSON array in MYSQL
提问by monchyrcg
I'm unable to extract determinate data in a JSON.
我无法在 JSON 中提取确定的数据。
I have this JSON:
我有这个 JSON：
[{"id":1, "type":2}, {"id":2, "type":1}]
I want to recover all the ids in a variable in my stored procedure.
我想恢复存储过程中变量中的所有 ID。
CREATE DEFINER=`root`@`%` PROCEDURE `new_procedure`(a JSON)
BEGIN
SELECT JSON_TYPE(a);
-- return ARRAY
SELECT a,JSON_EXTRACT(a,'$.id');
END
areturn the JSON, but JSON_EXTRACTis empty
a返回 JSON，但JSON_EXTRACT为空
Even I prove to save the JSON in a temporary table
即使我证明将 JSON 保存在临时表中
CREATE DEFINER=`root`@`%` PROCEDURE `new_procedure`(a JSON)
BEGIN
SELECT JSON_TYPE(a);
DROP TEMPORARY TABLE IF EXISTS jsonTemporary;
CREATE TEMPORARY TABLE jsonTemporary SELECT a;
SELECT *,a->'$.id',a->>'$.id',JSON_EXTRACT(a,'$.id') FROM jsonTemporary;
END
But the result is the same only the first column return something.
但结果是相同的，只有第一列返回了一些东西。
回答by wchiquito
mysql> SET @`json` :=
-> '[
'> {
'> "id": 1, "type": 2
'> },
'> {
'> "id": 2, "type": 1
'> }
'> ]';
Query OK, 0 rows affected (0.00 sec)
You can get all idsin an array:
您可以将所有内容ids放在一个数组中：
mysql> SELECT JSON_EXTRACT(@`json` ,'$[*].id');
+----------------------------------+
| JSON_EXTRACT(@`json` ,'$[*].id') |
+----------------------------------+
| [1, 2] |
+----------------------------------+
1 row in set (0.00 sec)
Can access each JSON id:
可以访问每个 JSON id：
mysql> SELECT JSON_EXTRACT(@`json` ,'$[0].id');
+----------------------------------+
| JSON_EXTRACT(@`json` ,'$[0].id') |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
Try:
尝试：
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)
See db-fiddle.
