目录1. 背景知识2. 示例数据3. sql 查询分析3.1 查询结构解析4. 查询结果5. 性能考虑6. 总结1. 背景知识 JSON(javascript Object Notation)是一种轻量级的数据交换格式,
JSON(javascript Object Notation)是一种轻量级的数据交换格式,易于阅读和编写,同时也易于机器解析和生成。Mysql 从版本 5.7 开始支持 jsON 数据类型,使得在数据库中存储和操作 JSON 数据成为可能。
在许多应用中,JSON 字符串可能存储在表的某个字段中,我们需要提取和转换这些数据以便进行进一步分析或展示。
假设我们在 wf_lcdy
表中有一个字段 lct
,其中存储了如下 JSON 字符串:
{"15775d64e52c4ba3a8eef4bafc5f40e5":"875 162","75b67fab657748a9ab4bba141bfa0d36":"375 98","428299fd90814b3eaf129e8246f82b2a":"155 126"}
我们希望将其转换为以下格式的数组:
[{"id":"15775d64e52c4ba3a8eef4bafc5f40e5","x":875,"y":162},{"id":"75b67fab657748a9ab4bba141bfa0d36","x":375,"y":98},{"id":"428299fd90814b3eaf129e8246f82b2a","x":155,"y":126}]
以下是实现这一转换的 SQL 查询:
SELECT
CONCAT('[', GROUP_CONCAT(
CONCAT(
'{"id":"',
SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', 1), '"', -1),
'", "x":',
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', 1) AS UNSIGNED),
', "y":',
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', -1) AS UNSIGNED),
'}'
)
), ']') AS result
FROM (
SELECT
TRIM(BOTH '"' FROM kv) AS kv
FROM (
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(lct, '{', ''), '}', ''), '"', ''), ',', numbers.n), ',', -1) AS kv
FROM wf_lcdy
JOIN (
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10
) numbers
WHERE CHAR_LENGTH(lct) - CHAR_LENGTH(REPLACE(lct, ',', '')) >= numbers.n - 1 AND ID = '0c86346993D64d98ad17892974bf8963'
) AS temp
) AS kv_pairs;
内层查询:
REPLACE
函数将 lct
字段中的 {
、}
和 "
去掉。这样可以简化后续处理。SUBSTRING_INDEX
将每个键值对分割开。我们通过一个数字表(1到10)来实现。数字表的作用是帮助我们迭代处理每个键值对,因为我们无法预先知道 JSON 中键值对的数量。SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(lct, '{', ''), '}', ''), '"', ''), ',', numbers.n), ',', -1) AS kv
这段代码将 JSON 字符串拆分为多个键值对,kv
列中将包含这样的值,例如:
15775d64e52c4ba3a8eef4bafc5f40e5:875 162
75b67fab657748a9ab4bba141bfa0d36:375 98
428299fd90814b3eaf129e8246f82b2a:155 126
中层查询:
kv
列进行进一步处理。使用 TRIM(BOTH '"' FROM kv)
去掉多余的引号,以确保后续操作不会受到影响。SELECT
TRIM(BOTH '"' FROM kv) AS kv
GROUP_CONCAT
聚合所有的 kv
对,并使用 CONCAT
生成目标格式的 JSON 字符串。SUBSTRING_INDEX
提取 id
、x
和 y
的值,并将它们转换为相应的格式。这里的关键在于分割字符串并提取数字。GROUP_CONCAT(
CONCAT(
'{"id":"',
SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', 1), '"', -1),
'", "x":',
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', 1) AS UNSIGNED),
', "y":',
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', -1) AS UNSIGNED),
'}'
)
)
运行上述查询后,您将得到所需的结果格式:
[{"id":"15775d64e52c4ba3a8eef4bafc5f40e5","x":875,"y":162},{"id":"75b67fab657748a9ab4bba141bfa0d36","x":375,"y":98},{"id":"428299fd90814b3eaf129e8246f82b2a","x":155,"y":126}]
CHAR_LENGTH(lct) - CHAR_LENGTH(REPLACE(lct, ',', ''))
的计算用于确保我们只处理存在的键值对。此方法对性能有一定影响,特别是对于大文本。通过上述 SQL 查询,我们成功地从一个包含 JSON 字符串的字段中提取了数据并转换成了另一种结构化格式。这种方法展示了 mysql 在处理 JSON 数据方面的灵活性和强大能力。
在实际应用中,您可以根据具体的需求对查询进行适当的修改,以适应不同结构的 JSON 数据。此外,了解 SQL 中字符串处理和聚合函数的使用,对于提升数据处理的能力和效率至关重要。希望本篇文章对您在处理 JSON 数据时有所帮助!
以上就是使用MySQL从JSON字符串提取数据的方法详解的详细内容,更多关于MySQL JSON提取数据的资料请关注编程网(www.lsjlt.com)其它相关文章!
--结束END--
本文标题: 使用MySQL从JSON字符串提取数据的方法详解
本文链接: https://www.lsjlt.com/news/623946.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0