Mysql之json字符串解析成对应字段

dkjhl

已于 2023-06-26 17:43:58 修改

阅读量2.5k

点赞数

分类专栏： mysql 文章标签： mysql json

于 2018-11-15 16:23:05 首次发布

本文链接：https://blog.csdn.net/dkjhl/article/details/84106337

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

mysql数据库中有一个字段，其对应内容为json字符串

append:

{"photos":[{"url":"shopping\/20180419\/d632d2661573bf10d0481e19685cb771.jpg","name":"TB2qHHTtFXXXXcdXXXXXXXXXXXX_!!0-saturn_solar.jpg_230x230.jpg_.webp.jpg","thumbnail":[{"width":"64","height":"64","filename":"thumb_64x64_d632d2661573bf10d0481e19685cb771.jpg","file_path":"shopping\/20180419\/thumb_64x64_d632d2661573bf10d0481e19685cb771.jpg"},{"width":"128","height":"128","filename":"thumb_128x128_d632d2661573bf10d0481e19685cb771.jpg","file_path":"shopping\/20180419\/thumb_128x128_d632d2661573bf10d0481e19685cb771.jpg"},{"width":"320","height":"320","filename":"thumb_320x320_d632d2661573bf10d0481e19685cb771.jpg","file_path":"shopping\/20180419\/thumb_320x320_d632d2661573bf10d0481e19685cb771.jpg"},{"width":"640","height":"640","filename":"thumb_640x640_d632d2661573bf10d0481e19685cb771.jpg","file_path":"shopping\/20180419\/thumb_640x640_d632d2661573bf10d0481e19685cb771.jpg"}]}]}

需求：将append字段中的单独解析出来

具体做法如下：

SELECT
	id,
	`name`,
	REPLACE (
		JSON_EXTRACT (append, '$.photos[0].url'),
		'"',
		''
	) AS 'url',
	single_price
FROM
	items
WHERE
	id = 2695

扩展：

如果需要对上述查询出来的结果进行拼接只需要concat即可

SELECT
	id,
	`name`,
	REPLACE (
		JSON_EXTRACT (append, '$.photos[0].url'),
		'"',
		''
	) AS url,
	single_price,
	"http://test.img/upload/" AS prefix,
	CONCAT(
		"http://test.img/upload/",
		REPLACE (
			JSON_EXTRACT (append, '$.photos[0].url'),
			'"',
			''
		)
	)
FROM
	items
WHERE
	id = 2695