如何在PostgreSQL中构建指定嵌套结构的订单JSON？

当前生成嵌套订单JSON的SQL方案存在两个问题：

• 子项的子元素（如discounts、taxes数组）无法正常生成；
• 订单包含多个商品时，每个商品会生成单独JSON行，需要每个订单对应一行JSON。

{
  "order_id": "string",
  "createdAt": 0,
  "orderNumber": "string", 
  "tags": [
    "string"
  ],
  "lineItems": [
    {
      "line_id": "string",
      "productName": "string",
      "quantity": 0, 
      "productTags": [ 
        "string"
      ],
      "discounts": [
        {
          "title": "string", 
          "voucherCode": "string", 
          "discountAmount": 0.1 
        }
      ],
      "taxes": [
        {
          "tactitle": "string",
          "taxRate": 0.1,
          "taxAmount": 0.1
        }
      ],
      "totalAmountBeforeTaxesAndDiscounts": 0.1,
      "totalAmountAfterTaxesAndDiscounts": 0.1
    }
  ],
  "shipping": {
    "city": "string",
    "zipOrPostalCode": "string",
    "providerDescriptor": "string", 
    "shippingTotalAmountBeforeTaxAndDiscounts": 0.1,
    "discounts": [
      {
        "title": "string",
        "voucherCode": "string",
        "discountAmount": 0.1
      }
    ],
    "taxes": [
      {
        "title": "string",
        "taxRate": 0.1,
        "taxAmount": 0.1
      }
    ],
    "shippingTotalAmountAfterTaxAndDiscounts": 0.1
  },
  "transactionCosts": 0.1,
  "customer": {
    "id": "string",
    "email": "string",
    "tags": [ 
      "string"
    ]
  },
  "optional": {
    "googleAnalyticsTransactionId": "string",
    "orderSourceName": "string",
    "orderChannelName": "string",
    "orderPlatformName": "string"
  }
}

With  orders as
(
SELECT "order_id",
      "createdAt",
      "orderNumber", 
      STRING_AGG(tags,',') as tags
FROM  orders o
        )
,lineItems as
(
SELECT   "line_id",
          order_id
          "productName",
          "quantity", 
          STRING_AGG(productTags,',') as "productTags",
          "vouchertitle", 
          "voucherCode",
          "discountAmount",
          "taxtitle",
          "taxRate",
          "taxAmount",
          "totalAmountBeforeTaxesAndDiscounts",
          "totalAmountAfterTaxesAndDiscounts"
FROM items 
)
,shipments as
(
SELECT  order_id,
        "city",
        "zipOrPostalCode",
        "providerDescriptor", 
        "shippingTotalAmountBeforeTaxAndDiscounts",
        "title",
        "voucherCode",
        "discountAmount", 
        "taxtitle",
        "taxRate",
        "taxAmount",
        "shippingTotalAmountAfterTaxAndDiscounts"
FROM shipments s
INNER JOIN orders o ON s.order_id=o.id
)
,customer AS
(
SELECT 
        order_id,
        "customer_id",
        "email"
         STRING_AGG("customer_tags") as tags
FROM  customers c 
)
, optional AS
(
SELECT  order_id,
        "googleAnalyticsTransactionId",
        "source",
        "channel",
        "platform"
    FROM analytics
)
   , base as (
  select   
          cm.*,i as lineItems 
          , s as shipment
          , c as customer
          , ga as  "optionalIdentifiers"
  from 
    orders cm
    LEFT JOIN lineItems i ON  cm.order_id = i.order_id 
    LEFT JOIN shipments s ON cm.order_id=s.order_id
    LEFT JOIN customer c ON cm."order_id"=c.order_id
    LEFT JOIN optional ga ON cm."order_id"=ga.order_id     
 )
 select row_to_json(c) as "data" from base c

create temp table orders(order_id int,"createdAt" date,"orderNumber" text,tags text);
INSERT INTO orders(order_id,"createdAt","orderNumber",tags)
VALUES (1,'2022-12-09' , '10001', 'no tags'),
       (2,'2022-12-10' , '19999', 'tag1,tags 2');
 
create temp table lineItems(line_id int,order_id int,"productName" text,"quantity" int,
 "productTags" text,"vouchertitle" text,"voucherCode" text,"discountAmount" real,
"taxtitle" text,"taxRate" real,"taxAmount" real,"totalAmountBeforeTaxesAndDiscounts" real,
"totalAmountAfterTaxesAndDiscounts" real);
INSERT INTO lineItems(line_id ,order_id ,"productName" ,"quantity" ,
 "productTags" ,"vouchertitle" ,"voucherCode" ,"discountAmount" ,
"taxtitle" ,"taxRate" ,"taxAmount" ,"totalAmountBeforeTaxesAndDiscounts" ,
"totalAmountAfterTaxesAndDiscounts" )
VALUES (0,1,'Vitamin D',100,'Bio','Xmas campaign','XMAS001',1000,'TAX 10%',10,500,15000,14000),
       (2,1,'Vitamin C',50,'No Tags','Xmas campaign','XMAS001',500,'TAX 7%',7,33,1900,1400),
       (11,2,'Vitamin C',50,'No Tags','Xmas campaign','XMAS002',100,'TAX 7%',7,55,2500,2400)
;

create temp table shipments(order_id int,"city" text,"zipOrPostalCode" text,"providerDescriptor" text, 
"shippingTotalAmountBeforeTaxAndDiscounts" real,"title" text,"voucherCode" text,"discountAmount" real, 
"taxtitle" text,"taxRate" real,"taxAmount" real,"shippingTotalAmountAfterTaxAndDiscounts" real);
INSERT INTO shipments(order_id ,"city" ,"zipOrPostalCode" ,"providerDescriptor" , 
"shippingTotalAmountBeforeTaxAndDiscounts" ,"title" ,"voucherCode" ,"discountAmount" , 
"taxtitle" ,"taxRate" ,"taxAmount" ,"shippingTotalAmountAfterTaxAndDiscounts" )
VALUES (1,'Berlin','100203','DHL','100','Shipper','XMAS001',1000,'TAX 10%',10,500,1000),
  (2,'Milan','122203','Hermes','100','Shipp_001','XMAS002',1000,'TAX 7%',7,500,1000);

create temp table customer(  order_id int,"customer_id" int,"email" text,"customer_tags" text);
INSERT INTO customer(order_id ,"customer_id" ,"email" ,"customer_tags" )
VALUES (1,1900,'xxxx@gmail.com','new')
,      (2,2000,'yyyy@gmail.com','return');


create temp table optional( order_id int,"googleAnalyticsTransactionId" int,"source" text,"channel" text,"platform" text);
INSERT INTO optional(order_id ,"googleAnalyticsTransactionId" ,"source" ,"channel" ,platform)
VALUES (1,'9990001','facebook','paid marketing','mobile')
      ,(2,'7770001','gppgle','direct','mobile');

{
      "order_id": "1",
      "createdAt": "2022-12-09",
      "orderNumber": "10001", 
      "tags": ["no tags" ],
      "lineItems": [
        {
          "line_id": "0",
          "productName": "Vitamin D",
          "quantity": 100, 
          "productTags": [ "Bio"],
          "discounts": [
            {
              "title": "Xmas campaign", 
              "voucherCode": "XMAS001", 
              "discountAmount": 1000 
            }
          ],
          "taxes": [
            {
              "tactitle": "TAX 10%",
              "taxRate": 10,
              "taxAmount": 500
            }
          ],  
          "totalAmountBeforeTaxesAndDiscounts": 15000,
          "totalAmountAfterTaxesAndDiscounts": 14000
        },
          {
          "line_id": "2",
          "productName": "Vitamin C",
          "quantity": 50, 
          "productTags": [ "No Tags"],
          "discounts": [
            {
              "title": "Xmas campaign", 
              "voucherCode": "XMAS001", 
              "discountAmount": 500 
            }
          ],
          "taxes": [
            {
              "tactitle": "TAX 7%",
              "taxRate": 7,
              "taxAmount": 33
            }
          ],  
          "totalAmountBeforeTaxesAndDiscounts": 1900,
          "totalAmountAfterTaxesAndDiscounts": 1400
        }
      ],
      "shipping": {
        "city": "Berlin",
        "zipOrPostalCode": "100203",
        "providerDescriptor": "DHL", 
        "shippingTotalAmountBeforeTaxAndDiscounts": 100,
        "discounts": [
          {
            "title": "Shipper",
            "voucherCode": "XMAS001",
            "discountAmount": 1000
          }
        ],
        "taxes": [
          {
            "title": "TAX 10%",
            "taxRate": 10,
            "taxAmount": 500
          }
        ],
        "shippingTotalAmountAfterTaxAndDiscounts": 1000
      },
      "transactionCosts": 0.1,
      "customer": {
        "id": "1900",
        "email": "xxxx@gmail.com",
        "tags": [ 
          "new"
        ]
       },
      "optional": {
        "googleAnalyticsTransactionId": "9990001",
        "orderSourceName": "facebook",
        "orderChannelName": "paid marketing",
        "orderPlatformName": "mobile"
      }
    }      

核心思路是按订单分组聚合子数据，用PostgreSQL的JSON函数构建层级结构，避免多表直接关联导致的行膨胀。

修正后的SQL #

WITH order_tags AS (
    SELECT 
        order_id,
        string_to_array(tags, ',') AS tags_array
    FROM orders
),
line_items_agg AS (
    SELECT 
        order_id,
        json_agg(
            json_build_object(
                'line_id', line_id::text,
                'productName', "productName",
                'quantity', quantity,
                'productTags', string_to_array("productTags", ','),
                'discounts', json_build_array(
                    json_build_object(
                        'title', "vouchertitle",
                        'voucherCode', "voucherCode",
                        'discountAmount', "discountAmount"
                    )
                ),
                'taxes', json_build_array(
                    json_build_object(
                        'tactitle', "taxtitle",
                        'taxRate', "taxRate",
                        'taxAmount', "taxAmount"
                    )
                ),
                'totalAmountBeforeTaxesAndDiscounts', "totalAmountBeforeTaxesAndDiscounts",
                'totalAmountAfterTaxesAndDiscounts', "totalAmountAfterTaxesAndDiscounts"
            )
        ) AS lineItems
    FROM lineItems
    GROUP BY order_id
),
shipping_agg AS (
    SELECT 
        order_id,
        json_build_object(
            'city', "city",
            'zipOrPostalCode', "zipOrPostalCode",
            'providerDescriptor', "providerDescriptor",
            'shippingTotalAmountBeforeTaxAndDiscounts', "shippingTotalAmountBeforeTaxAndDiscounts",
            'discounts', json_build_array(
                json_build_object(
                    'title', "title",
                    'voucherCode', "voucherCode",
                    'discountAmount', "discountAmount"
                )
            ),
            'taxes', json_build_array(
                json_build_object(
                    'title', "taxtitle",
                    'taxRate', "taxRate",
                    'taxAmount', "taxAmount"
                )
            ),
            'shippingTotalAmountAfterTaxAndDiscounts', "shippingTotalAmountAfterTaxAndDiscounts"
        ) AS shipping
    FROM shipments
),
customer_agg AS (
    SELECT 
        order_id,
        json_build_object(
            'id', "customer_id"::text,
            'email', "email",
            'tags', string_to_array("customer_tags", ',')
        ) AS customer
    FROM customer
),
optional_agg AS (
    SELECT 
        order_id,
        json_build_object(
            'googleAnalyticsTransactionId', "googleAnalyticsTransactionId"::text,
            'orderSourceName', "source",
            'orderChannelName', "channel",
            'orderPlatformName', platform
        ) AS optional
    FROM optional
)
SELECT 
    json_build_object(
        'order_id', o.order_id::text,
        'createdAt', o."createdAt"::text,
        'orderNumber', o."orderNumber",
        'tags', ot.tags_array,
        'lineItems', COALESCE(lia.lineItems, '[]'::json),
        'shipping', COALESCE(sa.shipping, '{}'::json),
        'transactionCosts', 0.1,
        'customer', COALESCE(ca.customer, '{}'::json),
        'optional', COALESCE(oa.optional, '{}'::json)
    ) AS data
FROM orders o
JOIN order_tags ot ON o.order_id = ot.order_id
LEFT JOIN line_items_agg lia ON o.order_id = lia.order_id
LEFT JOIN shipping_agg sa ON o.order_id = sa.order_id
LEFT JOIN customer_agg ca ON o.order_id = ca.order_id
LEFT JOIN optional_agg oa ON o.order_id = oa.order_id
GROUP BY o.order_id, o."createdAt", o."orderNumber", ot.tags_array, lia.lineItems, sa.shipping, ca.customer, oa.optional;

关键修正说明 #

1. 解决多行重复问题：所有子表（lineItems、shipping等）先通过GROUP BY order_id聚合，用json_agg将同订单的多条子数据转为JSON数组，再与主订单表关联，避免join导致的行复制。
2. 生成嵌套子元素：用json_build_object构建层级对象，json_build_array生成数组类型的子节点（如discounts、taxes），string_to_array将逗号分隔的字符串转为JSON数组，匹配目标结构要求。
3. 类型适配：将数值类型的ID、日期转为字符串，符合目标JSON的string类型定义。
4. 空值兼容：用COALESCE处理子表无数据的场景，返回空数组/空对象，保证JSON结构完整性。

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