Appending data to existing string_agg in bigquery

Question

when a person pings or calls, we should be able to show that data and if he doesn't do, it should show as null but when he makes multiple pings or calls, it is appending as multiple array.

below is the sample data in that I'm getting now:

1,
veer,
[
    {"name":"ping","date":"2023-05-30T12:30:53Z"},
    {"name":null,"date":"2023-05-30T12:30:53Z"}
],
[
    {"name":"ping","date":"2023-05-31T14:32:50Z"},
    {"name":"call","date":"2023-05-31T14:32:50Z"}
],
[
    {"name":null,"date":"2023-05-31T14:53:12Z"},
    {"name":"call","date":"2023-05-31T14:53:12Z"}
]

but what i'm expecting is

1, 
veer,
[
    {"name":"ping","date":"2023-05-30T12:30:53Z"},
    {"name":null,"date":"2023-05-30T12:30:53Z"},
    {"name":"ping","date":"2023-05-31T14:32:50Z"},
    {"name":"call","date":"2023-05-31T14:32:50Z"},
    {"name":null,"date":"2023-05-31T14:53:12Z"},
    {"name":"call","date":"2023-05-31T14:53:12Z"}
]

Below is the query, I'm using

select id, name,
    string_agg(if(
        recent_ping is not null or recent_call is not null,
        to_json_string(
            [
                struct(recent_ping as name,date_ts as date),
                struct(recent_call as name,date_ts as date)
            ]
         ), 
         null
    ) ) as chnl  
from `pz-dev-it-0.dt_cust_tbls.ort_stg`  
group by id,name

I have tried array_agg but it is not working as expected.

Could you someone please guide me on this

Answer 1

You can consider below.

-- sample data
WITH `pz-dev-it-0.dt_cust_tbls.ort_stg` AS (
  SELECT 1 id, 'veer' name, 'ping' recent_ping, null recent_call, "2023-05-30T12:30:53Z" date_ts UNION ALL
  SELECT 1 id, 'veer' name, 'ping', 'call', "2023-05-31T14:32:50Z" UNION ALL
  SELECT 1 id, 'veer' name, null, 'call', "2023-05-31T14:53:12Z"
)
-- query starts here
SELECT id, name,
       TO_JSON_STRING(ARRAY_CONCAT_AGG([
         struct(recent_ping as name,date_ts as date),
         struct(recent_call as name,date_ts as date)
       ])) AS chnl 
  FROM `pz-dev-it-0.dt_cust_tbls.ort_stg`
 GROUP BY 1, 2;

query result