topic Re: Shows null values for tExtractJasonFields in Talend Studio

Shows null values for tExtractJasonFields

Parikhharshal — Sat, 16 Nov 2024 07:19:23 GMT

Hi there

I have designed my job like below which shows result as NULL. I am sure my tExtractjsonFields is not configured properly.

Trying to extract Json (as shown below) using URL:

[
  {
    "assignment_id": 1234,
    "title": "Assignment 1",
    "points_possible": 10,
    "due_at": "2012-01-25T22:00:00-07:00",
    "unlock_at": "2012-01-20T22:00:00-07:00",
    "muted": false,
    "min_score": 2,
    "max_score": 10,
    "median": 7,
    "first_quartile": 4,
    "third_quartile": 8,
    "module_ids": [
        1,
        2
    ],
    "submission": {
      "submitted_at": "2012-01-22T22:00:00-07:00",
      "score": 10
    }
  },
  {
    "assignment_id": 1235,
    "title": "Assignment 2",
    "points_possible": 15,
    "due_at": "2012-01-26T22:00:00-07:00",
    "unlock_at": null,
    "muted": true,
    "min_score": 8,
    "max_score": 8,
    "median": 8,
    "first_quartile": 8,
    "third_quartile": 8,
    "module_ids": [
        1
    ],
    "submission": {
      "submitted_at": "2012-01-22T22:00:00-07:00"
    }
  }
]

My tExtractJsonFields is configred as below:

Can someone please guide what am I doing wrong?

Thanks

Harshal.

Re: Shows null values for tExtractJasonFields

Parikhharshal — Mon, 12 Nov 2018 04:22:21 GMT

@rhall and @nthampi:

I tried doing with Xpath.

but it shows me error like this:tExtractJSONFields_1 - Unbound prefix: 2018-11-01T18

Not sure why it cannot still read.

Re: Shows null values for tExtractJasonFields

Anonymous — Mon, 12 Nov 2018 08:17:39 GMT

hi Parikhharshal,

your json path queries does not seem to be correct as per data provided and the requirement

you better use <"$[*]"> for the loop query.

and then specify the jsonpath for fields.

Regards

Chandra Kant

Re: Shows null values for tExtractJasonFields

Anonymous — Mon, 12 Nov 2018 15:06:27 GMT

@Parikhharshal

If you are using Read By JsonPath, please mention the Loop Jsonpath query as below.

If you are using Read By Xpath, please mention the Loop Xpath query as below.

Re: Shows null values for tExtractJasonFields

Parikhharshal — Mon, 12 Nov 2018 21:49:22 GMT

@CK395 and @kharindran:

It works fine. Thanks.

Similarly I have got 2 different scenarios:

Can you please suggest what should be my Loop jSonpath query in tExtractJsonFields?

{
  "page_views": {
    "2012-01-24T13:00:00-00:00": 19,
    "2012-01-24T14:00:00-00:00": 13,
    "2012-01-27T09:00:00-00:00": 23
  },
  "participations": [
    {
      "created_at": "2012-01-21T22:00:00-06:00",
      "url": "https://canvas.example.com/path/to/canvas",
    },
    {
      "created_at": "2012-01-27T22:00:00-06:00",
      "url": "https://canvas.example.com/path/to/canvas",
    }
  ]
}

{
  "2012-01-24":{
    "instructorMessages":1,
    "studentMessages":2
  },
  "2012-01-27":{
    "studentMessages":1
  }
}

Re: Shows null values for tExtractJasonFields

Parikhharshal — Tue, 13 Nov 2018 10:02:46 GMT

@rhall and @manodwhb: Can you pls respond to this?

Re: Shows null values for tExtractJasonFields

Anonymous — Tue, 13 Nov 2018 10:04:16 GMT

@Parikhharshal

1. The loop Jsonpath query for this case would be $.participations

2. I am not sure about this as there seems no fixed variable name. Will check on this and update you if I find a solution.

Re: Shows null values for tExtractJasonFields

Parikhharshal — Tue, 13 Nov 2018 10:06:30 GMT

@kharindan: thanks for your reply. What about page_views block which is in point 1.?

Re: Shows null values for tExtractJasonFields

Anonymous — Tue, 13 Nov 2018 12:29:21 GMT

Hi,

first of all make your first json file valid,

and since there are no such looping elements thus without looping

for 1 jsonpath query would be :

<"$.pageviews">

<"$.participations[*].created_at">

<"$.participations[*].url">

and for 2 jsonpath query would be

<"$.2012-01-24.instructorMessages">

and similarly for other keys

Regards

Chandra Kant

Re: Shows null values for tExtractJasonFields

Parikhharshal — Tue, 13 Nov 2018 16:27:33 GMT

@CK395 : thanks for the reply. What do you mean by make first json valid? What do I need to do?

Also for the second one, dates keep changing, So I think the way you have setup is going to be hardcoded.

Does it mean for both json queries it should be fairly simple as just bringing fields and get them?

Re: Shows null values for tExtractJasonFields

Anonymous — Tue, 13 Nov 2018 16:35:00 GMT

It looks like you have different JSON schemas arriving into your job. You need to first identify which schema you are working with and then configure a tExtractJSONField to cater for each schema. I don't think you will find a one size fits all solution for this.

Re: Shows null values for tExtractJasonFields

Parikhharshal — Tue, 13 Nov 2018 16:46:37 GMT

@rhall: yes that’s right. Lately I have got different json files to deal with. I know that I have to use different config in extract json field component. That’s why reaching out for help on what to be configured. If you can also provide some help on first scenario, that would be great.

Re: Shows null values for tExtractJasonFields

Anonymous — Tue, 13 Nov 2018 17:14:05 GMT

Use a tExtractJSONField component to test for an element you know will be in one of the schemas you need to deal with. If it returns a value, then you know that is the correct schema and you can them retrieve the rest of the values. If the value is null, then you know the schema is not correct and it must be one of the other schema types. Keep doing that until you find the correct schema and process the data from there.

It might mean using several tExtractJSONField components, but it will allow you to process multiple types in one job.

For example....

Input file ---->tExtractJSONField (for schema 1) ----> Rest of the job

---->tExtractJSONField (for schema 2) ----> Rest of the job

---->tExtractJSONField (for schema 3) ----> Rest of the job

Re: Shows null values for tExtractJasonFields

Parikhharshal — Tue, 13 Nov 2018 17:17:21 GMT

: yes I know about this. I am going to end up using extract json field component only.

What my ques though is, being json handling tricky, I want to know for each scenario, what should be configured in extract json component.

Thanks.

Re: Shows null values for tExtractJasonFields

Anonymous — Tue, 13 Nov 2018 17:18:52 GMT

This is what I used to solve problems like this....

http://jsonpath.com/

Re: Shows null values for tExtractJasonFields

Parikhharshal — Tue, 13 Nov 2018 17:21:56 GMT

@rhall: I know this too. But if you see my json carefully, the second scenario has got file like this:

2.

{
"2012-01-24":{
"instructorMessages":1,
"studentMessages":2
},
"2012-01-27":{
"studentMessages":1
}
}

Now the dates here are not having any tag names. So how do I get them?

Re: Shows null values for tExtractJasonFields

Anonymous — Tue, 13 Nov 2018 17:28:07 GMT

I see. This is a difficult one. I don't believe (I may be wrong) that this is possible with Talend at the moment. What you could do is identify if the schema is of this type using a JSONPath like this....

$.*.studentMessages

.... and if it is, using a slightly different approach to get the data. The approach I have used before (I'm afraid I do not have the code at hand) is to build Java code to return all of the key/value pairs and then interrogating the Java object for your data. An example of this approach (albeit not perfect, hence the question it is associated to) can be seen here...

https://community.talend.com/t5/Design-and-Development/Get-dynamic-Key-and-value-from-json-file/td-p/134249

Re: Shows null values for tExtractJasonFields

Parikhharshal — Tue, 13 Nov 2018 23:35:08 GMT

@rhall: Does it mean something like this?

Usual flow of loop/itetrate -> Java code to get keys/pair -> output to DB.

Re: Shows null values for tExtractJasonFields

Anonymous — Wed, 14 Nov 2018 06:11:37 GMT

hi,

making json valid means checking syntactical validity of your json file

for ex. consider json below

{
"parentkey1": {

"childkey1": "childvalue1",

"childkey2": "childvalue2" ,

}
}

in above json the comma on line 4 is invalid ,it should not be there because there are no more childkeys after that .

the "childkey2":"childvalue2" is the last key value pair so there should not be any comma after that.

Before reading any of your json files you should check if they are valid or not.

You can do this online easily.

Regards

Chandra Kant

Re: Shows null values for tExtractJasonFields

Parikhharshal — Wed, 14 Nov 2018 06:18:20 GMT

@CK395: this is how it actually looks. See if you can provide any help as to how to read json and insert to db.

{"page_views":{"2018-03-14T11:00:00+11:00":24,"2018-03-14T13:00:00+11:00":10,"2018-03-14T14:00:00+11:00":47,"2018-03-14T19:00:00+11:00":16,"2018-03-26T19:00:00+11:00":15,"2018-03-26T20:00:00+11:00":15,"2018-03-26T21:00:00+11:00":4,"2018-03-26T22:00:00+11:00":7,"2018-03-26T23:00:00+11:00":8,"2018-03-27T22:00:00+11:00":18,"2018-03-27T23:00:00+11:00":2,"2018-03-28T00:00:00+11:00":15,"2018-03-28T20:00:00+11:00":6,"2018-03-28T21:00:00+11:00":43,"2018-03-28T22:00:00+11:00":13,"2018-03-28T23:00:00+11:00":11,"2018-03-30T15:00:00+11:00":7,"2018-03-30T17:00:00+11:00":4,"2018-03-30T20:00:00+11:00":6,"2018-03-30T21:00:00+11:00":10,"2018-03-30T22:00:00+11:00":17,"2018-03-30T23:00:00+11:00":21,"2018-04-02T16:00:00+10:00":2,"2018-04-03T18:00:00+10:00":6,"2018-04-03T23:00:00+10:00":29,"2018-04-05T20:00:00+10:00":31,"2018-04-05T21:00:00+10:00":17,"2018-04-05T22:00:00+10:00":41,"2018-04-06T10:00:00+10:00":2,"2018-04-06T12:00:00+10:00":1,"2018-04-07T11:00:00+10:00":16,"2018-04-07T17:00:00+10:00":7,"2018-04-10T21:00:00+10:00":26,"2018-04-10T22:00:00+10:00":4,"2018-04-11T20:00:00+10:00":4,"2018-04-12T20:00:00+10:00":13,"2018-04-12T21:00:00+10:00":16,"2018-04-12T22:00:00+10:00":12,"2018-04-13T15:00:00+10:00":7,"2018-04-15T08:00:00+10:00":15,"2018-04-15T15:00:00+10:00":11,"2018-04-17T06:00:00+10:00":16,"2018-04-17T07:00:00+10:00":7,"2018-04-23T22:00:00+10:00":1,"2018-04-29T08:00:00+10:00":4,"2018-04-30T06:00:00+10:00":6,"2018-04-30T08:00:00+10:00":7,"2018-04-30T10:00:00+10:00":22,"2018-04-30T12:00:00+10:00":1,"2018-05-01T07:00:00+10:00":2,"2018-05-01T19:00:00+10:00":11,"2018-05-03T05:00:00+10:00":7,"2018-05-03T06:00:00+10:00":9,"2018-05-06T13:00:00+10:00":2,"2018-05-06T19:00:00+10:00":13,"2018-05-08T13:00:00+10:00":15,"2018-05-09T09:00:00+10:00":7,"2018-05-09T12:00:00+10:00":1,"2018-05-09T13:00:00+10:00":6,"2018-05-09T14:00:00+10:00":5,"2018-05-09T15:00:00+10:00":6,"2018-05-10T06:00:00+10:00":11,"2018-05-10T07:00:00+10:00":13,"2018-05-10T08:00:00+10:00":6,"2018-05-11T07:00:00+10:00":23,"2018-05-12T19:00:00+10:00":27,"2018-05-14T09:00:00+10:00":14,"2018-05-14T10:00:00+10:00":3,"2018-05-15T23:00:00+10:00":8,"2018-05-16T09:00:00+10:00":4,"2018-05-16T11:00:00+10:00":3,"2018-05-16T14:00:00+10:00":6,"2018-05-21T04:00:00+10:00":37,"2018-05-21T05:00:00+10:00":1,"2018-05-22T15:00:00+10:00":6,"2018-05-22T16:00:00+10:00":6,"2018-05-28T10:00:00+10:00":52,"2018-05-29T20:00:00+10:00":14,"2018-05-29T21:00:00+10:00":48,"2018-05-31T14:00:00+10:00":7,"2018-05-31T21:00:00+10:00":21,"2018-05-31T22:00:00+10:00":77,"2018-05-31T23:00:00+10:00":12,"2018-06-01T00:00:00+10:00":23,"2018-06-01T10:00:00+10:00":4,"2018-06-01T13:00:00+10:00":13,"2018-06-01T14:00:00+10:00":22,"2018-06-01T15:00:00+10:00":4,"2018-06-01T17:00:00+10:00":19,"2018-06-02T14:00:00+10:00":11,"2018-06-04T16:00:00+10:00":25,"2018-06-04T17:00:00+10:00":1,"2018-06-11T12:00:00+10:00":2,"2018-06-11T13:00:00+10:00":101,"2018-07-02T19:00:00+10:00":5,"2018-07-03T19:00:00+10:00":8,"2018-07-09T10:00:00+10:00":5},"participations":[{"created_at":"2018-03-14T03:16:57Z","url":" https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74529/entries"},{"created_at":"2018-03-14T03:20:44Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74529/entries/500954/replies"},{"created_at":"2018-03-26T11:10:26Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74529/entries/549938/replies"},{"created_at":"2018-03-26T11:26:56Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74529/entries/537711/replies"},{"created_at":"2018-03-27T13:22:35Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74560/entries/454732/replies"},{"created_at":"2018-03-27T13:40:28Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74560/entries/517063/replies"},{"created_at":"2018-03-28T10:20:25Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74557/entries/454734/replies"},{"created_at":"2018-03-28T11:50:16Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74558/entries"},{"created_at":"2018-03-30T11:00:12Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74552/entries"},{"created_at":"2018-03-30T11:46:33Z","url":"https://swinburneonline.instructure.com/courses/361/quizzes/2197/take?user_id=17022"},{"created_at":"2018-03-30T11:56:20Z","url":"https://swinburneonline.instructure.com/courses/361/quizzes/2196/take?user_id=17022"},{"created_at":"2018-04-10T11:09:49Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74557/entries/594227/replies"},{"created_at":"2018-04-29T22:03:02Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74525/entries"},{"created_at":"2018-05-02T20:45:39Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74551/entries"},{"created_at":"2018-05-09T04:57:56Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74535/entries"},{"created_at":"2018-05-09T20:51:02Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74533/entries"},{"created_at":"2018-05-09T22:18:36Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74549/entries"},{"created_at":"2018-05-20T19:17:28Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74530/entries/454725/replies"},{"created_at":"2018-05-22T05:24:07Z","url":"https://swinburneonline.instructure.com/api/v1/groups/3467/discussion_topics/74551/entries/678374/replies"}]}