openstatic.org

openstatic.org

JSON-Roller

JSON Roller is a tool for flattening complex JSON data structures into tables. I know there are a lot of tools that perform this exact function, but json-roller is fast and native!

Using a test file movies.json (3.22 Megs, 3 layers of data)

$ time json-roller -i movies.json -c movies.csv

real    0m1.318s
user    0m1.000s
sys     0m0.313s

$ wc movies.csv
  28796  173765 3130845 movies.csv

That's 28,796 records in 1.318 seconds!

How do i use it?

usage: json-roller
 -?,--help          Shows help
 -c,--csv <arg>     Output CSV file
 -i,--input <arg>   Input file .json only
 -m,--md <arg>      Output Markdown file
 -t,--tsv <arg>     Output TSV file
 -u,--url <arg>     URL to read json from
 -v,--verbose       Be Verbose

Lets take a look a simple example

Input: people.json

[
    {
        "name": "Bob",
        "id": 44432,
        "age": 34
    },
    {
        "name": "Mark",
        "id": 14535,
        "age": 31,
        "extra": "stuff"
    },
    {
        "name": "Erin",
        "id": 43232,
        "age": 27
    }
]

So in order to turn this into a nice table we would run:

json-roller -i people.json -c people.csv

Output: people.csv

extra,name,id,age
,Bob,44432,34
stuff,Mark,14535,31
,Erin,43232,27

What about more complex JSON?

This tool really shines when dealing with more complex data structures, lets try another example that isnt as straight forward.

{
    "IT Dept":
    {
        "4432": {
            "name": "Bob",
            "age": 34
        },
        "14535": {
            "name": "Mark",
            "age": 31,
            "extra": "stuff"
        },
        "43232": {
            "name": "Erin",
            "age": 27
        }
    },
    "Sales":
    {
        "387532": {
            "name": "Karen",
            "age": 22
        },
        "142348": {
            "name": "Lenny",
            "age": 25,
            "extra": "stuff"
        },
        "531241": {
            "name": "Joe",
            "age": 30
        }
    }
}

Lets run the tool again with verbose logging enabled. This time we are going to
output a Markdown file for ease of viewing.

json-roller -v -i people.json -m people.md
Root JSONObject detected
Columns Created: layer1key, extra, name, layer0key, age

Result:
| layer1key | extra | name | layer0key | age |
|-----------|-------|-------|-----------|-----|
| 387532 | | Karen | Sales | 22 |
| 142348 | stuff | Lenny | Sales | 25 |
| 531241 | | Joe | Sales | 30 |
| 43232 | | Erin | IT Dept | 27 |
| 4432 | | Bob | IT Dept | 34 |
| 14535 | stuff | Mark | IT Dept | 31 |

As you can see the data structure was still flattened, with new columns created
for dept and user_id, (layer0key and layer1key). If the names of the keys are known
in advance you can provide the -k option to add key names based on layer.

json-roller -v -i people.json -m people.md -k dept,user_id
Root JSONObject detected
Columns Created: user_id, extra, name, dept, age

Result:
| user_id | extra | name | dept | age |
|-----------|-------|-------|-----------|-----|
| 387532 | | Karen | Sales | 22 |
| 142348 | stuff | Lenny | Sales | 25 |
| 531241 | | Joe | Sales | 30 |
| 43232 | | Erin | IT Dept | 27 |
| 4432 | | Bob | IT Dept | 34 |
| 14535 | stuff | Mark | IT Dept | 31 |

Github Project



Downloads

Latest Update: August 20 2021 02:52:11 PM EDT

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.