topic Re: problem in data extraction from JSON file in Talend Studio

problem in data extraction from JSON file

Anonymous — Mon, 17 Jul 2017 08:06:57 GMT

I have the following JSON file at input and while extracting the data (using tExtractJSONFields, Readby: XPath) from the input file; I am getting only one record for the column status at output with value as "Viewed" but the status "Expired" goes missing and does not come at Target. May I know what did i do wrong and how to resolve so as to get 2 records that makes both the record to come at output. Any assistance is greatly appreciated.

{
        "_id": "abcd1234",
        "type": "assigned",
        "_time": {
            "stt": "2017-05-31T20:15:56.920Z",
            "tmn": "2017-04-04T16:47:51.669Z"
        },
        "assignedBy": "59235523cdb4446f2f95e583",
        "isPrivate": false,
        "status": [
            "viewed",
            "expired"
        ],
        "confirmationStatus": "",
        "completedTime": "0001-01-01T00:00:00",
        "milestones": [
            {
                "title": "Week 1",
                "completeBy": "2017-06-09T00:00:00Z",
                "completedDate": "0001-01-01T00:00:00"
            },
            {
                "title": "Week 2",
                "completeBy": "2017-06-09T00:00:00Z",
                "completedDate": "0001-01-01T00:00:00"
             },
        ],
        "searchableTitle": "Title123",
        "_acl": {
            "creator": "avcbbde123",
            "gr": false,
            "gw": false,
            "groups": {
                "r": [
                    "abcde12345"
                ],
                "w": [
                    "uvwxyz6789"
                ]
            }
        },
        "count": 672
    }

Re: problem in data extraction from JSON file

Anonymous — Mon, 17 Jul 2017 09:04:44 GMT

Hi
Any other elements do you want to parse and extract value? Or you just want to extract value of status element? How do you set the Loop xpath query?

Regards
Shong

Re: problem in data extraction from JSON file

Anonymous — Tue, 18 Jul 2017 11:29:40 GMT

Hello

Do you want to extract 2 lines with the same data, but for one the status should be viewed and the other expired, right?

Please find the image attached, maybe it could helps you.

Thanks

Douglas

Re: problem in data extraction from JSON file

Anonymous — Wed, 19 Jul 2017 11:55:21 GMT

sorry for the delayed response,

in the attached screenshot, if i am having 2 different values at status viz., viewed, expired; I am getting only one value. can you please let me know how can both the value be extracted and the result should have 2 rows; each row containing each value of status all the other remains the same.

Also there is another problem which I encountered recently; different loop xpath query yields different rows from input. For Eg: if loop xpath query is based on title i am getting different no of rows compared to the loop xpath query that is based on /_acl/groups. Can you please let me know how is that possible and how to eliminate such differences while extracting the data from source.

Re: problem in data extraction from JSON file

Anonymous — Wed, 19 Jul 2017 12:03:08 GMT

@douglaszickuhr wrote:

Hello

Do you want to extract 2 lines with the same data, but for one the status should be viewed and the other expired, right?

Please find the image attached, maybe it could helps you.

Thanks

Douglas

if i use status in loop xpath query, i am getting lesser no. of records than that of using another field in xpath query.

Re: problem in data extraction from JSON file

Anonymous — Wed, 19 Jul 2017 12:52:03 GMT

@sreenathtr wrote:

sorry for the delayed response,

in the attached screenshot, if i am having 2 different values at status viz., viewed, expired; I am getting only one value. can you please let me know how can both the value be extracted and the result should have 2 rows; each row containing each value of status all the other remains the same.

Also there is another problem which I encountered recently; different loop xpath query yields different rows from input. For Eg: if loop xpath query is based on title i am getting different no of rows compared to the loop xpath query that is based on /_acl/groups. Can you please let me know how is that possible and how to eliminate such differences while extracting the data from source.

The difference in record count happens when there is no data or no value inside that particular xpath loop query for that element hence that record gets discarded.

I guess, we need to select maximum level of depth as the absolute path, only then only the JSON will get converted in RDB format. If we choose, as said above we encounter 2 issues

different values are not coming (column Status)
If the data inside the path is null then whole record is ignore

How to have more than one row if the status having more than 1 value and if there is no value in status; get that particular record to output with other column present (in which column status alone is null)