主页 / dba / 问题 / 313252
Accepted
wuku
Asked: 2022-06-12 15:31:23 +0800 CST

如何根据条件对 JSON 数组中的嵌套值求和

  • 772

我在 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

我在这个在线演示中尝试了一个查询，但没有得到预期的结果。

postgresql json

1 个回答

  1. Best Answer
    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 列会更好。

    • 2

相关问题