英文:
Appending data to existing string_agg in bigquery
问题
当一个人发起ping或电话时，我们应该能够显示这些数据，如果他没有这样做，应该显示为null，但当他多次ping或电话时，它将追加为多个数组。
以下是我现在获取到的示例数据：
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"}
]
但我期望的是
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"}
]
以下是我正在使用的查询：
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
我尝试过array_agg，但它没有按预期工作。
英文:
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
答案1
得分: 0
以下是翻译好的代码部分：
-- 示例数据
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"
)
-- 查询开始
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;
查询结果
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论