topic Re: How to convert nested JSON output into a single table in Connectivity & Data Prep

How to convert nested JSON output into a single table

lightsection — Tue, 21 Dec 2021 20:25:42 GMT

How do we convert nested JSON data into a clean straight table format.

Using REST Connector, the nested data look like this:

Data Model: Multiple tables get created for each hierarchy instead of 1 single fact table.

How do we convert these into a single table in a dynamic fashion.

Re: How to convert nested JSON output into a single table

dplr-rn — Tue, 15 Jan 2019 19:24:20 GMT

What do you mean by "convert in a dynamic fashion"?
Principle to convert this into a start schema is same as converting a transnational DB tables to a star schema

Re: How to convert nested JSON output into a single table

lightsection — Tue, 15 Jan 2019 19:55:51 GMT

This is just 1 JSON file, containing data for 1 element of the whole data model.
I have similar JSON files that will add more tables to the whole data model.

The idea is be able to view each file in a single table format , instead of multiple sub tables

Re: How to convert nested JSON output into a single table

dplr-rn — Tue, 15 Jan 2019 20:02:17 GMT

Not an expert by any mean on JSON
but IMHO JSON nesting basically means its different json objects or "tables"/"datastructures" (for the lack of a better term) underneath the hood. Which is why you are getting multiple tables.

like i mentioned before to covert to a flat structure you need to de-normalize.

Hope i understood correctly

Re: How to convert nested JSON output into a single table

fernandobibian — Wed, 23 Jan 2019 12:06:06 GMT

Hi Lightsection,

I understand your struggle. We also faced this problem working with JIRA API and we added this feature as an out-the-box feature of our connector.

I hope it helps you!

Here is a video to see it in action: