topic Parse multi record JSON file in Talend Studio

Parse multi record JSON file

ankushdeshpande — Wed, 13 Nov 2019 02:49:30 GMT

Hi All,

I have a input file with multiple JSON records separated by newline. I need to know how we can parse there records. tFileinputJSON only reads first record and ignores all other records as its intended to be used for one json record file but here this file has multiple json records separated by newline.

I am able to extract firstname and lastname but not array elements. i have used tFileInputFullRow to read all json objects as each record with newline and then linked to tExtractJSONfield.

{ "firstname" : "abc" , "lastname" : "def" , "city" : [ "aaa" , "bbb" ]}

{ "firstname" : "ghi" , "lastname" : "jkl" , "city" : [ "ccc" , "ddd" ]}

Expected Output

firstname | lastname | city

abc | def | aaa

abc| def | bbb

ghi| jkl| ccc

ghi|kjl ddd

Thanks

Re: Parse multi record JSON file

uzix — Wed, 13 Nov 2019 16:38:50 GMT

hello,

using 1º tExtractJSONfield with

JSON field -> line

Loop Jsonpath query "$"

mapping:

column json query

city "city[*]"

firstname "firtsname"

lastname "lastname"

connect directly to other tExtractJSONfield

with JSON field ->city

Loop Jsonpath query "$[*]"

column Json query

city "$"

then connect to tjavarow

//code start

output_row.city = input_row.city;
output_row.firstname = row17.firstname;
output_row.lastname = row17.lastname;

//code end

where row17 is the row number(Main) after 1º tExtractJSONfield

output:

.------------------------------------------------------------------------.
| tLogRow_9 |
|=----------------------------------------------------------------------=|
|line |
|=----------------------------------------------------------------------=|
|{ "firstname" : "abc" , "lastname" : "def" , "city" : [ "aaa" , "bbb" ]}|
|{ "firstname" : "ghi" , "lastname" : "jkl" , "city" : [ "ccc" , "ddd" ]}|
'------------------------------------------------------------------------'

.---------+--------+----.
| tLogRow_12 |
|=--------+--------+---=|
|firstname|lastname|city|
|=--------+--------+---=|
|abc |def |aaa |
|abc |def |bbb |
|ghi |jkl |ccc |
|ghi |jkl |ddd |
'---------+--------+----'

see attached image - im using tlogrow after tjavaRow

json_parse.png

Re: Parse multi record JSON file

ankushdeshpande — Mon, 18 Nov 2019 22:04:30 GMT

Thank You SO much
Worked Well