How to flatten nested Json dynamically on snowflake or javascript?

Question

This is an example of the JSON file I'm working with, it has around 600 rows of the same nested JSON structure.

{
  "at": {
    "Availability": "Yes",
    "Message": "NV",
    "Delivery option": "fast",
  },
  "childNodeIds": [],
  "crawlMessageFlags": null,
  "pd": {
    "product_id": "product_id value",
    "product_name": "product_name value",
    "retailerName": "retailerName value",
    "stock_status": "InStock",
    "subClassName": "n/a"
  },
  "sellers": [],
  "zipDetails": {}
}

I want to generate a report that has the keys inside PD as the column names. Example

--------------------------------------------------------
    product_id  |   product_name     | retailerName
-----------------------------------------------------------
productid value1| product_name value1| retailerName value1
productid value2| product_name value2| retailerName value2
productid value3| product_name value3| retailerName value3
productid value4| product_name value4| retailerName value4

I can generate this table with hardcoded values but I want to do it dynamically as we have several different use cases.

I have tried several solutions from the web but none solves this without hardcoding.

Any solutions?

Extract the column names from the first object with `Object.keys`, and then iterate over the array grabbing each `pd` `Object.values`. — Andy, May 04 '22 at 10:09
The closest solution was this https://community.snowflake.com/s/article/Dynamically-extract-multi-level-JSON-object-using-lateral-flatten but i cannot make it fit my case. Through this I get PD.keys as individual rows. — Worker, May 04 '22 at 10:13

score 1 · Answer 1 · answered May 04 '22 at 10:14

Here's one approach that might work for you. Depending on the structure of the resulting variable (output) you might want to refactor accordingly.

const list = [
  {
  "at": {
    "Availability": "Yes",
    "Message": "NV",
    "Delivery option": "fast",
  },
  "childNodeIds": [],
  "crawlMessageFlags": null,
  "pd": {
    "product_id": "product_id value1",
    "product_name": "product_name value1",
    "retailerName": "retailerName value1",
    "stock_status": "InStock 1",
    "subClassName": "n/a"
  },
  "sellers": [],
  "zipDetails": {}
},
  {
  "at": {
    "Availability": "Yes",
    "Message": "NV",
    "Delivery option": "fast",
  },
  "childNodeIds": [],
  "crawlMessageFlags": null,
  "pd": {
    "product_id": "product_id value2",
    "product_name": "product_name value2",
    "retailerName": "retailerName value2",
    "stock_status": "InStock 2",
    "subClassName": "n/a"
  },
  "sellers": [],
  "zipDetails": {}
}
]

const output = list.reduce((acc,{ pd })=>{
  acc.push([ pd.product_id, pd.product_name, pd.retailerName ])
  return acc;
}, [["product_id", "product_name", "retailerName"]]);


console.log( output );

How to flatten nested Json dynamically on snowflake or javascript?

1 Answers1