Shows null values for tExtractJasonFields

[Parikhharshal]

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.

[Anonymous]

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.

[Parikhharshal]

@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.

[Anonymous]

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

[Parikhharshal]

: 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.

[Anonymous]

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

http://jsonpath.com/

[Parikhharshal]

@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?

[Anonymous]

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...

[Parikhharshal]

@rhall: Does it mean something like this?

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

[Anonymous]

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

[Parikhharshal]

@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"},{"creat..."}]}