Extracting Keys from JSON Input

[SNasif]

Hello Community,

Pretty new with using Talend (couple weeks in) and I'm sure my request has been addressed before or has a pretty straight forward solution but I can't seem to wrap my head around it.

I have inputs coming in from an Oracle DB table that are pulled in JSON format. The inputs do not have any object hierarchy within them, i.e.: it's only a root path with key-value pairs stated ({"fname":"John","lname":"Doe"....}). Each row serves as an event with it's own JSON formatted input. I am looking into extracting the keys only while ignoring the values such that my desired output would look as follows:

ID Field

1 fname

2 lname

Ultimately, I am interested in computing the same logic for each row that ends up on my Oracle table in order to create some sort of field reference list that includes any and all fields that could have possibly passed in my db table.

I have tried using the tilde (~) to extract fields strictly as I read on some forum that it refers to the JSONPath query for keys, but that didn't seem to do anything. Using the tNormalize component requires me to normalize a single column at once too. Any advice or help would be greatly appreciated.

[Anonymous]

Hello @Sam Nsaif​ ,

You can use tFileInputJSON component to read the fields from the JSON input file like the below settingsFor Json path finder, please refer to https://jsonpathfinder.com/

Best regards

Aiming

[anselmopeixoto · Accepted solution]

I'm afraid Talend native Json components like tExtractJsonFields don`t support the usage of ~ operator to extract json element names dynamically.

I would try to use some regex expression on a tJavaRow component instead. Or maybe String split() method... though it might not be an elegant solution.

[SNasif]

Hey @Aiming Chen​ ,

Thank you for your response, I'm aware of the tFileInputJSON component, but my inputs come straight from an Oracle DB Table and as such the component doesn't fit my need. Additionally, I am not facing any issues with parsing the input and extracting the field-value pairs, instead I am looking into creating a list with the Field names only and not the content associated with it.

Best,

Sam

[SNasif]

Hello @Anselmo Peixoto​ ,

Thank you for the response. I was afraid that would be the situation, I was hoping maybe I'm missing something here but just as I anticipated. In any case, just as you mentioned using a string split method isn't exactly the most elegant, and neither is using a tJava component. Guess I'm just surprised a platform aimed for data integration leaves us with limited capacity to parse JSON dynamically.

Thank you for your time and support though.

Best,

Sam