如何根據條件對 JSON 數組中的嵌套值求和

我在 Postgres 14.2 中有一個包含 3 列的表：name, name_adds, aditional.

這些規則：

1. name並且name_adds可以填充相同的值或不填充。範例：name (john) 和 name_adds (doe) 或兩者的值為 (john)
2. 列additional具有以下兩種格式之一：

格式一：

{"default":[{"value_1": 100, "value_2": 0.1},{"value_1": 200, "value_2": 0.2}], "non_default":[{"value_1": 200, "value_2": 0.1}, {"value_1": 400, "value_2": 0.1}]}

格式 2：

[{"value_1": 10,"value_2": 11},{"value_1": 1,"value_2": 19} 3. if (name = name_adds) sum default -> value_1 4. if (name != name_adds) sum non_default -> value_1

如何根據第3點和第 4點中的規則對第2點的格式 1 或 2 求和？

範例表：

name    name_adds   additional
---------------------------------------------------------------
john    john      {"default":[{"value_1": 100, "value_2": 0.1}, 
                 {"value_1": 200, "value_2": 0.2}],"non_default": 
                 [{"value_1": 200, "value_2": 0.1}, {"value_1": 400, 
                  "value_2": 0.1}]}

john     doe     {"default":[{"value_1": 100, "value_2": 0.1}, 
                 {"value_1": 200, "value_2": 0.2}],"non_default": 
                 [{"value_1": 200, "value_2": 0.1}, {"value_1": 400, 
                  "value_2": 0.1}]}

downy    downy    [{"value_1": 10, "value_2": 11},{"value_1": 1,"value_2": 19}]

downy    dan      [{"value_1": 10, "value_2": 11},{"value_1": 1,"value_2": 19}]

預期結果：

name    name_adds   sum_result
---------------------------------------------------------------
john    john        300

john     doe        600

downy    downy      11

downy    dan        11

我在這個線上展示中嘗試了一個查詢，但沒有得到預期的結果。

SELECT a.name, a.name_adds, sub.*
FROM   test_json a
CROSS  JOIN LATERAL (
  SELECT sum((obj ->> 'value_1')::int) AS sum_result  -- or numeric?
  FROM   json_array_elements(COALESCE(CASE WHEN name = name_adds
                                           THEN additional::json -> 'default'
                                           ELSE additional::json -> 'non_default' END
                                    , additional::json)) obj
  ) sub;

db<>在這裡擺弄

這依賴於additional::json -> 'default'（或'non_default'分別）在“格式 2”的情況下為 NULL，在這種情況下，我們additional::json直接使用“格式 1” COALESCE。

然後 unnest with json_array_elements()，並提取 sum value_1。

該列additional應以 type json(or jsonb) 開頭。

很有可能，使用規範化的關係設計完全取代高度規則的 JSON 列會更好。

引用自：https://dba.stackexchange.com/questions/313252