google-bigquery - 使用 BigQuery 即时生成 JSON

Question

BigQuery 具有TO_JSON_STRING()将 SQL 表达式的结果转换为 json 字符串的函数。我试图弄清楚如何将它与具有嵌套数组的数据结构一起使用，该数组表示为BigQuery 表中的一对多关系。

这是我要运行的查询：

SELECT a.account_id, 
  TO_JSON_STRING((SELECT s.skill_id FROM skills s WHERE s.account_id = a.account_id))
FROM accounts a

我从 BigQuery 收到此错误

标量子查询产生了多个元素

最终目标是也将其account_id放入 json 中，并保留在字符串列中。

标签： google-bigquery

Answer 1

这是另一种解决方案；

with accounts as (
  select *
    from unnest([struct(1 as account_id, 'first acc' as account_name)
                ,struct(2 as account_id, 'second acc' as account_name)
                ,struct(3 as account_id, 'third acc' as account_name)
                ])
)
, skills as (
  select *
    from unnest([struct(1 as account_id, 1 as skill_id)
                ,struct(1 as account_id, 2 as skill_id)
                ,struct(1 as account_id, 3 as skill_id)
                ,struct(2 as account_id, 1 as skill_id)
                ,struct(2 as account_id, 4 as skill_id)
                ])
)
, nest as (
  select a.account_id
        ,any_value(a.account_name) as account_name
        ,to_json_string(ifnull(array_agg(s.skill_id ignore nulls),[])) as skills
  from accounts a
  left join skills s
  on a.account_id = s.account_id
  group by a.account_id
)
select *
from nest

输出将如下所示：