如何用SQL从字典格式列中提取hotel_id字段值？

你的问题核心在于用固定字符位置截取的方式处理类JSON数据，这种方法容错性极低，遇到空格、格式变动或脏数据就会失效。正确的做法是利用数据库的JSON解析函数，先修复数据格式（示例数据存在格式错误：开头用[而非{、部分数据未闭合），再按结构提取字段。

以下是主流数据库的针对性解决方案：

1. MySQL/MariaDB #

基础结构提取（适配示例1的search_params） #

SELECT 
  JSON_UNQUOTE(
    JSON_EXTRACT(
      -- 修复格式：替换开头的[为{，补全末尾缺失的闭合}
      REGEXP_REPLACE(REPLACE(info, '[', '{'), '[^}]+$', ''),
      '$.hotel_id'
    )
  ) AS hotel_id
FROM df;

嵌套结构提取（适配示例2的search_result.hotel_dict） #

SELECT 
  JSON_UNQUOTE(
    JSON_EXTRACT(
      REGEXP_REPLACE(REPLACE(info, '[', '{'), '[^}]+$', ''),
      '$.search_result.hotel_dict.*.hotel_id'
    )
  ) AS hotel_id
FROM df;

2. PostgreSQL #

基础结构提取 #

SELECT 
  (
    jsonb_path_query(
      -- 修复格式后转换为jsonb类型
      replace(regexp_replace(info, '^\[', '{'), '[^}]+$', '')::jsonb,
      '$.hotel_id'
    )
  )::text AS hotel_id
FROM df;

嵌套结构提取 #

SELECT 
  (
    jsonb_path_query(
      replace(regexp_replace(info, '^\[', '{'), '[^}]+$', '')::jsonb,
      '$.search_result.hotel_dict.*.hotel_id'
    )
  )::text AS hotel_id
FROM df;

3. SQL Server #

基础结构提取 #

SELECT 
  JSON_VALUE(
    -- 修复格式：替换开头的[为{，截取到hotel_id所在的闭合}位置
    REPLACE(LEFT(info, CHARINDEX('}', info, CHARINDEX('hotel_id', info))), '[', '{'),
    '$.hotel_id'
  ) AS hotel_id
FROM df;

嵌套结构提取 #

SELECT 
  JSON_VALUE(
    REPLACE(LEFT(info, CHARINDEX('}', info, CHARINDEX('hotel_id', info))), '[', '{'),
    '$.search_result.hotel_dict.*.hotel_id'
  ) AS hotel_id
FROM df;

脏数据额外处理技巧 #

如果数据中存在HTML转义的引号（"），需先替换为标准双引号：

-- 以MySQL为例，修复格式前先替换转义引号
REGEXP_REPLACE(REPLACE(REPLACE(info, '"', '"'), '[', '{'), '[^}]+$', '')

若需统一hotel_id类型（如字符串转数字），可使用CAST函数：

-- MySQL示例
CAST(JSON_UNQUOTE(JSON_EXTRACT(...)) AS UNSIGNED) AS hotel_id

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