# JSON数组元素展开及对象字段提取实践 #### 场景介绍 某电商公司，订单信息表有三个字段：订单ID（order_id）、客户名称（customer_name）、订单详情（order_info）。其中订单详情 order_info以JSON格式存储了订单号、总金额、订单状态、商品列表（items数组，包含SKU、商品名称、数量、单价等信息）和配送信息（shipping对象，包含收货地址、联系电话、快递公司等）。 对订单信息表的查询如下： ``` SELECT order_id, customer_name, order_info::json->'items' AS items FROM orders; ``` 查询结果如下： ``` order_id | customer_name | items ------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- ORD2026002 | user_2 | [{"sku":"SKU004","productName":"Monitor","quantity":1,"price":2599.00}] ORD2026001 | user_1 | [{"sku":"SKU001","productName":"Laptop","quantity":1,"price":8999.00},{"sku":"SKU002","productName":"Wireless Mouse","quantity":2,"price":199.00},{"sku":"SKU003","productName":" Mechanical Keyboard","quantity":1,"price":699.00}] ORD2026003 | user_3 | [{"sku":"SKU005","productName":"Headphone","quantity":1,"price":599.00},{"sku":"SKU006","productName":"Power Bank","quantity":1,"price":299.00}] (3 rows) ``` 每行数据对应一个订单，items字段返回完整的JSON数组字符串，但无法直接对数组内的商品进行统计和筛选。一个订单会包含多个商品记录，公司需要知道每个订单购买了哪些商品、各商品的购买数量和单价，以及配送方式等信息，以便进行销售统计、库存管理和物流分析。 **场景需求分析** **：** - 统计各商品的销售数量和销售额。 - 分析不同快递公司的配送订单量。 - 查询特定状态订单的商品明细。 - 计算每个订单的商品种类数和总金额。 **数据特点：** - 订单与商品为一对多关系，一个订单可包含多个商品。 - 商品信息存储在JSON数组中，需要**展开为多行**才能进行统计分析。 - 配送信息为嵌套JSON对象，需要**逐层提取字段值**。 #### JSON数组元素转为多行示例 1. 创建示例订单表orders，并插入数据。 ``` CREATE TABLE orders ( order_id VARCHAR(50), customer_name VARCHAR(100), order_info TEXT ); INSERT INTO orders VALUES ('ORD2026001', 'user_1', '{"orderNo":"PO20260901001","totalAmount":15999.00,"status":"completed","items":[{"sku":"SKU001","productName":"Laptop","quantity":1,"price":8999.00},{"sku":"SKU002","productName":"Wireless Mouse","quantity":2,"price":199.00},{"sku":"SKU003","productName":"Mechanical Keyboard","quantity":1,"price":699.00}],"shipping":{"address":"Building 1, Mountain Street, H District","phone":"6666666","express":"DHL Express"}}'), ('ORD2026002', 'user_2', '{"orderNo":"PO20260901002","totalAmount":2599.00,"status":"shipped","items":[{"sku":"SKU004","productName":"Monitor","quantity":1,"price":2599.00}],"shipping":{"address":"Building 2, Garden Street, K District","phone":"7777777","express":"UPS Logistics"}}'), ('ORD2026003', 'user_3', '{"orderNo":"PO20260901003","totalAmount":899.00,"status":"pending","items":[{"sku":"SKU005","productName":"Headphone","quantity":1,"price":599.00},{"sku":"SKU006","productName":"Power Bank","quantity":1,"price":299.00}],"shipping":{"address":"Building 3, Central Street, S District","phone":"8888888","express":"GLS Express"}}'); ``` 2. JSON数组元素转多行（商品列表展开）。 ``` SELECT order_id, customer_name, item->>'sku' AS sku, item->>'productName' AS product_name, (item->>'quantity')::INT AS quantity, (item->>'price')::NUMERIC(10,2) AS price FROM ( SELECT order_id, customer_name, json_array_elements(order_info::json->'items') AS item FROM orders ) t; ``` 返回结果： ``` order_id | customer_name | sku | product_name | quantity | price ------------+---------------+--------+---------------------+----------+--------- ORD2026001 | user_1 | SKU001 | Laptop | 1 | 8999.00 ORD2026001 | user_1 | SKU002 | Wireless Mouse | 2 | 199.00 ORD2026001 | user_1 | SKU003 | Mechanical Keyboard | 1 | 699.00 ORD2026003 | user_3 | SKU005 | Headphone | 1 | 599.00 ORD2026003 | user_3 | SKU006 | Power Bank | 1 | 299.00 ORD2026002 | user_2 | SKU004 | Monitor | 1 | 2599.00 (6 rows) ``` 3. 提取嵌套对象字段（配送信息）。 ``` SELECT order_id, customer_name, order_info::json->>'orderNo' AS order_no, order_info::json->>'totalAmount' AS total_amount, order_info::json->>'status' AS status, order_info::json->'shipping'->>'address' AS shipping_address, order_info::json->'shipping'->>'phone' AS shipping_phone, order_info::json->'shipping'->>'express' AS express_company FROM orders; ``` 返回结果： ``` order_id | customer_name | order_no | total_amount | status | shipping_address | shipping_phone | express_company ------------+---------------+---------------+--------------+-----------+-----------------------------------------+----------------+----------------- ORD2026001 | user_1 | PO20260901001 | 15999.00 | completed | Building 1, Mountain Street, H District | 6666666 | DHL Express ORD2026003 | user_3 | PO20260901003 | 899.00 | pending | Building 3, Central Street, S District | 8888888 | GLS Express ORD2026002 | user_2 | PO20260901002 | 2599.00 | shipped | Building 2, Garden Street, K District | 7777777 | UPS Logistics (3 rows) ``` 4. 综合查询（订单+商品+配送）。 ``` SELECT o.order_id, o.customer_name, o.order_info::json->>'orderNo' AS order_no, t.item->>'productName' AS product_name, (t.item->>'quantity')::INT AS quantity, (t.item->>'price')::NUMERIC(10,2) AS unit_price, o.order_info::json->'shipping'->>'express' AS express_company, o.order_info::json->>'status' AS status FROM orders o, ( SELECT order_id, json_array_elements(order_info::json->'items') AS item FROM orders ) t WHERE o.order_id = t.order_id ORDER BY o.order_id; ``` 返回结果： ``` order_id | customer_name | order_no | product_name | quantity | unit_price | express_company | status ------------+---------------+---------------+---------------------+----------+------------+-----------------+----------- ORD2026001 | user_1 | PO20260901001 | Laptop | 1 | 8999.00 | DHL Express | completed ORD2026001 | user_1 | PO20260901001 | Wireless Mouse | 2 | 199.00 | DHL Express | completed ORD2026001 | user_1 | PO20260901001 | Mechanical Keyboard | 1 | 699.00 | DHL Express | completed ORD2026002 | user_2 | PO20260901002 | Monitor | 1 | 2599.00 | UPS Logistics | shipped ORD2026003 | user_3 | PO20260901003 | Headphone | 1 | 599.00 | GLS Express | pending ORD2026003 | user_3 | PO20260901003 | Power Bank | 1 | 299.00 | GLS Express | pending (6 rows) ``` #### JSON操作符和函数参考 表1JSON操作符和函数 | 操作符 | 说明 | 示例 | |:---|:---|:---| | -\> | 获取JSON对象字段（返回JSON类型）。 | ``` order_info::json->'shipping' ``` | | -\>\> | 获取JSON对象字段（返回文本类型）。 | ``` order_info::json->>'status' ``` | | #\> | 按路径获取JSON值。 | ``` order_info::json#>'{shipping,address}' ``` | | #\>\> | 按路径获取文本值。 | ``` order_info::json#>>'{shipping,express}' ``` | | json_array_elements() | JSON数组展开为多行。 | ``` json_array_elements(order_info::json->'items') ``` | | json_array_length() | 获取数组长度。 | ``` json_array_length(order_info::json->'items') ``` | #### 操作注意事项 1. TEXT类型字段需先用"::json"转换为JSON类型才能使用JSON操作符。 2. "-\>"返回JSON类型，可继续链式操作；"-\>\>"返回文本类型，用于最终取值。 3. 数值类型字段建议用"::INT"或"::NUMERIC"转换后进行计算。 4. 如果JSON数组可能为空，可使用LEFT JOIN避免丢失主表数据，空数组对应字段显示为空。