spark-sql处理json字符串的常用函数

整理了spark-sql处理json字符串的几个函数：

1 get_json_object 解析不含数组的 json

 

2 from_json  解析json

3 schema_of_json 提供生成json格式的方法

4 explode   把JSONArray转为多行

get_json_object

get_json_object(string json_string, string path) ：适合最外层为{}的json解析。

 第一个参数是json对象变量，也就是含json的字段 。第二个参数使用  $ 作为json变量标识 定位数据位置,按jsonpath的规则

简单的例子：

select get_json_object('{"drink":"Tea","fruit":"Apple"}', '$.drink') as text

text
Tea

from_json

from_json(column, schema_string)：用schema_string的格式，来解析column。 用schema_string的格式可以用schema_of_json获取。

简单例子：

select from_json('[{"text":"Tea"},{"text":"Apple"}]', 'ARRAY<STRUCT<text: STRING>>');

jsontostructs([{"text":"Tea"},{"text":"Apple"}])
[{"text":"Tea"},{"text":"Apple"}]

进阶例子：

select from_json('[{"text":"Tea"},{"text":"Apple"}]', 'ARRAY<STRUCT<text: STRING>>')[0]['text'] as q;

q
tea

schema_of_json

schema_of_json(s)：解析字符串s，返回描述json格式的字符串。

字典：STRUCT，字典里是一些Key-Value键值对。

列表：ARRAY，列表里是一些相同的元素。

例子1  array：

select schema_of_json('[{"text":"a"},{"b":"a"}, {"c":1}]')

结果：
ARRAY<STRUCT<`b`: STRING, `c`: BIGINT, `text`: STRING>>

例子2  复杂的json：

select schema_of_json('{"vid":391420754,"aid":9701587,"isreply":"0","ispic":"0","fromPage":"2","site":"2","isreplysp":"2","isat":"0","isemoji":"0","loc":"1","page":"1","track_info":""}')

结果：

struct<aid:bigint,fromPage:string,isat:string,isemoji:string,ispic:string,isreply:string,isreplysp:string,loc:string,page:string,site:string,track_info:null,vid:bigint>

例子3  混合：

select schema_of_json('{"text":["a", 2, "b", "a"],"b":1.2, "c":1}')

结果：
STRUCT<`b`: DOUBLE, `c`: BIGINT, `text`: ARRAY<STRING>>

对于复杂的字符串，如果python json.loads报错，schema_of_json可能报错。

批注 1：

语句里有什么，就会返回什么。但一般用json形式的字段，都是扩展字段，里面的属性个数不是固定的。我们可以从生成的结果里面筛选一下，只取我们需要的字段。比如上面的例子2里的，就只取： struct<aid:bigint>

select from_json('{"vid":391420754,"aid":9701587,"page":"1"}' , 'struct<aid:bigint>')

结果：
{"aid":9701587}

批注 2：

如果我们不需要key的话，就直接带上key

select from_json('{"vid":391420754,"aid":9701587,"page":"1"}' , 'struct<aid:bigint>')['aid']

结果：
aid
9701587

explode

explode(from_json(...))：将from_json的内容展成多行（应用于ARRAY，每个元素1行）

例子：

select explode(from_json('[{"text":"Tea"},{"text":"Apple"}]', 'ARRAY<STRUCT<text: STRING>>')) as q;
q
----
{"text":"Tea"}
{"text":"Apple"}

SELECT r1.col.Attr_INT, r1.col.ATTR_DATE, r1.col.ATTR_DOUBLE FROM (SELECT explode(r.json) AS col FROM (SELECT from_json('[{"Attr_INT":1, "ATTR_DOUBLE":10.201, "ATTR_DATE": "2021-01-01"},{"Attr_INT":1, "ATTR_DOUBLE":10.201, "ATTR_DATE": "2021-02-01"}]','array<struct<ATTR_DATE:string,ATTR_DOUBLE:double,Attr_INT:bigint>>') AS json) r) AS r1;
Attr_INT	ATTR_DATE	ATTR_DOUBLE
1	2021-01-01	10.201
1	2021-02-01	10.201

select q.text from (select explode(from_json('[{"text":"Tea"},{"text":"Apple"}]', 'ARRAY<STRUCT<text: STRING>>')) as q);
text
----
Tea
Apple

参考

spark-sql处理json字符串的函数 - 知乎