# SparkSQL JSON 处理函数完全指南 > 适用版本：Apache Spark 3.x > 函数语法均已对照 [Apache Spark SQL 官方文档](https://spark.apache.org/docs/latest/api/sql/index.html) 核实。 --- ## 目录 1. [基础提取函数](#1-基础提取函数) - get_json_object 2. [结构化解析函数](#2-结构化解析函数) - from_json - named_struct - to_json 3. [JSON Key 重命名](#3-json-key-重命名) 4. [JSON 数组高阶函数](#4-json-数组高阶函数) - filter - transform 5. [from_json + filter/transform + named_struct + to_json 组合链路](#5-fromjson--filtertransform--namedstruct--tojson-组合链路) 6. [Schema 推断函数](#6-schema-推断函数) - schema_of_json 7. [综合案例](#7-综合案例) --- ## 1. 基础提取函数 ### `get_json_object` #### 函数定义 ```sql get_json_object(json_txt, path) ``` | 参数 | 类型 | 说明 | |----------|------|------------------------------| | json_txt | STRING | 原始 JSON 字符串 | | path | STRING | JSONPath 表达式，以 `$` 开头 | **返回类型**：`STRING`（路径不存在或解析失败时返回 `NULL`） #### 常用 JSONPath 语法 | 表达式 | 含义 | |----------------|------------------------------| | `$` | 根对象 | | `$.key` | 取顶层字段 | | `$.a.b` | 取嵌套字段 | | `$.arr[0]` | 取数组第一个元素 | | `$.arr[*]` | 取数组全部元素（返回 JSON 串） | | `$.arr[*].id` | 取数组中每个对象的 id 字段 | #### 使用案例 ```sql -- 建表准备 CREATE OR REPLACE TEMP VIEW orders AS SELECT * FROM VALUES ('{"id":1,"user":{"name":"Alice","age":30},"items":[{"sku":"A01","qty":2},{"sku":"B02","qty":1}],"amount":199.5}'), ('{"id":2,"user":{"name":"Bob","age":25},"items":[{"sku":"C03","qty":5}],"amount":88.0}') AS t(raw_json); -- 提取顶层字段 SELECT get_json_object(raw_json, '$.id') AS order_id, get_json_object(raw_json, '$.amount') AS amount FROM orders; -- 结果： -- order_id | amount -- 1 | 199.5 -- 2 | 88.0 -- 提取嵌套字段 SELECT get_json_object(raw_json, '$.user.name') AS user_name, get_json_object(raw_json, '$.user.age') AS user_age FROM orders; -- 结果： -- user_name | user_age -- Alice | 30 -- Bob | 25 -- 提取数组中特定下标元素 SELECT get_json_object(raw_json, '$.items[0].sku') AS first_sku, get_json_object(raw_json, '$.items[0].qty') AS first_qty FROM orders; -- 结果： -- first_sku | first_qty -- A01 | 2 -- C03 | 5 -- 返回值始终是 STRING，需要数值运算时要显式 CAST SELECT CAST(get_json_object(raw_json, '$.amount') AS DOUBLE) * 0.9 AS discounted FROM orders; ``` --- ## 2. 结构化解析函数 ### 2.1 `from_json` 将 JSON 字符串解析为结构体（Struct）或数组类型，是 SparkSQL 处理 JSON 数据最核心的函数。 #### 函数定义 ```sql from_json(jsonStr, schema [, options]) ``` | 参数 | 类型 | 说明 | |---------|----------------------|---------------------------------------------------| | jsonStr | STRING | 原始 JSON 字符串列 | | schema | STRING | DDL 格式的 Schema 字符串，或由 `schema_of_json` 生成 | | options | MAP\ | 可选的解析配置项 | **常用 options 配置** | 选项 | 默认值 | 说明 | |----------------------|-------------------------------------|--------------------------------------------------------------| | `timestampFormat` | `yyyy-MM-dd'T'HH:mm:ss[.SSS][XXX]` | 时间戳格式 | | `dateFormat` | `yyyy-MM-dd` | 日期格式 | | `mode` | `PERMISSIVE` | PERMISSIVE（宽容）/ FAILFAST（报错）/ DROPMALFORMED（丢弃坏数据）| | `allowComments` | `false` | 是否允许 JSON 注释 | | `primitivesAsString` | `false` | 是否将所有基础类型都读为 STRING | **Schema DDL 语法速查** ``` INT, BIGINT, DOUBLE, FLOAT, STRING, BOOLEAN, DATE, TIMESTAMP STRUCT ARRAY MAP ``` #### 使用案例 ```sql -- 案例1：解析简单对象，然后用点号取字段 SELECT parsed.id, parsed.amount, parsed.user.name, parsed.user.age FROM ( SELECT from_json(raw_json, 'STRUCT>' ) AS parsed FROM orders ); -- 结果： -- id | amount | name | age -- 1 | 199.5 | Alice | 30 -- 2 | 88.0 | Bob | 25 -- 案例2：解析 JSON 中的数组字段，再展开为多行 SELECT parsed.id, item.sku, item.qty FROM ( SELECT from_json(raw_json, 'STRUCT>>' ) AS parsed FROM orders ) LATERAL VIEW EXPLODE(parsed.items) AS item; -- 结果： -- id | sku | qty -- 1 | A01 | 2 -- 1 | B02 | 1 -- 2 | C03 | 5 -- 案例3：解析顶层本身就是 JSON 数组的字符串 CREATE OR REPLACE TEMP VIEW raw_arrays AS SELECT * FROM VALUES ('[{"id":1,"val":"x"},{"id":2,"val":"y"}]'), ('[{"id":3,"val":"z"}]') AS t(arr_json); SELECT elem.id, elem.val FROM ( SELECT from_json(arr_json, 'ARRAY>') AS arr FROM raw_arrays ) LATERAL VIEW EXPLODE(arr) AS elem; -- 结果： -- id | val -- 1 | x -- 2 | y -- 3 | z -- 案例4：使用 options 指定自定义时间戳格式 SELECT from_json( '{"event_time":"2024-01-15 10:30:00"}', 'STRUCT', MAP('timestampFormat', 'yyyy-MM-dd HH:mm:ss') ) AS parsed; -- 结果：parsed.event_time = 2024-01-15 10:30:00 ``` --- ### 2.2 `named_struct` 按名称-值对构造一个 Struct，常与 `to_json` 配合用于灵活地组装 JSON 输出；也是 JSON Key 重命名的核心工具。 #### 函数定义 ```sql named_struct(name1, val1, name2, val2, ...) ``` | 参数 | 说明 | |---------------|-------------------------------------| | name1, name2… | 字段名，必须是**字符串字面量** | | val1, val2… | 字段值，可以是列名、表达式或字面量 | **返回类型**：`STRUCT` #### 使用案例 ```sql -- 从字面量构造 Struct SELECT named_struct('id', 1, 'name', 'Alice', 'score', 9.5) AS s; -- 结果：{id: 1, name: Alice, score: 9.5} -- 与计算表达式结合 SELECT named_struct( 'order_id', 1001, 'subtotal', 99.0 * 2, 'tax', ROUND(99.0 * 2 * 0.08, 2) ) AS order_summary; -- 结果：{order_id: 1001, subtotal: 198.0, tax: 15.84} ``` --- ### 2.3 `to_json` 将 Struct、Array、Map 类型序列化为 JSON 字符串，常与 `named_struct` 配合使用以自由控制输出结构。 #### 函数定义 ```sql to_json(expr [, options]) ``` | 参数 | 类型 | 说明 | |---------|----------------------|-------------------------------| | expr | STRUCT / ARRAY / MAP | 要序列化的结构化类型列或表达式 | | options | MAP\ | 可选配置项，与 `from_json` 一致 | **返回类型**：`STRING` #### 使用案例 ```sql -- 案例1：named_struct 直接序列化（最常用） SELECT to_json(named_struct('a', 1, 'b', 2)); -- 结果：{"a":1,"b":2} -- 案例2：对列做字段筛选后输出 JSON SELECT to_json(named_struct( 'order_id', get_json_object(raw_json, '$.id'), 'customer', get_json_object(raw_json, '$.user.name'), 'amount', CAST(get_json_object(raw_json, '$.amount') AS DOUBLE) )) AS summary_json FROM orders; -- 结果： -- {"order_id":"1","customer":"Alice","amount":199.5} -- {"order_id":"2","customer":"Bob","amount":88.0} -- 案例3：Array of Struct 序列化 SELECT to_json(ARRAY( named_struct('sku', 'A01', 'qty', 2), named_struct('sku', 'B02', 'qty', 1) )); -- 结果：[{"sku":"A01","qty":2},{"sku":"B02","qty":1}] -- 案例4：Map 序列化 SELECT to_json(MAP('city', '北京', 'district', '朝阳区')); -- 结果：{"city":"北京","district":"朝阳区"} -- 案例5：带 options 处理时间戳格式 SELECT to_json( named_struct('ts', CURRENT_TIMESTAMP()), MAP('timestampFormat', 'yyyy-MM-dd HH:mm:ss') ); -- 结果：{"ts":"2024-01-15 10:30:00"} ``` --- ## 3. JSON Key 重命名实际场景中常需要修改 JSON 字符串的字段名，标准做法是：**`from_json` 解析 → `named_struct` 重命名 → `to_json` 序列化**。 ### 3.1 简单字段重命名 ```sql -- 原始 JSON：{"a": 1, "b": "hello"} -- 目标：将 key "a" 改为 "a1"，"b" 改为 "b1" SELECT to_json( named_struct( 'a1', parsed.a, 'b1', parsed.b ) ) AS renamed_json FROM ( SELECT from_json('{"a": 1, "b": "hello"}', 'STRUCT') AS parsed ); -- 结果：{"a1":1,"b1":"hello"} ``` ### 3.2 批量列重命名（对表操作） ```sql CREATE OR REPLACE TEMP VIEW user_events AS SELECT * FROM VALUES ('{"uid":1001,"evt_nm":"click","pg":"/home"}'), ('{"uid":1002,"evt_nm":"purchase","pg":"/checkout"}') AS t(raw_json); -- 将缩写字段名展开为规范字段名 SELECT to_json(named_struct( 'user_id', parsed.uid, 'event_name', parsed.evt_nm, 'page', parsed.pg )) AS normalized_json FROM ( SELECT from_json(raw_json, 'STRUCT' ) AS parsed FROM user_events ); -- 结果： -- {"user_id":1001,"event_name":"click","page":"/home"} -- {"user_id":1002,"event_name":"purchase","page":"/checkout"} ``` ### 3.3 嵌套结构字段重命名 ```sql -- 同时重命名顶层和嵌套层字段 SELECT to_json(named_struct( 'order_id', parsed.id, 'user_info', named_struct( 'full_name', parsed.user.name, 'years_old', parsed.user.age ), 'total_amt', parsed.amount )) AS output_json FROM ( SELECT from_json(raw_json, 'STRUCT>' ) AS parsed FROM orders ); -- 结果： -- {"order_id":1,"user_info":{"full_name":"Alice","years_old":30},"total_amt":199.5} -- {"order_id":2,"user_info":{"full_name":"Bob","years_old":25},"total_amt":88.0} ``` ### 3.4 同时增减字段 ```sql -- 只保留部分字段，并新增计算字段 SELECT to_json(named_struct( 'id', parsed.id, 'name', parsed.user.name, 'is_vip', parsed.user.age >= 28, -- 新增：根据年龄计算 'discounted', ROUND(parsed.amount * 0.9, 2) -- 新增：九折金额 -- 不写 items 字段，相当于从输出中删除该字段 )) AS new_json FROM ( SELECT from_json(raw_json, 'STRUCT>' ) AS parsed FROM orders ); -- 结果： -- {"id":1,"name":"Alice","is_vip":true,"discounted":179.55} -- {"id":2,"name":"Bob","is_vip":false,"discounted":79.2} ``` --- ## 4. JSON 数组高阶函数以下函数作用于 **`ARRAY` 类型列**，配合 `from_json` 解析后使用效果最佳。 ### 数据准备 ```sql CREATE OR REPLACE TEMP VIEW parsed_orders AS SELECT parsed.id AS order_id, parsed.items AS items, -- ARRAY> parsed.amount FROM ( SELECT from_json(raw_json, 'STRUCT>>' ) AS parsed FROM orders ); ``` --- ### 4.1 `filter` 按条件过滤数组元素，返回满足条件的子数组。 #### 函数定义 ```sql filter(expr, func) ``` | 参数 | 说明 | |------|------------------------------------------------------------------------| | expr | ARRAY 类型表达式 | | func | Lambda 函数：`element -> condition` 或 `(element, index) -> condition` | **返回类型**：与输入数组相同的 `ARRAY` 类型 #### 使用案例 ```sql -- 案例1：过滤出 qty > 1 的商品 SELECT order_id, filter(items, item -> item.qty > 1) AS high_qty_items FROM parsed_orders; -- 结果： -- order_id | high_qty_items -- 1 | [{"sku":"A01","qty":2}] -- 2 | [{"sku":"C03","qty":5}] -- 案例2：过滤特定 sku 前缀的商品 SELECT order_id, filter(items, item -> item.sku LIKE 'A%') AS a_series_items FROM parsed_orders; -- 结果： -- 1 | [{"sku":"A01","qty":2}] -- 2 | [] -- 案例3：使用 SIZE 统计过滤后的数量 SELECT order_id, SIZE(filter(items, item -> item.qty >= 2)) AS bulk_item_count FROM parsed_orders; -- 结果： -- 1 | 1 -- 2 | 1 -- 案例4：双参数 lambda —— (element, index)，只保留偶数下标元素 SELECT order_id, filter(items, (item, idx) -> idx % 2 = 0) AS even_index_items FROM parsed_orders; ``` --- ### 4.2 `transform` 对数组中每个元素做映射转换，返回转换后的新数组。 #### 函数定义 ```sql transform(expr, func) ``` | 参数 | 说明 | |------|-----------------------------------------------------------------------------| | expr | ARRAY 类型表达式 | | func | Lambda 函数：`element -> expression` 或 `(element, index) -> expression` | **返回类型**：`ARRAY` #### 使用案例 ```sql -- 案例1：提取数组中每个 Struct 的单个字段，生成简单数组 SELECT order_id, transform(items, item -> item.sku) AS sku_list, transform(items, item -> item.qty) AS qty_list FROM parsed_orders; -- 结果： -- order_id | sku_list | qty_list -- 1 | ["A01","B02"] | [2,1] -- 2 | ["C03"] | [5] -- 案例2：对字段做计算，用 named_struct 返回新 Struct（qty 打八折） SELECT order_id, transform(items, item -> named_struct( 'sku', item.sku, 'discounted_qty', CAST(item.qty * 0.8 AS INT) )) AS discounted_items FROM parsed_orders; -- 结果： -- 1 | [{"sku":"A01","discounted_qty":1},{"sku":"B02","discounted_qty":0}] -- 2 | [{"sku":"C03","discounted_qty":4}] -- 案例3：拼接字符串标签 SELECT order_id, transform(items, item -> CONCAT(item.sku, ' x', item.qty)) AS item_labels FROM parsed_orders; -- 结果： -- 1 | ["A01 x2","B02 x1"] -- 2 | ["C03 x5"] -- 案例4：与 filter 组合——先过滤再转换 SELECT order_id, transform( filter(items, item -> item.qty > 1), item -> item.sku ) AS bulk_skus FROM parsed_orders; -- 结果： -- 1 | ["A01"] -- 2 | ["C03"] -- 案例5：双参数 lambda —— (element, index)，生成带序号的标签 SELECT order_id, transform(items, (item, idx) -> CONCAT(CAST(idx + 1 AS STRING), '. ', item.sku) ) AS numbered_items FROM parsed_orders; -- 结果： -- 1 | ["1. A01","2. B02"] -- 2 | ["1. C03"] ``` --- ## 5. from_json + filter/transform + named_struct + to_json 组合链路这是 SparkSQL 处理 JSON 数据最常用的完整链路：**解析 → 数组处理 → 重组 → 输出**，四步串联，适用于 JSON 字段清洗、格式转换、数据富化等场景。 ### 数据准备 ```sql CREATE OR REPLACE TEMP VIEW raw_orders AS SELECT * FROM VALUES ('{"order_id":"ORD001","channel":"app","items":[{"sku":"P001","name":"笔记本","price":5999.0,"qty":1,"category":"电子"},{"sku":"P002","name":"鼠标","price":99.0,"qty":2,"category":"电子"},{"sku":"P003","name":"水杯","price":39.0,"qty":3,"category":"生活"}]}'), ('{"order_id":"ORD002","channel":"web","items":[{"sku":"P004","name":"键盘","price":299.0,"qty":1,"category":"电子"},{"sku":"P005","name":"笔记本","price":15.0,"qty":10,"category":"文具"}]}'), ('{"order_id":"ORD003","channel":"app","items":[{"sku":"P001","name":"笔记本电脑","price":8999.0,"qty":1,"category":"电子"}]}') AS t(raw_json); ``` --- ### 链路一：过滤数组元素 + 重命名 key + 输出 JSON **场景**：只保留电子类商品，同时将 JSON key 从英文改为中文，输出新 JSON。 ```sql SELECT to_json(named_struct( '订单号', parsed.order_id, '渠道', parsed.channel, '电子商品', filter(parsed.items, item -> item.category = '电子'), '电子商品数', SIZE(filter(parsed.items, item -> item.category = '电子')) )) AS result_json FROM ( SELECT from_json(raw_json, 'STRUCT>>' ) AS parsed FROM raw_orders ); -- 结果（ORD001）： -- {"订单号":"ORD001","渠道":"app","电子商品":[{"sku":"P001","name":"笔记本","price":5999.0,"qty":1,"category":"电子"},{"sku":"P002","name":"鼠标","price":99.0,"qty":2,"category":"电子"}],"电子商品数":2} ``` --- ### 链路二：转换数组元素结构 + 精简字段 + 输出 JSON **场景**：对数组中每个商品做字段裁剪和计算（只保留 sku、name、小计），输出精简 JSON。 ```sql SELECT to_json(named_struct( 'order_id', parsed.order_id, 'channel', parsed.channel, 'items', transform( parsed.items, item -> named_struct( 'sku', item.sku, 'name', item.name, 'subtotal', ROUND(item.price * item.qty, 2) -- 省略 price、qty、category，相当于删除这三个字段 ) ) )) AS slim_json FROM ( SELECT from_json(raw_json, 'STRUCT>>' ) AS parsed FROM raw_orders ); -- 结果（ORD001）： -- {"order_id":"ORD001","channel":"app","items":[{"sku":"P001","name":"笔记本","subtotal":5999.0},{"sku":"P002","name":"鼠标","subtotal":198.0},{"sku":"P003","name":"水杯","subtotal":117.0}]} ``` --- ### 链路三：filter + transform 组合 + 数组 key 重命名 + 输出 JSON **场景**：先过滤出单价 ≥ 100 的商品，再对剩余商品打九折并将字段名改为英中混合，最终输出 JSON。 ```sql SELECT to_json(named_struct( 'order_id', parsed.order_id, 'eligible_items', transform( -- Step1：先过滤单价 >= 100 的商品 filter(parsed.items, item -> item.price >= 100), -- Step2：对过滤结果做字段裁剪 + 重命名 + 计算 item -> named_struct( 'sku', item.sku, '商品名称', item.name, '折后单价', ROUND(item.price * 0.9, 2), '数量', item.qty, '折后小计', ROUND(item.price * 0.9 * item.qty, 2) ) ) )) AS result_json FROM ( SELECT from_json(raw_json, 'STRUCT>>' ) AS parsed FROM raw_orders ); -- 结果（ORD001）：过滤掉 P003（price=39），剩余 P001、P002 打九折 -- {"order_id":"ORD001","eligible_items":[{"sku":"P001","商品名称":"笔记本","折后单价":5399.1,"数量":1,"折后小计":5399.1},{"sku":"P002","商品名称":"鼠标","折后单价":89.1,"数量":2,"折后小计":178.2}]} ``` --- ### 链路四：多条件 filter + 多步 transform + 嵌套 named_struct 输出 **场景**：按渠道分类商品，app 渠道打九五折、web 渠道打九折；同时将订单整体结构重组，加入折扣率字段。 ```sql SELECT to_json(named_struct( 'order_id', parsed.order_id, 'channel', parsed.channel, 'discount', CASE parsed.channel WHEN 'app' THEN 0.95 ELSE 0.90 END, 'items', transform( parsed.items, item -> named_struct( 'sku', item.sku, 'name', item.name, 'ori_price', item.price, 'fin_price', ROUND( item.price * CASE parsed.channel WHEN 'app' THEN 0.95 ELSE 0.90 END, 2 ), 'qty', item.qty ) ), -- 汇总字段：所有商品折后总金额 'total', ROUND( SIZE(parsed.items) * 0 + -- 触发 ARRAY 长度计算（占位） -- 用 transform 计算折后小计后求和需借助 array_sum（Spark 3.3+） -- 此处演示通过嵌套 named_struct 携带汇总信息 0, -- 实际场景可用 aggregate 或外层 SUM 替代 2 ) )) AS result_json FROM ( SELECT from_json(raw_json, 'STRUCT>>' ) AS parsed FROM raw_orders ); ``` > 💡 **补充说明**：若需在 JSON 内计算数组汇总值（如折后总金额），推荐先用 `transform` 生成含小计的新数组，再将整个结果 `to_json` 输出，或在外层查询中用普通 SQL 聚合后拼接。`named_struct + to_json` 的职责是**组装结构**，复杂聚合交给外层 SQL 处理，职责分离更清晰。 --- ### 链路五：JSON 数组中的嵌套对象字段重命名 **场景**：JSON 数组的每个元素本身也是嵌套对象，需要对数组元素内的嵌套层字段同步重命名。 ```sql CREATE OR REPLACE TEMP VIEW nested_items AS SELECT * FROM VALUES ('{"order":"ORD001","products":[{"prod_cd":"P001","prod_nm":"笔记本","spec":{"color":"银色","weight_kg":1.5}},{"prod_cd":"P002","prod_nm":"鼠标","spec":{"color":"黑色","weight_kg":0.1}}]}') AS t(raw_json); -- 将数组中每个元素的 prod_cd → product_code，prod_nm → product_name -- 同时将 spec 中的 weight_kg → weight SELECT to_json(named_struct( 'order_no', parsed.order, 'products', transform( parsed.products, p -> named_struct( 'product_code', p.prod_cd, 'product_name', p.prod_nm, 'spec', named_struct( 'color', p.spec.color, 'weight', p.spec.weight_kg -- key 重命名 ) ) ) )) AS renamed_json FROM ( SELECT from_json(raw_json, 'STRUCT>>>' ) AS parsed FROM nested_items ); -- 结果： -- {"order_no":"ORD001","products":[{"product_code":"P001","product_name":"笔记本","spec":{"color":"银色","weight":1.5}},{"product_code":"P002","product_name":"鼠标","spec":{"color":"黑色","weight":0.1}}]} ``` --- ## 6. Schema 推断函数 ### `schema_of_json` 根据 JSON 字符串**字面量样本**自动推断 DDL Schema，主要用于开发探索阶段，快速了解数据结构。 #### 函数定义 ```sql schema_of_json(json [, options]) ``` | 参数 | 类型 | 说明 | |---------|----------------------|-----------------------------------------| | json | STRING 字面量 | JSON 样本，**必须是常量字面量**，不能是列名 | | options | MAP\ | 可选配置项 | **返回类型**：`STRING`（DDL 格式的 Schema，字段按字母顺序排列） > ⚠️ **重要限制** > - 只接受字符串**字面量**，不能直接传入列名 > - 推断的整数类型统一为 `BIGINT`，浮点为 `DOUBLE` > - 推断结果仅基于样本，若实际数据字段更多，需手工补全 #### 使用案例 ```sql -- 案例1：推断简单对象 Schema（字段按字母排序） SELECT schema_of_json('{"id":1,"name":"Alice","score":9.5,"active":true}'); -- 结果：STRUCT -- 案例2：推断嵌套对象和数组 Schema SELECT schema_of_json('{"id":1,"user":{"name":"Alice","age":30},"tags":["spark","sql"]}'); -- 结果：STRUCT, user: STRUCT> -- 案例3：推断顶层数组 Schema SELECT schema_of_json('[{"sku":"A01","qty":2},{"sku":"B02","qty":1}]'); -- 结果：ARRAY> -- 案例4：用 options 将数字识别为 DECIMAL SELECT schema_of_json( '{"price":9.99}', MAP('prefersDecimal', 'true') ); -- 结果：STRUCT -- 案例5：推断结果直接嵌入 from_json（适合开发探索，生产环境建议写死 Schema） SELECT from_json( raw_json, schema_of_json('{"id":1,"amount":199.5,"user":{"name":"Alice","age":30},"items":[{"sku":"A01","qty":2}]}') ) AS parsed FROM orders; ``` --- ## 7. 综合案例 ### 电商订单 JSON 全链路处理 ```sql CREATE OR REPLACE TEMP VIEW full_orders AS SELECT * FROM VALUES ('{"order_id":"ORD001","user_id":1001,"status":"paid","items":[{"sku":"P001","name":"笔记本","price":5999.0,"qty":1},{"sku":"P002","name":"鼠标","price":99.0,"qty":2}],"address":{"city":"北京","district":"朝阳区"}}'), ('{"order_id":"ORD002","user_id":1002,"status":"pending","items":[{"sku":"P003","name":"键盘","price":299.0,"qty":1}],"address":{"city":"上海","district":"浦东新区"}}'), ('{"order_id":"ORD003","user_id":1001,"status":"paid","items":[{"sku":"P001","name":"笔记本","price":5999.0,"qty":2},{"sku":"P004","name":"显示器","price":1999.0,"qty":1}],"address":{"city":"北京","district":"海淀区"}}') AS t(raw_json); -- Step 1：一次性解析为结构化类型 CREATE OR REPLACE TEMP VIEW parsed_full AS SELECT from_json(raw_json, ' STRUCT< order_id : STRING, user_id : INT, status : STRING, items : ARRAY>, address : STRUCT > ') AS o FROM full_orders; -- Step 2：过滤高价商品（price >= 1000），只保留 sku/name/小计，重组输出 JSON SELECT to_json(named_struct( 'order_id', o.order_id, 'city', o.address.city, 'status', o.status, -- filter：过滤出高价商品 -- transform + named_struct：裁剪字段并计算小计 'expensive_items', transform( filter(o.items, i -> i.price >= 1000), i -> named_struct( 'sku', i.sku, 'name', i.name, 'subtotal', ROUND(i.price * i.qty, 2) ) ), 'item_count', SIZE(filter(o.items, i -> i.price >= 1000)) )) AS result_json FROM parsed_full; -- 结果（ORD001）： -- {"order_id":"ORD001","city":"北京","status":"paid","expensive_items":[{"sku":"P001","name":"笔记本","subtotal":5999.0}],"item_count":1} -- 结果（ORD002）： -- {"order_id":"ORD002","city":"上海","status":"pending","expensive_items":[],"item_count":0} -- Step 3：对所有商品打折，输出含折扣信息的新 JSON SELECT to_json(named_struct( 'order_id', o.order_id, 'user_id', o.user_id, 'items', transform( o.items, i -> named_struct( 'sku', i.sku, 'name', i.name, 'ori_price', i.price, 'dis_price', ROUND(i.price * 0.9, 2), 'qty', i.qty ) ) )) AS discounted_json FROM parsed_full WHERE o.status = 'paid'; -- 结果（ORD001）： -- {"order_id":"ORD001","user_id":1001,"items":[{"sku":"P001","name":"笔记本","ori_price":5999.0,"dis_price":5399.1,"qty":1},{"sku":"P002","name":"鼠标","ori_price":99.0,"dis_price":89.1,"qty":2}]} ``` --- ## 函数速查表 | 函数 | 适用场景 | 返回类型 | |-------------------|------------------------------|--------------------| | `get_json_object` | 按 JSONPath 提取单值 | STRING | | `from_json` | JSON 字符串 → 结构化类型 | STRUCT / ARRAY | | `named_struct` | 按名称-值对构造 Struct | STRUCT | | `to_json` | 结构化类型 → JSON 字符串 | STRING | | `filter` | 数组按条件过滤 | ARRAY（同输入类型） | | `transform` | 数组元素映射转换 | ARRAY（新类型） | | `schema_of_json` | 从字面量 JSON 推断 Schema | STRING（DDL） | --- ## 最佳实践 **1. 优先用 `from_json` 一次性解析**：对同一列只调用一次 `from_json`，解析后通过点号取字段，避免多次调用 `get_json_object` 重复解析同一 JSON 字符串。 **2. 用 `transform + named_struct` 处理数组内的字段重命名/裁剪**：数组元素是 Struct 时，`transform` 配合 `named_struct` 可以对每个元素做字段增删改，是最灵活的改造方式。 **3. `filter` 在前，`transform` 在后**：先过滤缩小数组规模，再做映射转换，避免对不需要的元素做无用计算。 **4. 用 `named_struct + to_json` 控制最终输出结构**：需要输出 JSON 时，`named_struct` 是 Key 重命名和字段增删的核心工具，配合 `to_json` 完成序列化，比操作原始字符串更安全可靠。 **5. 生产环境写死 Schema**：`schema_of_json` 仅用于开发探索，生产代码中应显式写明 Schema 字符串，避免因样本不全导致字段丢失或类型推断偏差。 **6. 注意 NULL 处理**：JSON 中缺失的字段经 `from_json` 解析后为 `NULL`，使用 `COALESCE` 或 `IFNULL` 兜底默认值。 **7. `get_json_object` 返回值始终是 STRING**：需要参与数值计算时，务必显式 `CAST` 为目标类型。

sparksql-json-processing

Install this skill

Comments