Create a nested json string in bigquery-CodePudding

i want to create a nested json string where one phone number returns all product_name in one row only

I have tried but the output of TO_JSON_STRING isn't what i need. Here is the image of the query result Image

Here the query that i used:

select cus.phone,
       TO_JSON_STRING(STRUCT(
       line.product_name
         )) as attributes
from `dtm_med.t1_customer` cus
left join `dtm_med.t2_toa_total_sales_line` line on cus.phone = line.phone
left join `med_product.raw_cms_users` u on  u.id = line.patient_id
where date_diff(current_date(), date(latest_order_date), week) < 26
and sale_contribution > 3000000 
and transaction_count > 2

I want all the product_name in one row and only one phone number, not duplicated. is there a way to do that in bigquery?

CodePudding user response：

Here This might Help you. Credit to the Helpers of this question:

listagg function alternative in bigquery

You can use STRING_AGG() for csv or ARRAY_AGG() if you want a list-like structure (array). Then GROUP BY the other two columns.