How to take data from nested JSON data in bigquery

Question

I have table with column field as follow :

{
"Quantity":{
     "id_1":1,
     "id_2":4,
},
"Discount" : {
     "id_1":2,
     "id_2":1,

},
"sales":{
     "id_1":{
        "price":50,
        "quantity":1
},
     "id_2":{
        "quantity":1,
        "price":620
}}
,
"tax":{
     "id_1" : 2,
     "id_2" : 3
}
}

My Expected Result is a table as follow :

id	tax	sales_quantity	sales_price	Discount	Quantity
id_1	2	1	50	2	1
id_2	3	1	620	1	4

While I'm trying to create the expected result, I think I found UDF function that work in javascript as follow :

CREATE TEMP FUNCTION  json2array(json STRING)

RETURNS ARRAY <STRUCT<id STRING, tax STRING, sales_quantity STRING, sales_price STRING, Discount STRING, Quantity STRING>>

LANGUAGE js AS """
   var result = {};
   var keys = Object.keys(json);
   keys.forEach(k => {
      keys2 = Object.keys(json[k])
      keys2.forEach(k2 => {
       if(result[k2]== null)
           result[k2] ={}
       if(typeof json[k][k2] === 'object' )
       {
           Object.keys(json[k][k2]).forEach(k3 => {
               result[k2][k +"_"+k3] = json[k][k2][k3]
           })
       } else {
        result[k2][k] =json[k][k2]
       }
      })
   })
  
   var final_result = []
   for (const [key, value] of Object.entries(result)) {
       value["id"] = key; final_result.push(value);
   }
   final_result.map(obj => Object.keys(obj).map(k => obj[k] = obj[k] === null ? "" : obj[k]))
  
   return final_result
""";

the function is working in javascript compiler, but it seems the result is not expected when using BigQuery UDF.I think the main problem is in return, but I'm not sure what I'm missing here

Jaytiger · Answer 1 · 2023-02-04T09:05:20.257

Consider below javascript UDF and PIVOT query.

CREATE TEMP FUNCTION json2array(json STRING) 
RETURNS ARRAY<STRUCT<id STRING, key STRING, value STRING>> LANGUAGE js AS """
  result = [];
  for (const [key, obj] of Object.entries(JSON.parse(json))) { // top-most
    Object.entries(obj).forEach(o => {
      const [k0, v0] = o;
      if (typeof(v0) === 'object')
        for (const [k, v] of Object.entries(v0))
          result.push({id:k0, key:key + '_' + k, value:v});
      else
        result.push({id:k0, key:key, value:v0});
    });
  }
  return result;
""";

WITH sample_table AS (
  SELECT '''{
    "Quantity":{ "id_1":1, "id_2":4 },
    "Discount" : { "id_1":2, "id_2":1 },
    "sales":{
      "id_1":{ "price":50, "quantity":1 },
      "id_2":{ "quantity":1, "price":620 }
    },
    "tax":{ "id_1" : 2, "id_2" : 3 }
  }''' json
)
SELECT * FROM (
  SELECT e.* FROM sample_table, UNNEST(json2array(json)) e
) PIVOT (ANY_VALUE(value) FOR key IN ('tax', 'sales_quantity', 'sales_price', 'Discount', 'Quantity'));

Query results

How to take data from nested JSON data in bigquery

1 Answers1