Expanding JSON Array Elements and Extracting Object Fields

Scenario

An e-commerce company has an order information table with three fields: order_id, customer_name, and order_info. The order_info field stores data in JSON format, including the order No., total amount, order status, product list (an items array that contains information such as the SKU, product name, quantity, and unit price), and delivery information (a shipping object that contains the delivery address, phone number, and courier company).

Query the order information table:

1
2
3
4
5

SELECT
    order_id,
    customer_name,
    order_info::json->'items' AS items
FROM orders; 

The query result is as follows:

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)

Each row of data corresponds to an order. The items field returns a complete JSON array string, but you cannot directly collect statistics on or filter products in the array. An order contains multiple product records. The company wants to know what products are purchased in each order, the quantity and unit price of each product, and the delivery method for sales statistics, inventory management, and logistics analysis.

Requirement analysis:

• Collect statistics on the sales volume and revenue of each product.
• Analyze the number of delivery orders of different courier companies.
• Query product details of orders in a specific status.
• Calculate the number of product types and total amount of each order.

Data characteristics:

• An order can contain multiple products.
• Product information is stored in a JSON array and needs to be expanded into multiple rows for statistical analysis.
• Delivery information is a nested JSON object, and field values need to be extracted layer by layer.

Expanding a JSON Array into Multiple Rows

1. Create a sample order table orders and insert data into the table.

   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. Expand a JSON array into multiple rows (expand the product list).

   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;

   Return result:

   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. Extract fields from a nested object (delivery information).

   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;

   Return result:

   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. Query order, product, and delivery information.

   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;

   Return result:

   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 Operators and Functions

1

order_info::json->'shipping'

1

order_info::json->>'status'

1

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

1

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

1

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

1

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

Precautions

1. To use JSON operators for TEXT fields, convert the fields into fields of the JSON type using ::json.
2. The -> operator returns a JSON object, allowing for further chain operations. The ->> operator returns a text object, which is used as the final value.
3. You are advised to convert numeric fields using ::INT or ::NUMERIC before calculation.
4. If a JSON array may be empty, you can use LEFT JOIN to prevent data loss in the primary table. The field corresponding to an empty array is empty.