返回顶部
首页 > 资讯 > 数据库 >使用MySQL从JSON字符串提取数据的方法详解
  • 518
分享到

使用MySQL从JSON字符串提取数据的方法详解

MySQL从JSON提取数据MySQLJSON提取数据MySQL提取数据MySQLJSON 2024-10-22 21:10:41 518人浏览 八月长安
摘要

目录1. 背景知识2. 示例数据3. sql 查询分析3.1 查询结构解析4. 查询结果5. 性能考虑6. 总结1. 背景知识 JSON(javascript Object Notation)是一种轻量级的数据交换格式,

1. 背景知识

JSONjavascript Object Notation)是一种轻量级的数据交换格式,易于阅读和编写,同时也易于机器解析和生成。Mysql 从版本 5.7 开始支持 jsON 数据类型,使得在数据库中存储和操作 JSON 数据成为可能。

在许多应用中,JSON 字符串可能存储在表的某个字段中,我们需要提取和转换这些数据以便进行进一步分析或展示。

2. 示例数据

假设我们在 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}]

3. SQL 查询分析

以下是实现这一转换的 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;

3.1 查询结构解析

  1. 内层查询

    • 去除多余字符:首先,使用 REPLACE 函数将 lct 字段中的 {} 和 " 去掉。这样可以简化后续处理。
    • 分割字符串:使用 SUBSTRING_INDEX 将每个键值对分割开。我们通过一个数字表(1到10)来实现。数字表的作用是帮助我们迭代处理每个键值对,因为我们无法预先知道 JSON 中键值对的数量。
SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(lct, '{', ''), '}', ''), '"', ''), ',', numbers.n), ',', -1) AS kv
  1. 这段代码将 JSON 字符串拆分为多个键值对,kv 列中将包含这样的值,例如:

    • 15775d64e52c4ba3a8eef4bafc5f40e5:875 162
    • 75b67fab657748a9ab4bba141bfa0d36:375 98
    • 428299fd90814b3eaf129e8246f82b2a:155 126
  2. 中层查询

    • 在此查询中,我们会对 kv 列进行进一步处理。使用 TRIM(BOTH '"' FROM kv) 去掉多余的引号,以确保后续操作不会受到影响。
SELECT
    TRIM(BOTH '"' FROM kv) AS kv
  • 外层查询
    • 聚合和格式化:在外层查询中,我们使用 GROUP_CONCAT 聚合所有的 kv 对,并使用 CONCAT 生成目标格式的 JSON 字符串。
    • 提取数据:使用 SUBSTRING_INDEX 提取 idx 和 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),
        '}'
    )
)
    • 最终结果:最终的结果将是一个字符串,格式为 JSON 数组。

4. 查询结果

运行上述查询后,您将得到所需的结果格式:

[{"id":"15775d64e52c4ba3a8eef4bafc5f40e5","x":875,"y":162},{"id":"75b67fab657748a9ab4bba141bfa0d36","x":375,"y":98},{"id":"428299fd90814b3eaf129e8246f82b2a","x":155,"y":126}]

5. 性能考虑

  • 字符长度计算CHAR_LENGTH(lct) - CHAR_LENGTH(REPLACE(lct, ',', '')) 的计算用于确保我们只处理存在的键值对。此方法对性能有一定影响,特别是对于大文本。
  • 数字表的使用:由于 JSON 的结构可能变化,数字表的使用可以扩展以支持更多的键值对。在实际应用中,您可以根据需要增加数字的范围。

6. 总结

通过上述 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

猜你喜欢
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作