How to Create Nested Data & Export as JSON?

Sometimes you want to export your data as JSON files.

One cool thing about JSON file is that you can have the data in a nested structure.

For example, here is a sales data that each row represents each order of a given product on a given date from a given customer.

If you export this data as a JSON file by clicking on the Export button,

you'll get the data presented as a flat JSON format.

But, given that a same customer can have multiple rows representing multiple orders and that the JSON format can support a nested format you might want to have the same data in the following format.

When you look at the data above, the first customer's multiple orders are nested inside the 'Orders' property.

You can generate this type of JSON format data in Exploratory, but first, you need to transform the data in a nested format.

I'll show you how you can do it in Exploratory.

I'm going to use this sales data. Each row represents each order. We want to transform this data so that each row will represent each customer and have a column to have multiple orders for each customer.

1. Group Data By Customer

First, we want to group the data by the customer so that in the next step we can create the nested order data for each customer.

2. Create Nested Data Frame

Second, you want to use the 'nest' function, which is a R function and from the tidyr package., to create the nested order data.

We don't have an UI for this functionality so you need to use the custom R command feature to call the function.

Select 'Custom R Command' from the Step menu.

Then, type a command that is something like below.

nest(`Order Date`, `Order Priority`, `Product ID`, `Product Name`, Sales, .key="Orders")

I'm selecting the columns that I want them to be in the nested part of the data. In this case, since I wanted to have multiple orders per customer and make the orders information as the nested part of the data I'm specifying the order related columns.

The reason you see those 'back-ticks' for most of the column names is because they contain spaces in the names.

The argument called '.key' located at the end inside the 'nest' function is to set the name for the column holding the nested order data.

Once you run it you'll have two columns. One is the grouping column, which is 'Customer Name' in this case. And another is the column that holds the nested order data.

3. Export it as JSON

Now you can export the nested data as JSON file.

When you open the exported JSON file you'll see something like below.

That's it!