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
– Andy, Commented May 4, 2022 at 10:09
The closest solution was this community.snowflake.com/s/article/… but i cannot make it fit my case. Through this I get PD.keys as individual rows. — Worker
– Worker, Commented May 4, 2022 at 10:13

Kostas Minaidis · Accepted Answer · 2022-05-04 10:14:17Z

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 );

Collectives™ on Stack Overflow

How to flatten nested Json dynamically on snowflake or javascript?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related