Overview

The JSON plugin creates a table in a database from any JSON file.

Example use case

Let's take a simple JSON file with nested data and convert the data into tables in a relational database.

Starting JSON file

[
  {
    "id": 1,
    "name": "Justin",
    "pets": [
      {
        "id": 1,
        "name": "Bear",
        "pet": {
          "type": "dog",
          "breed": "Goldendoodle"
        }
      },
      {
        "id": 2,
        "name": "Birdie",
        "pet": {
          "type": "dog",
          "breed": "Goldendoodle"
        }
      }
    ]
  },
  {
    "id": 2,
    "name": "Matt",
    "pets": []
  }
]

Expected Relational Database results

Because the JSON file has nested data in the pets array ([ ]), we will need to create two tables in the database.

  • Table 1 will include top level key/value pairs: id, name. The resulting table should have 2 rows.
  • Table 2 will include data in the pets array (id, name, and pet object's data). Table 2 will also include data from the parent object (id) so we can join the two database tables. The resulting table should have 2 rows.

Table 1 result:

Table 2 result:

Create a JSON job

The JSON file(s) must exist on Mitto. Use the file manager to manually add a JSON file to Mitto.

The JSON plugin is a custom job type, so create a job using the Generic plugin.

Example job configurations

Example 1 Configuration:

{
    "input": {
        "use": "flatfile.iov2#JsonInput",
        "source": "/var/mitto/data/sample.json"
    },
    "output": {
        "tablename": "invoice",
        "use": "call:mitto.iov2.db#todb",
        "schema": "json",
        "dbo": "postgresql://localhost/analytics"
    },
    "sdl": {
        "name": "flatfile",
        "columns": []
    },
    "steps": [
        {
            "use": "mitto.iov2.steps#Input",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#ExtraColumnsTransform"
                },
                {
                    "use": "mitto.iov2.transform#ColumnsTransform"
                }
            ]
        },
        {
            "use": "mitto.iov2.steps#CreateTable"
        },
        {
            "use": "mitto.iov2.steps#Output",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ]
        },
        {
            "use": "mitto.iov2.steps#CollectMeta"
        }
    ]
}

Example 2 Configuration:

{
    "input": {
        "use": "flatfile.iov2#JsonInput",
        "source": "/var/mitto/data/sample.json"
    },
    "output": {
        "tablename": "invoice_lines",
        "use": "call:mitto.iov2.db#todb",
        "schema": "json",
        "dbo": "postgresql://localhost/analytics"
    },
    "sdl": {
        "name": "flatfile",
        "columns": []
    },
    "steps": [
        {
            "use": "mitto.iov2.steps#Input",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#PluckV2Transform",
                    "jpath": "$.invoice_lines[*]",
                    "members": [
                        {
                            "name": "invoice_id",
                            "value": "$.id"
                        }
                    ]
                },
                {
                    "use": "mitto.iov2.transform#ExtraColumnsTransform"
                },
                {
                    "use": "mitto.iov2.transform#ColumnsTransform"
                }
            ]
        },
        {
            "use": "mitto.iov2.steps#CreateTable"
        },
        {
            "use": "mitto.iov2.steps#Output",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ]
        },
        {
            "use": "mitto.iov2.steps#CollectMeta"
        }
    ]
}

JSONPath Expressions

To understand how to use JSONPath to pick pieces of data out of a particular JSON object you can visit:

https://goessner.net/articles/JsonPath/index.html#e2

To run different JSONPath syntax you can visit:

http://jsonpath.com

Example of JSON File converted into Relational Database

JSON file:

[
  {
    "amount": 500.00,
    "customer": {
      "id": 1,
      "name": "Best Customer"
    },
    "id": 1,
    "invoice_lines": [
      {
        "amount": 200.00,
        "id": 1,
        "product": "A"
      },
      {
        "amount": 300.00,
        "id": 2,
        "product": "B"
      }
    ],
    "invoice_number": 1001
  },
  {
    "amount": 240.00,
    "customer": {
      "id": 1,
      "name": "Worst Customer"
    },
    "id": 2,
    "invoice_lines": [
      {
        "amount": 200.00,
        "id": 1,
        "product": "A"
      },
      {
        "amount": 40.00,
        "id": 2,
        "product": "C"
      }
    ],
    "invoice_number": 1002
  }
]

Relational database desired end results from above configurations:

Example 1 End Result:

json.invoice

id invoice_number amount customer_id customer_name
1 1001 500.00 1 Best Customer
1 1002 240.00 2 Worst Customer

Example 2 End Result:

json.invoice_lines

invoice_id id line product amount
1001 1 1 A 200.00
1001 2 2 B 300.00
1001 3 1 A 200.00
1001 4 2 C 40.00