如何在PostgreSQL中通过键提取JSON对象的值并在函数中应用于增改操作

我来帮你搞定这个问题，你的查询里有两个关键小问题导致无法正确获取Total字段的值：

• 你错误引用了表名my_table，而非存储JSON数据的列名jsonblob
• 访问JSON键时需要用字符串引号包裹键名（比如'Total'），PostgreSQL才能识别这是JSON对象的键

修正后的基础查询 #

下面是可以正确提取Total值的查询：

WITH my_table ( jsonblob ) AS (
 VALUES (
 '{ "Customer_IntegrationCode": "558889999", "XretialOrderCode": "000020430", "ShippingAddress": "Cairo, Nasr City, 01128777733", "ShippingAddress_IntegrationCode": null, "PaymentOption": 1, "CreationDate": "2021-01-04T07:38:57.033Z", "Total": 73.0, "Currency": "EGP", "Note": null, "ShippingCost": 15.0, "CODFee": 25.0, "ShipmentProvider": null, "Plateform": 1, "SubTotal": 33.0, "TotalDiscountAmount_PerOrderLevel": 0, "OriginalSubTotal": 33.0, "TaxPercentage": null, "TaxValue": null, "Store_IntegrationCode": "1234567", "OrderItems": [ { "productCode": "12345678", "SKU": "23456789", "Qty": 3, "UnitPrice": 11.0, "NetPrice": 11.0, "SKUDiscount": 0, "Total": 33.0, "ShipmentCost": 0.0, "SubTotal": 33.0 }, { "productCode": "999999", "SKU": "988888", "Qty": 3, "UnitPrice": 11.0, "NetPrice": 11.0, "SKUDiscount": 0, "Total": 33.0, "ShipmentCost": 0.0, "SubTotal": 33.0 } ] } ' :: jsonb
 )
)
-- 使用->>直接获取文本类型的Total值，或者用->获取JSON原生类型
SELECT jsonblob ->> 'Total' AS order_total_text,
       jsonblob -> 'Total' AS order_total_json
FROM my_table;

关键JSONB操作符说明 #

PostgreSQL提供了几个常用的JSONB操作符，你可以根据场景选择：

• ->：返回JSON原生类型的字段值（比如数字会保留JSON数值类型）
• ->>：返回文本类型的字段值（适合直接用于INSERT/UPDATE的普通数据库列）
• #>>：用于提取嵌套路径的值，比如从OrderItems数组中提取第一个商品的Total：

  SELECT jsonblob #>> '{OrderItems,0,Total}' AS first_item_total
  FROM my_table;
  

在函数中使用JSONB进行INSERT/UPDATE #

假设你要把这个JSON数据插入到订单表和订单项表，这里是一个实用的示例函数：

CREATE OR REPLACE FUNCTION insert_order_from_json(p_order_json jsonb)
RETURNS void AS $$
BEGIN
  -- 插入主订单记录
  INSERT INTO orders (
    customer_integration_code,
    xretial_order_code,
    total,
    currency,
    shipping_cost,
    cod_fee
  ) VALUES (
    p_order_json ->> 'Customer_IntegrationCode',
    p_order_json ->> 'XretialOrderCode',
    (p_order_json ->> 'Total')::numeric,
    p_order_json ->> 'Currency',
    (p_order_json ->> 'ShippingCost')::numeric,
    (p_order_json ->> 'CODFee')::numeric
  );

  -- 插入订单项记录，用jsonb_to_recordset解析数组
  INSERT INTO order_items (
    order_code,
    product_code,
    sku,
    qty,
    unit_price,
    total
  )
  SELECT
    p_order_json ->> 'XretialOrderCode',
    item.productCode,
    item.SKU,
    item.Qty,
    item.UnitPrice::numeric,
    item.Total::numeric
  FROM jsonb_to_recordset(p_order_json -> 'OrderItems') AS item(
    productCode text,
    SKU text,
    Qty int,
    UnitPrice numeric,
    Total numeric
  );
END;
$$ LANGUAGE plpgsql;

-- 调用函数（替换为你的完整JSON对象）
SELECT insert_order_from_json(
  '{ "Customer_IntegrationCode": "558889999", ... }'::jsonb
);

你可以根据自己的实际表结构调整字段名和类型转换逻辑。

内容的提问来源于stack exchange，提问作者Tarek Ramadan