Parse multi record JSON file

[ankushdeshpande]

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

[uzix · Accepted solution]

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

[ankushdeshpande]

Thank You SO much
Worked Well