如何在SQL中查询记录并对JSON列指定字段截取括号内容

假设表A的JSON列名为config_json，以下针对主流数据库提供实现方案：

MySQL 方案 #

利用JSON_REPLACE修改JSON字段，配合正则表达式提取所有括号包裹的内容：

SELECT 
  JSON_REPLACE(
    JSON_REPLACE(
      config_json,
      '$.NameEn',
      (SELECT GROUP_CONCAT(match_str SEPARATOR ' ') FROM (
        SELECT REGEXP_SUBSTR(JSON_UNQUOTE(JSON_EXTRACT(config_json, '$.NameEn')), '\\([^)]+\\)', 1, n) AS match_str
        FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) nums
        WHERE REGEXP_SUBSTR(JSON_UNQUOTE(JSON_EXTRACT(config_json, '$.NameEn')), '\\([^)]+\\)', 1, n) IS NOT NULL
      ) AS matches)
    ),
    '$.NameAr',
    (SELECT GROUP_CONCAT(match_str SEPARATOR ' ') FROM (
      SELECT REGEXP_SUBSTR(JSON_UNQUOTE(JSON_EXTRACT(config_json, '$.NameAr')), '\\([^)]+\\)', 1, n) AS match_str
      FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) nums
      WHERE REGEXP_SUBSTR(JSON_UNQUOTE(JSON_EXTRACT(config_json, '$.NameAr')), '\\([^)]+\\)', 1, n) IS NOT NULL
    ) AS matches)
  ) AS processed_config
FROM A;

逻辑说明：

• 用JSON_EXTRACT+JSON_UNQUOTE取出原始字段值
• 通过REGEXP_SUBSTR循环提取所有(...)格式的片段
• 用GROUP_CONCAT把多个片段拼接成最终字符串
• 最后用JSON_REPLACE替换回JSON对应字段

PostgreSQL 方案 #

基于jsonb_set和正则匹配函数实现：

SELECT 
  jsonb_set(
    jsonb_set(
      config_json::jsonb,
      '{NameEn}',
      to_jsonb((SELECT string_agg(match[1], ' ') FROM regexp_matches(config_json::jsonb->>'NameEn', '\\([^)]+\\)', 'g') AS match))
    ),
    '{NameAr}',
    to_jsonb((SELECT string_agg(match[1], ' ') FROM regexp_matches(config_json::jsonb->>'NameAr', '\\([^)]+\\)', 'g') AS match))
  ) AS processed_config
FROM A;

逻辑说明：

• 将JSON转为jsonb类型方便操作
• regexp_matches加g参数全局匹配所有括号片段
• string_agg拼接匹配结果
• jsonb_set更新JSON字段中的对应值

SQL Server 方案 #

结合JSON_MODIFY和字符串处理函数：

WITH NameParts AS (
  SELECT 
    id,
    config_json,
    (SELECT STRING_AGG(value, ' ') FROM STRING_SPLIT(REPLACE(REPLACE(JSON_VALUE(config_json, '$.NameEn'), '(', '|('), ')', ')|'), '|') WHERE value LIKE '(%)') AS new_NameEn,
    (SELECT STRING_AGG(value, ' ') FROM STRING_SPLIT(REPLACE(REPLACE(JSON_VALUE(config_json, '$.NameAr'), '(', '|('), ')', ')|'), '|') WHERE value LIKE '(%)') AS new_NameAr
  FROM A
)
SELECT 
  JSON_MODIFY(JSON_MODIFY(config_json, '$.NameEn', new_NameEn), '$.NameAr', new_NameAr) AS processed_config
FROM NameParts;

逻辑说明：

• 用JSON_VALUE取出原始字段值，通过替换字符拆分出括号片段
• STRING_SPLIT拆分后筛选出符合(%)格式的内容，再用STRING_AGG拼接
• JSON_MODIFY两次调用更新两个字段值

注：如果括号嵌套层级较深，上述正则可能需要调整，当前方案仅处理单层括号的情况。

内容的提问来源于stack exchange，提问作者Faraz