Question 110440 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

BigQuery Merge and Sum JSON Documents

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}

Tags:

2

Answers


  1. 0

    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

    enter image description here

    Login or Signup to reply.
  2. 0

    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

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search