Read JSON

Read JSON objects.

Workshop - Read JSON

Parse a JSON file into rows. Use JSON Input.

What you’ll do

• Read a JSON file from disk.

• Set a loop path for an array.

• Extract fields with JSONPath.

• Use Get Fields to infer metadata.

• Preview rows and validate types.

Prerequisites: Basic transformations. Basic JSON (objects, arrays). PDI installed.

Estimated time: 15 minutes

JSON Input

---

Workshop files

Download the following files.

Keep the filenames unchanged.

Save them in your workshop folder.

17KB

tr_json.ktr

Open

---

JSON input

Create a new transformation

Use any of these options to open a new transformation tab:

• Select File > New > Transformation

• Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)

---

1. Open jsonfile.js in an editor. You will extract fields from this array:

{ "document": {
    "order": [ 
      { "productline": "Classic Cars",
        "customer": "Christine Loomis",
        "status": "Delivered",
        "date": "January 2004",
        "value": 21.99
      },
      { "productline": "Classic Cars",
        "customer": "Mary L. Peachin",
        "status": "Delivered",
        "date": "November 2008",
        "value": 24.99
      },
      { "productline": "Trains",
        "customer": "Bob Italia",
        "status": "Delivered",
        "date": "July 1994",
        "value": 14.99
      }
    ]
  }
}

status can be Delivered or Returned.

---

1. JSON Input

JSON Input

JSON Input reads JSON and outputs rows.

You set one loop path. It outputs one row per loop element.

1. Start Pentaho Data Integration.

Windows (PowerShell)

Set-Location C:\Pentaho\design-tools\data-integration
.\spoon.bat

macOS / Linux

1. Drag JSON Input onto the canvas.

2. Open the step.

3. On the File tab, select your jsonfile.js.

Save your transformation near jsonfile.js. Then use a portable path.

Example:

${Internal.Transformation.Filename.Directory}/jsonfile.js

1. Set Loop path to:

$.document.order[*]

1. Open the Fields tab.

2. Select Get Fields.

3. Verify these field paths and types:

• productline (String)

• customer (String)

• status (String)

• date (String)

• value (Number)

JSON input - file

JSON input - fields

Need help writing JSONPath? Use a tester like: https://jsonpath.com/

2. Dummy

3. Run

---

Troubleshooting

No rows returned

Check the Loop path first. For this file, it must point to the array:

$.document.order[*]

If the JSON structure changes, update the loop path.

Fields are null

Confirm field paths match the JSON keys. If you loop over order[*], use productline, not $.document.order.productline.

Wrong data types

Use Get Fields as a starting point. Then set types explicitly.

Example: keep status as String.