SQL JSON 在大数据场景下应用实践

应先用json_valid()或try_json()校验json合法性，再用->>提取去引号值，hive用get_json_object()需注意嵌套限制，避免explode()导致笛卡尔积，跨引擎需统一类型和格式，索引需匹配虚拟列路径。

sql 查询里怎么安全地解析 json 字段

直接用 JSON_EXTRACT 或 -> 操作符读取 JSON 字段，但字段为空或格式错乱时会静默返回 NULL，查不到数据还难定位。尤其在 Hive、spark SQL 或 mysql 5.7+ 中，JSON 字段常来自日志埋点或上游 etl，结构松散是常态。

• 先用 JSON_VALID()（MySQL）或 try_json()（Trino/Spark）兜底过滤非法 JSON，避免整个任务因单条脏数据失败
• 路径表达式别硬写 $.user.id，优先用 ->>（去引号）而非 ->（带引号），否则字符串值会多一层双引号，后续 WHERE 匹配容易失效
• Hive 不支持原生 JSON 函数，得靠 get_json_object()，它对嵌套数组支持弱，遇到 $.items[0].name 这种要拆成两层 UDTF 才行

JSON 膨胀导致 JOIN 性能断崖下跌

把 JSON 数组展开成行（比如用户标签列表、订单商品明细）再 JOIN，很容易触发笛卡尔爆炸。Spark 中 explode() 后数据量翻几倍，Shuffle 压力陡增，任务卡在 Stage 2 是典型信号。

• 能不下推就不下推：优先在应用层或预处理作业里把 JSON 展开并物化成宽表，别每次查询都 LATERAL VIEW explode()
• 必须实时展开时，给 explode() 加 size() 限制，比如只取前 5 个标签：explode(slice(json_array, 1, 5))
• MySQL 8.0 的 JSON_TABLE() 看似优雅，但执行计划里常转成临时表，大表关联时比手写 JOIN + 子查询还慢，实测 100 万行以上慎用

跨引擎 JSON 兼容性陷阱：从 Spark 写入到 Presto 查询

Spark 用 to_json() 写出的字段，Presto 有时读出来是字符串而非 JSON 类型，json_extract_scalar() 直接报错 Cannot cast varchar to json —— 根本不是语法问题，是类型没对齐。

• 写入时显式指定列类型：Spark SQL 中用 CAST(to_json(Struct(...)) AS String) 写入，但 Presto 侧建表要用 JSON 类型，不能是 VARCHAR
• ORC/Parquet 文件里 JSON 列实际存的是二进制字节流，不同引擎对 json 类型的元数据标记不一致，推荐统一用 Parquet + 显式 schema 定义
• Trino（旧 PrestoSQL）对 JSON 函数支持更全，json_format() 和 json_parse() 可双向转换，但老 PrestoDB 只支持 json_extract 系列，升级前先验算函数兼容性

JSON 字段索引失效的隐蔽原因

MySQL 给 JSON 字段加了虚拟列和二级索引，但 WHERE JSON_CONTAINS(json_col, '"ios"', '$.os') 还是走全表扫描——不是没建索引，是函数用法绕过了索引下推。

• 虚拟列必须严格匹配 JSON 路径提取值，比如建了 os_type VARCHAR(20) GENERATED ALWAYS AS (json_unquote(json_extract(json_col, '$.os')))，查询就得写 WHERE os_type = 'ios'
• postgresql 的 jsonb_path_ops 索引只加速 @>（包含）操作，->> 提取后比较不会走这个索引，得配合 jsonb_path_query_array() 配合 gin 索引
• ES 里把 JSON 当 text 字段映射，搜索 user.device.os: "ios" 会分词，结果搜不到；必须设为 keyword 类型，且注意嵌套对象需开启 enabled: true

JSON 在大数据链路里从来不是“拿来就能用”的数据类型，它像一扇虚掩的门——看着通，推一下才发现后面连着三道需要手动校准的锁：结构不确定性、计算路径不可控、引擎语义不一致。最常被跳过的一步，是写入前对原始 JSON 做 json_valid() + json_length() 双校验，而不是等下游任务报错再回溯。