BigQuery Merge and Sum JSON Documents-CodePudding

I want to merge JSON string in a table and sum its value after group for Eg:-

2023, {"hen":4, "owl":3}
2023, {"crow":4, "owl":2}
2022, {"owl":6, "crow":2}
2022, {"hen":5}
2021, {"hen":2, "crow":1}

Result could be like below

2023, {"hen":4, "owl":5, "crow":4}
2022, {"hen":5, "owl":6, "crow":2}
2021, {"hen":2, "crow":1}

CodePudding user response：

below might be an option when you don't know json object keys beforehand.

WITH sample_table AS (
  SELECT 2023 year, '{"hen":4, "owl":3}' json UNION ALL
  SELECT 2023, '{"crow":4, "owl":2}' UNION ALL
  SELECT 2022, '{"owl":6, "crow":2}' UNION ALL
  SELECT 2022, '{"hen":5}' UNION ALL
  SELECT 2021, '{"hen":2, "crow":1}'
)
SELECT year, '{' || STRING_AGG(k || ':' || v, ', ') || '}' json FROM (
  SELECT year, 
         SPLIT(kv, ':')[OFFSET(0)] k, 
         SUM(SAFE_CAST(SPLIT(kv, ':')[OFFSET(1)] AS INT64)) v
    FROM sample_table, UNNEST(SPLIT(TRIM(json, '{}'), ', ')) kv
   GROUP BY 1, 2
) GROUP BY 1;

Query results

CodePudding user response：

Consider also below approach

create temp function get_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
  """;
create temp function get_values(input string) returns array<string> language js as """ 
  return Object.values(JSON.parse(input));
  """;
select distinct year, 
  '{' || string_agg(format('"%s":%i', key ,sum(cast(val as int64))), ', ') over(partition by year) || '}' json
from your_table, unnest(get_keys(json)) key with offset
join unnest(get_values(json)) val with offset
using (offset)
group by year, key

if applied to sample data in your question - output is