I have a table with 3 columns in Postgres 14.2: name, name_adds, aditional.

And these rules:

  1. name and name_adds can be filled with the same value or not. Example: name (john) and name_adds (doe) or both value is (john)

  2. column additional has one of two formats:

    format 1:

    {"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}]}

    format 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

How can I sum format 1 or 2 from point 2. based on the rules in point 3. and 4.?

Example table:

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}]

Expected result:

name    name_adds   sum_result
john    john        300

john     doe        600

downy    downy      11

downy    dan        11

I tried a query in this online demo, but didn't manage to get the expected result.

1 Answer 1

SELECT a.name, a.name_adds, sub.*
FROM   test_json a
   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<>fiddle here

This relies on additional::json -> 'default' (or 'non_default', respectively) being NULL in case of "format 2", in which case we take additional::json directly for "format 1" using COALESCE.
Then unnest with json_array_elements(), and sum extracted value_1.

The column additional should be type json (or jsonb) to begin with.

Chances are, you would be much better off with a normalized relational design replacing that highly regular JSON column completely.

  • cool it's work, you are right about additional type, I will change it. Thanks a lot
    – wuku
    Commented Jun 13, 2022 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.