CODEWITHSUNDEEP
google-bigquery
user2268997
user2268997

Reputation: 1391

Generating JSON on the fly with BigQuery

BigQuery has a TO_JSON_STRING() function that converts the result of SQL expressions to json strings. I'm trying to figure out how to use it with a data structure that has a nested array represented as a one-to-many relationship in BigQuery's tables.

This is the query I'm trying to run:

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

I get this error from BigQuery

Scalar subquery produced more than one element

The final objective with be to get the account_id into the json also, and persisted into a string column.

Upvotes: 0

Views: 1799

Answers (2)

ktopcuoglu
ktopcuoglu

Reputation: 146

Here is another solution;

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

Output will look like: sql result

Upvotes: 2

jszule
jszule

Reputation: 432

You can join it first then generate the json, like this:

select TO_JSON_STRING(t) from (select account.account_id as acc_id, skills.* from account inner join skills using(account_id)) as t

enter image description here

Upvotes: 1

Related Questions