Spark SQL 内置函数（四）JSON Functions（基于 Spark 3.2.0）

Spark SQL 内置函数（四）JSON Functions（基于 Spark 3.2.0） 前言

本文隶属于专栏《1000个问题搞定大数据技术体系》，该专栏为笔者原创，引用请注明来源，不足和错误之处请在评论区帮忙指出，谢谢！

本专栏目录结构和参考文献请见1000个问题搞定大数据技术体系

正文 from_json(jsonStr, schema[, options]) 描述

返回基于给定 jsonStr 和 schema 的结构体类型。

实践

SELECt from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
+---------------------------+
|from_json({"a":1, "b":0.8})|
+---------------------------+
|                   {1, 0.8}|
+---------------------------+

SELECt from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+--------------------------------+
|from_json({"time":"26/08/2015"})|
+--------------------------------+
|    {"time":2015-08-26 00:00:00}|
+--------------------------------+

options 支持列表 属性名称默认值意义使用范围timeZone（配置值）spark.sql.session.timeZone设置指示时区 ID 的字符串，该时区 ID 用于设置 JSON 数据源或分区值中的时间戳的格式。支持以下格式：timeZone：1. 基于区域的区域 ID：它应具有"区域/城市"的形式，例如"美国/Los_Angeles"。2. 区域偏移：格式应为"(+/-)HH:mm"，例如"-08:00"或"+01:00"。此外，还支持将"UTC"和"Z"作为"+00:00"的别名。不建议使用其他短名称，如"CST"，因为它们可能不明确。读/写primitivesAsStringfalse将所有基元值推断为字符串类型。读prefersDecimalfalse将所有浮点值推断为十进制类型。如果值不适合十进制，则将其推断为双精度值。读allowCommentsfalse忽略 JSON 记录中的 Java/C++样式注释。读allowUnquotedFieldNamesfalse允许使用未加引号的 JSON 字段名称。读allowSingleQuotestrue除双引号外，还允许使用单引号。读allowNumericLeadingZerofalse允许数字中的前导零（例如 00012）。读allowBackslashEscapingAnyCharacterfalse允许使用反斜杠引用机制接受所有字符的引用。读modePERMISSIVE允许在解析期间处理损坏记录的模式。1. PERMISSIVE：当它遇到损坏的记录时，将格式错误的字符串放入 由 columnNameOfCorruptRecord 配置的字段中，并将格式错误的字段设置为 null。若要保留损坏的记录，用户可以在用户自定义 schema 中设置命名是 columnNameOfCorruptRecord的字符串类型字段。如果 schema 没有该字段，则会在分析过程中删除损坏的记录。推断schema 时，它会在输出schema 中隐式添加一个columnNameOfCorruptRecord字段。2. DROPMALFORMED：忽略整个损坏的记录。3. FAILFAST：在遇到损坏的记录时引发异常。读columnNameOfCorruptRecord（配置值）spark.sql.columnNameOfCorruptRecord允许重命名由 PERMISSIVE模式创建的格式错误的字符串的新字段。这将覆盖spark.sql.columnNameOfCorruptRecord。读dateFormatyyyy-MM-dd设置指示日期格式的字符串。自定义日期格式遵循日期时间模式中的格式。这适用于日期类型。读/写timestampFormatyyyy-MM-dd’T’HH:mm:ss[.SSS][XXX]设置指示时间戳格式的字符串。自定义日期格式遵循日期时间模式中的格式。这适用于时间戳类型。读/写multiLinefalse分析每个文件的一条记录，该记录可能跨越多行。读allowUnquotedControlCharsfalse允许 JSON 字符串包含或不包含未加引号的控制字符（值小于 32 的 ASCII 字符，包括制表符和换行符）。读encoding读取时当 multiLine 设置为 true 会进行自动检测，写入时默认为 UTF-8读取时，允许强制设置 JSON 文件的标准基本编码或扩展编码之一。例如 UTF-16BE、UTF-32LE。写入时，指定已保存 json 文件的编码（字符集）。读/写lineSep（r、rn 、 n）（读取时）、 n（写入时）定义应用于分析的行分隔符。读/写samplingRatio1.0定义用于 shema 推断的输入 JSON 对象的占比。读dropFieldIfAllNullfalse在 schema 推断期间，是忽略所有 null 值的列还是忽略空数组/struct。读localeen-US将区域设置设置为 IETF BCP 47 格式的语言标记。例如，在解析日期和时间戳时使用locale。读allowNonNumericNumberstrue允许 JSON 解析器将一组"非数字"（NaN） 符号识别为合法的浮动数字值。1. +INF：表示正无穷大，+Infinity以及Infinity的别名。2. -INF：对于负无穷大，-Infinity的别名 。3. NaN：对于其他 NaN ，如除以零的结果。读compression（无）保存到文件时要使用的压缩编解码器。缩写名称不区分大小写（none，bzip2，gzip，lz4，snappy和deflate）。写ignoreNullFields（配置值）spark.sql.jsonGenerator.ignoreNullFields生成 JSON 对象时是否忽略 null 字段。写 get_json_object(json_txt, path) 描述

从 json 文本 json_txt中基于 路径 path 抽取出一个 JSON 对象。

实践

SELECT get_json_object('{"a":"b"}', '$.a');
+-------------------------------+
|get_json_object({"a":"b"}, $.a)|
+-------------------------------+
|                              b|
+-------------------------------+

path 语法

使用$表示json变量标识，用 . 或 [] 读取对象或数组

get 单层值

spark-sql> SELECT  get_json_object('{
 "store":
        {
         "fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],  
         "bicycle":{"price":19.95,"color":"red"}
         }, 
 "email":"amy@only_for_json_udf_test.net", 
 "owner":"amy" 
}', '$.owner');

amy

get 多层值

spark-sql> SELECT  get_json_object('{
 "store":
        {
         "fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],  
         "bicycle":{"price":19.95,"color":"red"}
         }, 
 "email":"amy@only_for_json_udf_test.net", 
 "owner":"amy" 
}', '$.store.bicycle.price');

19.95

get 数组值

spark-sql> SELECT  get_json_object('{
 "store":
        {
         "fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],  
         "bicycle":{"price":19.95,"color":"red"}
         }, 
 "email":"amy@only_for_json_udf_test.net", 
 "owner":"amy" 
}', '$.store.fruit[0]');

{"weight":8,"type":"apple"}

json_array_length(jsonArray) 描述

返回最外层的 JSON 数组jsonArray中元素的数量。

实践

SELECT json_array_length('[1,2,3,4]');
+----------------------------+
|json_array_length([1,2,3,4])|
+----------------------------+
|                           4|
+----------------------------+

SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
+------------------------------------------------+
|json_array_length([1,2,3,{"f1":1,"f2":[5,6]},4])|
+------------------------------------------------+
|                                               5|
+------------------------------------------------+

SELECT json_array_length('[1,2');
+-----------------------+
|json_array_length([1,2)|
+-----------------------+
|                   null|
+-----------------------+

json_object_keys(json_object) 描述

返回最外层 JSON 对象 json_object 的所有 keys 组成的数组。

实践

ELECT json_object_keys('{}');
+--------------------+
|json_object_keys({})|
+--------------------+
|                  []|
+--------------------+

SELECT json_object_keys('{"key": "value"}');
+----------------------------------+
|json_object_keys({"key": "value"})|
+----------------------------------+
|                             [key]|
+----------------------------------+

SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
+--------------------------------------------------------+
|json_object_keys({"f1":"abc","f2":{"f3":"a", "f4":"b"}})|
+--------------------------------------------------------+
|                                                [f1, f2]|
+--------------------------------------------------------+

json_tuple(jsonStr, p1, p2, …, pn) 描述

• 返回一个类似函数 get_json_object 的元组。
• 这个元组可以有多个名称。
• 所有的输入参数和输出字段类型都是 String 类型。

实践

SELECT json_tuple('{"a":1, "b":2}', 'a', 'b');
+---+---+
| c0| c1|
+---+---+
|  1|  2|
+---+---+

schema_of_json(json[, options]) 描述

以 DDL 的形式返回 JSON 字符串 json 的 schema 形式。

实践

SELECT schema_of_json('[{"col":0}]');
+----------------------------+
|schema_of_json([{"col":0}])|
+----------------------------+
|ARRAY>|
+----------------------------+

SELECT schema_of_json('[{"col":01}]', map('allowNumericLeadingZeros', 'true'));
+----------------------------+
|schema_of_json([{"col":01}])|
+----------------------------+
|ARRAY>|
+----------------------------+

to_json(expr[, options]) 描述

通过给定的结构体返回一个 JSON 字符串。

实践

SELECT to_json(named_struct('a', 1, 'b', 2));
+---------------------------------+
|to_json(named_struct(a, 1, b, 2))|
+---------------------------------+
|                    {"a":1,"b":2}|
+---------------------------------+

SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
+-----------------------------------------------------------------+
|to_json(named_struct(time, to_timestamp(2015-08-26, yyyy-MM-dd)))|
+-----------------------------------------------------------------+
|                             				 {"time":"26/08/2015"}|
+-----------------------------------------------------------------+

SELECT to_json(array(named_struct('a', 1, 'b', 2)));
+----------------------------------------+
|to_json(array(named_struct(a, 1, b, 2)))|
+----------------------------------------+
|                         [{"a":1,"b":2}]|
+----------------------------------------+

SELECT to_json(map('a', named_struct('b', 1)));
+-----------------------------------+
|to_json(map(a, named_struct(b, 1)))|
+-----------------------------------+
|                      {"a":{"b":1}}|
+-----------------------------------+

SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
+----------------------------------------------------+
|to_json(map(named_struct(a, 1), named_struct(b, 2)))|
+----------------------------------------------------+
|                                     {"[1]":{"b":2}}|
+----------------------------------------------------+

SELECT to_json(map('a', 1));
+------------------+
|to_json(map(a, 1))|
+------------------+
|           {"a":1}|
+------------------+

SELECT to_json(array((map('a', 1))));
+-------------------------+
|to_json(array(map(a, 1)))|
+-------------------------+
|                [{"a":1}]|
+-------------------------+