在Databricks中处理JSON列数据的两类验证需求求助

算力阿峰

2026-6-12

问题描述

在Databricks中有如下表格：

date	json_col
10/05/2025	{estudents = [{id_student = 12341, score = 5.23}]}
01/05/2025	{estudents = [{id_student = 4325, score = 10}]}
21/02/2025	{estudents = [{id_student = 4674, score = 6}]}
11/03/2025	{estudents = [{id_student = 6764, score = 3.5}]}

需要完成两个验证任务：

检查是否存在非整数类型的分数
列出id_student字段长度超过4位数字的学生

解决方案

1. 检查非整数分数

先解析JSON列提取学生数据，通过对比score与其整数转换后的值，筛选出非整数分数的记录：

SELECT 
  date,
  json_col,
  student.id_student,
  student.score
FROM 
  your_table_name,
  LATERAL VIEW json_tuple(json_col, 'estudents') jt AS students_arr,
  LATERAL VIEW explode(from_json(students_arr, 'array<struct<id_student:string, score:double>>')) exploded AS student
WHERE 
  student.score != CAST(student.score AS INTEGER);

如果查询返回结果，说明存在非整数分数；无返回结果则所有分数均为整数。

2. 筛选id_student长度超4位的学生

同样解析JSON列后，通过字符串长度判断筛选目标记录：

SELECT 
  date,
  json_col,
  student.id_student,
  student.score
FROM 
  your_table_name,
  LATERAL VIEW json_tuple(json_col, 'estudents') jt AS students_arr,
  LATERAL VIEW explode(from_json(students_arr, 'array<struct<id_student:string, score:double>>')) exploded AS student
WHERE 
  LENGTH(student.id_student) > 4;