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

1. 背景知识

json（javascript 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 查询结构解析

内层查询：
- 去除多余字符：首先，使用 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),
        '}'
    )
)

- 最终结果：最终的结果将是一个字符串，格式为 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 的结构可能变化，数字表的使用可以扩展以支持更多的键值对。在实际应用中，您可以根据需要增加数字的范围。