mysql 处理json数组

最新推荐文章于 2024-08-22 09:06:15 发布

weixin_38081382

最新推荐文章于 2024-08-22 09:06:15 发布

阅读量2k

点赞数 1

分类专栏： mysql 文章标签： mysql json 数据库

本文链接：https://blog.csdn.net/weixin_38081382/article/details/127565739

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

数据准备

创建表

CREATE TABLE `json` (
  `id` int NOT NULL,
  `json` varchar(2550) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

插入数据

INSERT INTO `json_db`.`json` (`id`, `json`) VALUES (1, '[{\"name\":\"keyword\",\"type\":\"String\",\"property\":\"必填\",\"manual\":\"请求的公司代码\"},{\"name\":\"depth\",\"type\":\"number\",\"property\":\"选填\",\"manual\":\"请求的深度，最大三层，默认 3\"},{\"name\":\"fields\",\"type\":\"String\",\"property\":\"选填\",\"manual\":\"请求的数据字段\"},{\"name\":\"size\",\"type\":\"number\",\"property\":\"选填\",\"manual\":\"单次查询最大返回记录数\"},{\"name\":\"from\",\"type\":\"number\",\"property\":\"选填\",\"manual\":\"查询跳过的记录数\"},{\"name\":\"token\",\"type\":\"String\",\"property\":\"必填\",\"manual\":\"默认有效期 2 小时\"}]');

1.mysql版本5.7 JSON_EXTRACT

SELECT
	a.jsonarr,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ) AS jsonarr_info,
	b.help_topic_id,
	REPLACE (JSON_EXTRACT( SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].name' ),'"','' ) AS name,
	REPLACE (JSON_EXTRACT( SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].type' ),'"','' ) AS type ,
	REPLACE (JSON_EXTRACT( SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].property' ),'"','' ) AS property,
	REPLACE (JSON_EXTRACT( SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].long' ),'"','' ) AS long1,
	REPLACE (JSON_EXTRACT( SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].manual' ),'"','' ) AS manual,
	REPLACE (JSON_EXTRACT( SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].state' ),'"','' ) AS state
FROM
	( SELECT REPLACE ( REPLACE ( REPLACE ( json, "},{", "};{" ), "]", "" ), "[", "" ) AS jsonarr FROM json  ) a
JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.jsonarr ) - length( REPLACE ( a.jsonarr, ";", "" ) ) + 1 ) ;

结果：

2.mysql版本8.0 可以直接使用JSON_TABLE

select @json := json from json ;
	
SELECT * FROM JSON_TABLE(
	 @json
	 ,"$[*]"
COLUMNS(
name VARCHAR(255) PATH "$.name",
type VARCHAR(100) PATH "$.type",
manual VARCHAR(100) PATH "$.manual",
property VARCHAR(100) PATH "$.property"
)
) AS tt;

结果：