查询mediumtext类型JSON字段中email_settings_id返回NULL的解决方法

核心问题分析 #

你的flow_settings字段是mediumtext类型，直接用JSON_EXTRACT会因为类型不匹配无法解析；同时email_settings_id嵌套在flow.steps数组的元素中，需要正确遍历数组才能定位到目标字段。

方案1：提取所有可能的email_settings_id（含数组格式） #

通过CAST将文本字段转为JSON类型，再用正确的JSON路径提取所有步骤中的email_settings_id：

select
    id,
    JSON_UNQUOTE(JSON_EXTRACT(CAST(flow_settings AS JSON), '$.flow.steps[*].settings.email_settings_id')) AS email_settings_ids
from smsbump.flows
where flows.flow_trigger in ('Synergy/TierExpiryReminder',
        'integrations/swell_tier_earned',
        'integrations/swell_birthday',
        'integrations/swell_points_reminder',
        'integrations/swell_redemption_reminder',
        'integrations/cdp_optin_status_changed',
        'integrations/cdp_redemption_created') and flows.channel in ('email', 'all');

• CAST(flow_settings AS JSON)：将mediumtext转为可解析的JSON类型
• $.flow.steps[*].settings.email_settings_id：[*]遍历steps数组的所有元素，定位到每个元素的settings.email_settings_id
• JSON_UNQUOTE：去掉提取结果的引号，得到纯数值

方案2：精准筛选邮件步骤的email_settings_id（推荐） #

如果只需要提取type为email的步骤对应的email_settings_id，可以用JSON_TABLE将JSON数组展开为关系表，再筛选目标数据：

select
    f.id,
    js.email_settings_id
from smsbump.flows f
join JSON_TABLE(
    CAST(f.flow_settings AS JSON),
    '$.flow.steps[*]' columns (
        step_type varchar(50) path '$.type',
        email_settings_id bigint path '$.settings.email_settings_id'
    )
) js on js.step_type = 'email'
where f.flow_trigger in ('Synergy/TierExpiryReminder',
        'integrations/swell_tier_earned',
        'integrations/swell_birthday',
        'integrations/swell_points_reminder',
        'integrations/swell_redemption_reminder',
        'integrations/cdp_optin_status_changed',
        'integrations/cdp_redemption_created') and f.channel in ('email', 'all');

• JSON_TABLE：把steps数组拆分成多行记录，每行对应一个步骤
• 通过step_type = 'email'筛选出邮件类型的步骤，直接获取对应的email_settings_id，避免返回无效的null值

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