Spark SQL JSON数据的解析

公司记录日志的时候，将请求数据和返回数据以JSON格式存储到了数据库中，为了更高的处理这部分JSON数据，就用到了Spark SQL自带的一些JSON函数。这里做这些函数的方法做了一些整理。

get_json_object(json_txt, path)

get_json_object 是 Spark SQL 提供的一个内置函数，用于从 JSON 字符串中提取嵌套的字段。该函数接受两个参数：一个 JSON 字符串和一个 JSON 路径。

• 第一个参数 json_txt 是一个包含 JSON 对象的字符串列。
• 第二个参数 path 是一个 JSON 路径，用于指定你想从 JSON 对象中提取哪个字段。这个路径应该以 $ 开头，每个子字段用 . 分割。例如，如果 JSON 对象是 {“a”: {“b”: 1}}，你可以用 $.a.b 来提取值 1。

以下是一个 SQL 查询的例子，它使用 get_json_object 函数提取 JSON 字符串中的字段：

SELECT get_json_object(json_column, '$.field1')
FROM table;

这个查询会返回 json_column 中 field1 字段的值。如果 field1 是一个嵌套的字段，你可以使用 $.field1.subfield1 这样的路径来提取它。

如果 JSON 路径不存在，或者 JSON 字符串无法解析，get_json_object 函数会返回 NULL。

如果你的 JSON 字符串包含一个数组(列表)，你仍然可以使用 get_json_object 函数。你只需要在 JSON 路径中加上数组索引。请注意，索引是从 0 开始的。

例如，假设 json_column 包含一个如下的 JSON 对象：

{"array": [1, 2, 3]}

你可以使用以下的查询来获取数组的第一个元素：

SELECT get_json_object(json_column, '$.array[0]')
FROM table;

这个查询会返回 1。

如果你想获取数组的所有元素，你可以使用 explode 函数配合 get_json_object 使用。首先，你需要把 JSON 字符串转换为一个 Spark SQL 的 array 类型，然后使用 explode 函数创建一个新的行，每个行代表数组的一个元素。

如果你有一个包含多个 JSON 对象的数组，并且想从每个对象中提取 id 字段，你可以使用类似于以下的查询:

SELECT get_json_object(json_column, '$.array[*].id')
FROM table;

这种方法应该可以工作，但是它会返回一个包含所有 id 值的数组，而不是一个包含多行的表格。

json_array_length(jsonArray)

json_array_length的作用是返回JSON数组中元素的数量。

代码示例：

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

或者可以结合get_json_object使用，例如：

SELECT json_array_length(get_json_object(json_text,'$.products[*].items'))
FROM table

json_object_keys(json_object)

以数组形式返回最外层JSON对象的所有键。示例：

json_object_keys({"f1":"abc","f2":{"f3":"a", "f4":"b"}})

返回的是[f1, f2]

或者可以结合get_json_object使用，例如：

SELECT json_array_length(get_json_object(json_text,'$.products[0] '))
FROM table

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

json_tuple 是 Spark SQL 中的一个函数，它用于从 JSON 字符串中提取特定的字段。它会返回一个包含 n 个字段值的元组。

你可以像下面这样使用 json_tuple 函数：

SELECT json_tuple(json_column, 'field1', 'field2', ..., 'fieldn')
FROM table;

在这个 SQL 语句中，json_column 是你的 JSON 字符串所在的列，’field1′, ‘field2’, …, ‘fieldn’ 是你想从 JSON 字符串中提取的字段的名称。

例如，如果你有一个包含多个 JSON 对象的列，并且想从每个对象中提取 id 和 name 字段，你可以这样做：

SELECT json_tuple(json_column, 'id', 'name')
FROM table;

这将返回一个包含 id 和 name 字段值的元组。

schema_of_json(json[, options])

schema_of_json 是 Spark SQL 中的一个函数，它用于从 JSON 字符串中推断出其 schema。

示例：

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

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

但是个人城市后发现一直报错，原因好像是JSON字符串中有NULL值。due to data type mismatch: The input json should be a foldable string expression and not null;

from_json(jsonStr, schema[, options])

from_json 是 Spark SQL 中的一个函数，它用于将 JSON 字符串解析为一个 Spark SQL 可以理解的结构化数据格式。

• 函数的第一个参数 jsonStr 是包含 JSON 数据的字符串。
• 第二个参数 schema 是一个描述所需数据结构的字符串，它定义了 JSON 数据的预期格式。

示例：

SELECT from_json('{"teacher": "Alice", "student": [{"name": "Bob", "rank": 1}, {"name": "Charlie", "rank": 2}]}', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');

这里的schema 可以使用Spark SQL 中的DDL（Data Definition Language）语法，该语法是一种更简洁的方式来定义数据结构。

以下是一些常用的数据类型和它们的 DDL 表示：

• string：字符串类型。
• int：整数类型。
• double：双精度浮点数类型。
• boolean：布尔类型。
• date：日期类型。
• timestamp：时间戳类型。

你还可以使用 DDL 语法来定义更复杂的数据类型，如数组、映射和结构等。例如：

• array<type>：这定义了一个数组，数组中的元素都是同一类型。例如，array<int>是一个整数数组。
• map<type, type>：这定义了一个映射，其中的键和值可以是任何类型。例如，map<string, int>是一个键为字符串、值为整数的映射。
• struct<field1: type1, field2: type2, …>：这定义了一个结构，结构中可以包含多个字段，每个字段都有一个名称和类型。例如，struct<name: string, age: int>是一个包含名为 “name” 的字符串字段和名为 “age” 的整数字段的结构。

关于你之前提到的 from_json 函数中的 schema，你可以使用 DDL 语法来定义 JSON 数据的预期结构。例如：

from_json(json_column, 'struct<name:string, age:int, pets:array<string>, isAlive:boolean>')

在这个例子中，DDL schema 定义了一个名为 “name” 的字符串字段、一个名为 “age” 的整数字段、一个名为 “pets” 的字符串数组字段和一个名为 “isAlive” 的布尔字段。

to_json(expr[, options])

to_json 是 Spark SQL 的一个函数，它用于将一个结构化数据转换成 JSON 格式的字符串。

expr 是要转换成 JSON 的表达式，它通常是一个包含多个字段的结构或者一个 map。例如，你可以将一张表中的一行数据转换成 JSON：

SELECT to_json(struct(*)) as json
FROM table;

在这个 SQL 语句中，struct(*) 创建了一个包含表中所有字段的新结构，然后 to_json 函数将这个结构转换成了 JSON 格式的字符串。

选项参数 options 是一个 JSON 格式的字符串，包含了转换过程中可用的选项。一个常用的选项是 dateFormat，它用于指定日期字段在 JSON 中的表示方式。例如：

SELECT to_json(struct(*), '{"dateFormat": "dd/MM/yyyy"}') as json
FROM table;

在这个 SQL 语句中，我们设定了日期格式选项，这会影响到日期字段在 JSON 中的表示方式。

explode(from_json(…))

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

示例：

SELECT q.text
FROM (
    SELECT explode(from_json('[{"text":"Tea"},{"text":"Apple"}]', 'ARRAY<STRUCT<text: STRING>>')) AS q
);

更复杂的代码示例：

SELECT inquiry_id, product_name, item_name, item_charge_type, item_upper
    , item_lower, item_max_discount, res.name AS res_name, res.code AS res_code, res.carType AS res_car_type
    , res.chargeType AS res_charge_type, res.distance AS res_distance, res.duration AS res_duration, res.seats AS res_seats, res.merchantPrice AS res_mer_price
    , res.price AS res_price, res.finalPrice AS res_final_price, res.discount AS res_discount, res.heiJDiscount AS res_hj_discount, res.discounts AS res_discounts
    , res.priceAdjust AS res_price_adjust
FROM (
    SELECT inquiry_id, product_name, item.name AS item_name, item.chargeTypeName AS item_charge_type, item.upper AS item_upper
        , item.lower AS item_lower, item.maxDiscount AS item_max_discount, item.resources AS item_resources
    FROM (
        SELECT inquiry_id, product.name AS product_name, product.items AS items
        FROM (
            SELECT inquiry_id, from_json(inquiry_result, 'struct<products:array<struct<name:string, items:array<struct<name:string, chargeTypeName:string, upper:string, lower:string, maxDiscount:string,resources:array<struct<name:string,code:string,carType:int,chargeType:int,distance:int,duration:int,seats:int,merchantPrice:string,price:string,finalPrice:string,discount:string,heiJDiscount:string,discounts:array<struct<name:string,type:string,batchNo:string,flashSale:boolean,amount:string>>, priceAdjust:struct<search:struct<amount:string,percent:string>,booked:struct<amount:string,percent:string>>>>>>>>>') AS products
            FROM table
        ) p
            LATERAL VIEW explode(p.products.products) exploded_table AS product
    ) i
        LATERAL VIEW explode(i.items) exploded_table AS item
) r
    LATERAL VIEW explode(r.item_resources) exploded_table AS res

参考链接：

• Built-in Functions – Spark 3.5.1 Documentation (apache.org)