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

score 0 · Accepted Answer · answered Jun 15 '23 at 00:11

0

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

answered Jun 15 '23 at 00:11

Jaytiger

11,626
2
5
15

Thanks @jaytiger. This was really helpful. but when recent _call and recent_ping are null , chnl column should show just null, but is showing whole json structure with name as null [{"name":null,"date":"2023-05-16T01:27:26Z"},{"name":null,"date":"2023-05-16T01:27:26Z"},{"name":null,"date":"2023-05-16T01:08:23Z"},{"name":null,"date":"2023-05-16T01:08:23Z"}] – jay Jun 16 '23 at 15:17
What do you mean `chnl column should show just null,`? Do you expect `[{...}, null, {...}]` ? – Jaytiger Jun 16 '23 at 15:35

Appending data to existing string_agg in bigquery

1 Answers1