Parse JSON data in ExtractJSONFields

[himanshup]

Hello,

I am stuck with a JSON output from ExtractJSONFields, I need to parse it to extract:

total

start

num

nextStart

users.username

users.email

users.disabled

from this JSON:

{
"total": 1681,
"start": 401,
"num": 100,
"nextStart": 501,
"users": [
{
"username": "xxxyyyzzzz",
"udn": null,
"id": "717c8d888a254505ad4c5b6acf2829d8",
"fullName": "xxxyyyzzzz",
"availableCredits": 496.83594,
"assignedCredits": 500.0,
"categories": [],
"emailStatus": "notverified",
"firstName": "xxx",
"lastName": "yyyzzzz",
"preferredView": null,
"description": null,
"email": "xxxyyyzzzz9@yahoo.com",
"userType": "both",
"idpUsername": null,
"favGroupId": "5c1d9acdd33f4680ac57aa6ea8cb2d9e",
"lastLogin": 1626193947000,
"mfaEnabled": false,
"mfaEnforcementExempt": false,
"storageUsage": 62649842,
"storageQuota": 2199023255552,
"orgId": "QCH1Le0vwRIyplqB",
"role": "lKNJsGtwYUDeTAm6",
"level": "2",
"userLicenseTypeId": "prqtrstsionalAdvUT",
"disabled": false,
"tags": [],
"culture": "en-US",
"cultureFormat": "us",
"region": null,
"units": "english",
"thumbnail": null,
"access": "org",
"created": 1617650300000,
"modified": 1727309100000,
"provider": "testprovider"
},
{
"username": "pqrstuv",
"udn": null,
"id": "efea0b8b895247bb89e4849ab5d718f6",
"fullName": "pqrstuv pqrstuv",
"availableCredits": 999.94,
"assignedCredits": 1000.0,
"categories": [],
"emailStatus": "notverified",
"firstName": "pqrstuv",
"lastName": "pqrstuv",
"preferredView": null,
"description": null,
"email": "pqrstuv@test.com",
"userType": "both",
"idpUsername": null,
"favGroupId": "0911c1f1ea804da5a0426460457377d3",
"lastLogin": 1715922927000,
"mfaEnabled": false,
"mfaEnforcementExempt": false,
"storageUsage": 9265882,
"storageQuota": 2199023255552,
"orgId": "QCH1Le0vwRIyplqB",
"role": "lKNJsGtwYUDeTAm6",
"level": "2",
"userLicenseTypeId": "prqtrstsionalAdvUT",
"disabled": false,
"tags": [],
"culture": null,
"cultureFormat": null,
"region": null,
"units": "english",
"thumbnail": null,
"access": "org",
"created": 1706067642000,
"modified": 1706067642000,
"provider": "testprovider"
}
]
}

I have Loop Jsonpath query = "$"

Out Talend Studio installation version is: 

Version: 8

Build id: 20241016_1624-patch

Thank you

[Dave_Simo]

Hello,

You can parse the json with this jsonpath query like below

Best Regards

[himanshup]

Thank you Dave_Simo.
I tried your suggestion, but getting this error message:

Missing property in path $['users']

[himanshup]

Hi Dave_Simo,

I tried your suggestion but am getting this message:

Missing property in path $['users']

Thank you

[Dave_Simo]

Hello,

weird did you try with tFileInputJson

I did not encounter any errors with this component as you can see in my first screenshot above.

Best Regards

[himanshup]

Hello,

My output from tRESTClient is feeding into tExtractJSONFields component.

With the mapping as shown below, 

I am getting the following results:

Starting job < > at 08:52 21/11/2024.
[statistics] connecting to socket on port 3525
[statistics] connected
.-----+-----+---+---------+--------------------------------+--------+----------------------------------.
| tLogRow_1 |
|=----+-----+---+---------+--------------------------------+--------+---------------------------------=|
|total|start|num|nextStart|username |disabled|email |
|=----+-----+---+---------+--------------------------------+--------+---------------------------------=|
|null |null |null|null |xxxyyyzzzz |false |xxxyyyzzzz9@yahoo.com |
|null |null |null|null |pqrstuv |false |pqrstuv@test.com |
'-----+-----+---+---------+--------------------------------+--------+----------------------------------'

[statistics] disconnected

Job < > ended at 08:52 21/11/2024. [Exit code = 0]

+++++++++++++++++++++++++++++++

I have masked the actual values from my job for security reasons.

How ever, to note that the total, start, num and nextStart values are all NULL; we knwo that the inout has non null values for those fields.

Thank you

[Dave_Simo]

Hello,

try putting the total, start, num and nextStart fields into string type.

Best Regards

[himanshup · Accepted solution]

Hello Dave_Simo,

I ended up using this configuration (two tExtractJSONField components):

And it returned the data as I was expecting. Thank you very much for your guidance.

Have a great day.