Generating Nested JSON for bigquery

Question

I have created a nested table in Bigquery. The input needs to be JSON. I need the input to be following example:

{"store_nbr":"1234","sls_dt":"2014-01-01 00:00:00", "Items":[{"sku":"3456", "sls_amt":"9.99", "discounts":[{"disc_nbr":"1","disc_amt":"0.99"},{"disc_nbr":"2","disc_amt":"1.00"}]]

I have the flattened tables in big query. I have read nest can help in pivoting for repeated json. I have seen an example in another thread which is very nice.

Nest multiple repeated fields in BigQuery

But that does only for one level nesting. I need to do two levels "Items" -> "Discounts". Any suggestions are appreciated.

Answer 1

Try below
You can polish nuances but overall it should give you good start!

SELECT *
FROM JS( 
  ( // input table 
    SELECT store_nbr, sls_dt, NEST(CONCAT(STRING(item_sku), '|', STRING(sls_amt), '|', STRING(discounts))) AS items
    FROM (
      SELECT store_nbr, sls_dt, item_sku, sls_amt, GROUP_CONCAT(CONCAT(STRING(disc_nbr), ',', STRING(disc_amt)), ';') AS discounts
      FROM 
        (SELECT 1234 AS store_nbr, "2014-01-01 00:00:00" AS sls_dt, 3456 AS item_sku, 9.99 AS sls_amt, 1 AS disc_nbr, 0.99 AS disc_amt),
        (SELECT 1234 AS store_nbr, "2014-01-01 00:00:00" AS sls_dt, 3456 AS item_sku, 9.99 AS sls_amt, 2 AS disc_nbr, 1.00 AS disc_amt),
        (SELECT 1234 AS store_nbr, "2014-01-01 00:00:00" AS sls_dt, 2345 AS item_sku, 7.99 AS sls_amt, 1 AS disc_nbr, 0.59 AS disc_amt),
        (SELECT 1234 AS store_nbr, "2014-01-01 00:00:00" AS sls_dt, 4567 AS item_sku, 7.99 AS sls_amt, 1 AS disc_nbr, 0.59 AS disc_amt),
        (SELECT 1234 AS store_nbr, "2014-01-01 00:00:00" AS sls_dt, 4567 AS item_sku, 7.99 AS sls_amt, 2 AS disc_nbr, 0.69 AS disc_amt),
        (SELECT 1234 AS store_nbr, "2014-01-01 00:00:00" AS sls_dt, 4567 AS item_sku, 7.99 AS sls_amt, 3 AS disc_nbr, 0.79 AS disc_amt),
        (SELECT 2345 AS store_nbr, "2014-01-02 00:00:00" AS sls_dt, 3456 AS item_sku, 9.99 AS sls_amt, 1 AS disc_nbr, 0.99 AS disc_amt),
        (SELECT 2345 AS store_nbr, "2014-01-02 00:00:00" AS sls_dt, 3456 AS item_sku, 9.99 AS sls_amt, 2 AS disc_nbr, 1.00 AS disc_amt),
        (SELECT 2345 AS store_nbr, "2014-01-02 00:00:00" AS sls_dt, 4567 AS item_sku, 7.99 AS sls_amt, 1 AS disc_nbr, 0.59 AS disc_amt),
      GROUP BY store_nbr, sls_dt, item_sku, sls_amt
    ) GROUP BY store_nbr, sls_dt
  ), 
  store_nbr, sls_dt, items, // input columns 
  "[ // output schema 
    {'name': 'store_nbr', 'type': 'INTEGER'},
    {'name': 'sls_dt', 'type': 'STRING'},
     {'name': 'items', 'type': 'RECORD',
     'mode': 'REPEATED',
     'fields': [
       {'name': 'sku', 'type': 'STRING'},
       {'name': 'sls_amt', 'type': 'FLOAT'},
       {'name': 'discounts', 'type': 'RECORD',
       'mode': 'REPEATED',
       'fields': [
         {'name': 'disc_nbr', 'type': 'INTEGER'},
         {'name': 'disc_amt', 'type': 'FLOAT'}
         ]    
       }]    
     }]", 
  "function(row, emit) { // function 
    var items = []; 
    for (var i = 0; i < row.items.length; i++) { 
      x = row.items[i].split('|'); 
      var discounts = [];
      y = x[2].split(';');
      for (var j = 0; j < y.length; j++) {
        discount = y[j].split(',');
        discounts.push({disc_nbr:parseInt(discount[0]), disc_amt:parseFloat(discount[1])})
      }
      items.push({sku:x[0], sls_amt:parseFloat(x[1]), discounts: discounts}); 
    }; 
    emit({
      store_nbr: row.store_nbr, 
      sls_dt: row.sls_dt, 
      items: items
      }); 
  }"
)  

Result is as below

with expected schema