I have data in a BigQuery table where every row is an item in an ecommerce order, for example here are rows showing the purchase of three items across two orders:
| Order number | Product | Quantity |
|---|---|---|
| 001 | ABC | 1 |
| 001 | DEF | 2 |
| 002 | GHI | 1 |
I need to create a JSON list for every order in the below format, which using the example data above would look like this for order 001:
[ {product_id: "ABC", quantity:1},{product_id: "DEF", quantity:2} ]
How can I achieve this format in BigQuery SQL?
CodePudding user response：
As @aleix-cc pointed out, this can be easily done with the 