CODEWITHSUNDEEP
etlpentahotransformationdata-processingpdi
KayV
KayV

Reputation: 13855

Pentaho data integration JSON input in transformation job not showing all json objects

I am working with Pentaho Data integration tool for some of the ETL processing jobs.

My data input is a json file named data.json having many json objects as follows:

{"id":"333","name":"iit"}
{"id":"444","name":"nit"}
{"id":"555","name":"iiit"}
{"id":"666","name":"jnit"}

and when i am using json input in the PDI transformation design, the json preview shows only first row. Please check this screenshotenter image description here

But the same works fine when i modify my data.json file as follows:

{
    "data" : [
                {"id":"333","name":"iit"},
                {"id":"444","name":"nit"},
                {"id":"555","name":"iiit"},
                {"id":"666","name":"jnit"}
            ]
}

Please check the screenshot for that here: enter image description here

Please help out how can i fetch all the json objects present in the data.json file using the following format:

{"id":"333","name":"iit"}
{"id":"444","name":"nit"}
{"id":"555","name":"iiit"}
{"id":"666","name":"jnit"}

Upvotes: 2

Views: 10701

Answers (3)

Reitenator
Reitenator

Reputation: 1155

If your JSON-file looks like that, there could be done a workaround:

Create a text-file-input which reads every line and send the lines to the JSON-input.

Every line will then be considered as its own JSON-input.

input steps

You can then use the JSON-node as you intend to (if I understand you correctly)

JSON input-step

Your output will then look something like this:

Results

Use a 'select values' node to remove the json-column.

Upvotes: 2

eicherjc
eicherjc

Reputation: 301

Your path of $.id and $.name are fine. The problem is with the JSON.

{"id":"333","name":"iit"}
{"id":"444","name":"nit"}
{"id":"555","name":"iiit"}
{"id":"666","name":"jnit"}

This is not valid JSON, and that's why only the first row gets read in. It reads that first line and then it encounters a second open curly bracket without hitting a comma and it just stops because it doesn't know what to do with invalid JSON.

Format your JSON like this and it should work with no modifications to your JSONPath.

{"id":"333","name":"iit"},
{"id":"444","name":"nit"},
{"id":"555","name":"iiit"},
{"id":"666","name":"jnit"}

Upvotes: 4

Cristian Curti
Cristian Curti

Reputation: 1054

XPath:

$.data[*].id

Is different than:

$.data

Try using $..id, and $..name, Should work on both cases and bring you all objects.

Upvotes: 0

Related Questions