I have an Athena table with one column having JSON and key/value pairs. Ex:

Select test_client, test_column from ABC;

test_client, test_column
john, {"d":13, "e":210}
mark, {"a":1,"b":10,"c":1}
john, {"e":100,"a":110,"b":10, "d":10}
mark, {"a":56,"c":11,"f":9, "e": 10}

And I need to sum the values corresponding to keys and return in some sort like the below: return o/p format doesn't matter. I want to sum it up.

john: d: 23, e:310, a:110, b:10
mark: a:57, b:10, c:12, f:9, e:10

CodePudding user response：

It is a combination of a few useful functions in Trino:

WITH example_table AS
 (SELECT 'john' as person, '{"d":13, "e":210}' as _json UNION ALL
  SELECT 'mark', ' {"a":1,"b":10,"c":1}' UNION ALL
  SELECT 'john', '{"e":100,"a":110,"b":10, "d":10}' UNION ALL
  SELECT 'mark', '{"a":56,"c":11,"f":9, "e": 10}')
SELECT person, reduce(
    array_agg(CAST(json_parse(_json) AS MAP(VARCHAR, INTEGER))),
    MAP(ARRAY['a'],ARRAY[0]),
    (s, x) -> map_zip_with(
                        s,x, (k, v1, v2) -> 
                             if(v1 is null, 0, v1)  
                             if(v2 is null, 0, v2)
                    ),
    s -> s
)
FROM example_table
GROUP BY person

• json_parse - Parses the string to a JSON object
• CAST ... AS MAP... - Creates a MAP from the JSON object
• array_agg - Aggregates the maps for each Person based on the group by
• reduce - steps through the aggregated array and reduce it to a single map
• map_zip_with - applies a function on each similar key in two maps
• if(... is null ...) - puts 0 instead of null if the key is not present