文档首页/ 数据仓库服务 DWS/ 最佳实践/ 数据开发/ JSON数组元素展开及对象字段提取实践
更新时间：2026-09-07 GMT+08:00
查看PDF
分享

JSON数组元素展开及对象字段提取实践

场景介绍

某电商公司，订单信息表有三个字段：订单ID（order_id）、客户名称（customer_name）、订单详情（order_info）。其中订单详情 order_info以JSON格式存储了订单号、总金额、订单状态、商品列表（items数组，包含SKU、商品名称、数量、单价等信息）和配送信息（shipping对象，包含收货地址、联系电话、快递公司等）。

对订单信息表的查询如下：

1
2
3
4
5
 
SELECT
    order_id,
    customer_name,
    order_info::json->'items' AS items
FROM orders;

查询结果如下：

1
2
3
4
5
6
7
8
9
 
 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，并插入数据。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    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数组元素转多行（商品列表展开）。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    		 
    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;

    返回结果：

    1
2
3
4
5
6
7
8
9
    		 
     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. 提取嵌套对象字段（配送信息）。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    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;
    返回结果： 
    1
2
3
4
5
6
    		 
     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. 综合查询（订单+商品+配送）。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    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;
    返回结果： 
    1
2
3
4
5
6
7
8
9
    		 
      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操作符和函数参考

表1 JSON操作符和函数

操作符

说明

示例

->

获取JSON对象字段（返回JSON类型）。

 
1
 
order_info::json->'shipping'

->>

获取JSON对象字段（返回文本类型）。

 
1
 
order_info::json->>'status'

#>

按路径获取JSON值。

 
1
 
order_info::json#>'{shipping,address}'

#>>

按路径获取文本值。

 
1
 
order_info::json#>>'{shipping,express}'

json_array_elements()

JSON数组展开为多行。

 
1
 
json_array_elements(order_info::json->'items')

json_array_length()

获取数组长度。

 
1
 
json_array_length(order_info::json->'items')

操作注意事项

  1. TEXT类型字段需先用“::json”转换为JSON类型才能使用JSON操作符。
  2. “->”返回JSON类型，可继续链式操作；“->>”返回文本类型，用于最终取值。
  3. 数值类型字段建议用“::INT”或“::NUMERIC”转换后进行计算。
  4. 如果JSON数组可能为空，可使用LEFT JOIN避免丢失主表数据，空数组对应字段显示为空。
父主题： 数据开发

相关文档