spark sql 处理 对象嵌套数组json |
元数据; {"username":"king","actionInfo":{"id":1,"age":"22","partList":[{"code":"123","uname":"king"},{"code":"0012","uname":"king"}]}} |
//方法一:用sql风格处理嵌套json val session = SparkSession.builder() .master("local[*]").appName(this.getClass.toString).getOrCreate() val frame = session.read.format("json").load("E:\\a.json") frame.printSchema() frame.createTempView("test") session.sql("select * from test").show(false) //to_json将字符串json数据转成json对象,json_tuple解析json数据 session.sql( """ |select username,ai.id,ai.age,p.uname,p.code from test |lateral view json_tuple(to_json(actionInfo),'id','age','partList') ai as id,age,partlist |lateral view explode(split(regexp_replace(regexp_extract(partlist,'^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|')) partlist as p |lateral view json_tuple(p,'code','uname') p as code,uname """.stripMargin).show(false) |
//方法二:DSL风格处理嵌套json //导入spark sql方法 用DSL风格处理嵌套json import org.apache.spark.sql.functions._ val frame1 = frame.select(col("username"), get_json_object(to_json(col("actionInfo")), "$.id") as "id", get_json_object(to_json(col("actionInfo")), "$.age") as "age", explode(split(regexp_replace(regexp_extract(get_json_object(to_json(col("actionInfo")), "$.partList"), "^\\[(.+)\\]$", 1), "\\}\\,\\{", "\\}\\|\\|\\{"), "\\|\\|")) as "partlist") frame1.select(col("username"), col("id"), col("age"), get_json_object(col("partlist"),"$.code") as "code", get_json_object(col("partlist"),"$.uname") as "uname").show(false) |
//方法三:用dataFrame处理嵌套数组json |
结果展示:
schedule 信息: |
root |-- actionInfo: struct (nullable = true) | |-- age: string (nullable = true) | |-- id: long (nullable = true) | |-- partList: array (nullable = true) | | |-- element: struct (containsNull = true) | | | |-- code: string (nullable = true) | | | |-- uname: string (nullable = true) |-- username: string (nullable = true) |
方法一 二 数据结果展示: |
+--------+---+---+-----+----+ |username|id |age|uname|code| +--------+---+---+-----+----+ |king |1 |22 |king |123 | |king |1 |22 |king |0012| +--------+---+---+-----+----+ |
方法三结果展示: (king,1,22,List((123,king), (0012,king))) |
参考:参考博客