Postgres PLpgSQL JSON和

Question

我试图在PLpgSQL (Postgresv9.5.5)中计算一些JSON值的总和，但仍停留在逻辑上。

对于此数据集：

{
  clientOrderId: 'OR836374647',
  status: 'PENDING',
  clientId: '583b52ede4b1a3668ba0dfff',
  sharerId: '583b249417329b5b737ad3ee',
  buyerId: 'abcd12345678',
  buyerEmail: 'test@test.com',
  lineItems: [{
    name: faker.commerce.productName(),
    description: faker.commerce.department(),
    category: 'test',
    sku: faker.random.alphaNumeric(),
    quantity: 3
    price: 40
    status: 'PENDING'
  }, {
    name: faker.commerce.productName(),
    description: faker.commerce.department(),
    category: 'test',
    sku: faker.random.alphaNumeric(),
    quantity: 2,
    price: 30,
    status: 'PENDING'
  }

我试图获得每一行的所有lineItems的小计(即，每一行的数量*价格，然后是该行的这些值的总和)。因此，对于上面的示例，返回的值应该是180。

我做到了这一点，但这是返回表中所有lineItems的总计，而不是按行分组。

WITH line_items AS (SELECT jsonb_array_elements(line_items) as line_items FROM public.order),
line_item_totals AS (SELECT line_items->>'quantity' AS quantity, line_items->>'price' AS price FROM line_items)

SELECT (quantity::int * price::numeric) AS sub_total FROM line_item_totals;

我确信修复是简单的，但我不知道如何使用JSON字段来实现这一点。

Answer 1

请始终包括您正在使用的Postgres版本。您的JSON看起来也不正确。下面是一个示例，说明如何使用json类型和有效的 json文档来完成这一任务。

with t(v) as ( VALUES
  ('{
      "clientOrderId": "OR836374647",
      "status": "PENDING",
      "clientId": "583b52ede4b1a3668ba0dfff",
      "sharerId": "583b249417329b5b737ad3ee",
      "buyerId": "abcd12345678",
      "buyerEmail": "test@test.com",
      "lineItems": [{
          "name": "name1",
          "description": "desc1",
          "category": "test",
          "sku": "sku1",
          "quantity": 3,
          "price": 40,
          "status": "PENDING"
        },
        {
          "name": "name2",
          "description": "desc2",
          "category": "test",
          "sku": "sku2",
          "quantity": 2,
          "price": 30,
          "status": "PENDING"
        }]
      }'::JSON)
)
SELECT
  v->>'clientOrderId' cId,
  sum((item->>'price')::INTEGER * (item->>'quantity')::INTEGER) subtotal
FROM
  t,
  json_array_elements(v->'lineItems') item
GROUP BY cId;

结果：

     cid     | subtotal 
-------------+----------
 OR836374647 |      180
(1 row)