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]

for the hard coded issue , i think it would be easy to go this way only because to fetch value

corresponding to any key the key must be known before.

otherwise you can parse your json at run time and store values as per your requirement in context or global or output flow.

Regards

Chandra Kant 

[Parikhharshal]

@CK395: How do I get multiple dates which are in array of page_view? Along with the dates there is the value of :29,13,19 eg which I need to get all well. Using java is the only way?

[Anonymous]

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

 page_views is not an array as per your post

json array is an array that contains atleast two similar type of json objects for ex. participations 

and it can be accessed as <"$.participations[*]"> giving all participations object

or <"$.participations[*].url"> giving values of "url" in all objects of participations.

The point i am trying to convey is if there is an array of objects then it can be fetched using * because all the keys are same

but if there are all different keys in an object then they all must be specified separately to get their values.

Regards

Chandra Kant

[Anonymous]

Following on from what Chandra said, you need to find the keys dynamically. This is why I suggested parsing the JSON with dynamic keys using Java. It's a bit more fiddly, but allows you more control. Dynamic keys is something that would be a nice addition to the tExtractJsonField component.

[Parikhharshal]

@rhall: Thanks for your reply.

Is it then not easy to get everything read/formatted in java instead of textractjson field and just use tjava to fb output?

[Anonymous]

Well that is an implementation choice for you. I have a rule with Talend which is to not "reinvent the wheel". If a component exists to do the job, then I use that. However, if that component does not exist, I am happy to use some well documented Java in a tJava/tJavaFlex/tJavaRow component. I actually have a few routines that I have created for things the tXMLMap cannot do which make my life easier when working with XML. There is nothing wrong with using your own code, you just need to ensure that you don't end up writing bespoke code for functionality that is well supported. 

[Parikhharshal]

@rhall: I sort of agree with what you are saying but only problem I have is then if I just get the keys for dates, then how is it going to work for combination of static and dynamic keys, if that makes sense?

Like how my job would look like if I’m getting dates read from java and any static keys I get from textractjson fields? Hence I was asking.... how my job would be designed if I were to use both static and dynamic keys.

[Parikhharshal]

@CK395 and @rhall: I tried doing what you suggested but it is not printing anything for page_views.....Showing null.

This is how json data looks:

{"page_views":{"2018-03-22T20:00:00+11:00":19,"2018-03-27T10:00:00+11:00":4,
"2018-03-27T11:00:00+11:00":27,"2018-03-27T12:00:00+11:00":8,"2018-04-09T09:00:00+10:00":4,
"2018-04-16T10:00:00+10:00":9,"2018-04-16T11:00:00+10:00":3,"2018-04-16T13:00:00+10:00":8,
"2018-04-17T09:00:00+10:00":1,"2018-04-17T10:00:00+10:00":5,"2018-04-17T11:00:00+10:00":6,
"2018-04-17T14:00:00+10:00":15,"2018-04-17T15:00:00+10:00":3,"2018-04-18T13:00:00+10:00":4,
"2018-05-01T21:00:00+10:00":3,"2018-05-15T11:00:00+10:00":11,"2018-05-15T13:00:00+10:00":10,
"2018-05-15T14:00:00+10:00":3,"2018-05-15T15:00:00+10:00":6,"2018-05-15T16:00:00+10:00":4,
"2018-05-15T17:00:00+10:00":5,"2018-05-17T10:00:00+10:00":5,"2018-05-17T11:00:00+10:00":3,
"2018-05-17T12:00:00+10:00":5,"2018-05-17T14:00:00+10:00":2,"2018-05-20T17:00:00+10:00":1,
"2018-05-23T09:00:00+10:00":8,"2018-05-23T11:00:00+10:00":1,"2018-05-28T10:00:00+10:00":5,
"2018-05-28T12:00:00+10:00":2,"2018-05-28T13:00:00+10:00":24,"2018-05-28T20:00:00+10:00":9,
"2018-05-28T21:00:00+10:00":13,"2018-05-29T10:00:00+10:00":5,"2018-05-29T11:00:00+10:00":2,
"2018-05-29T12:00:00+10:00":6,"2018-05-29T13:00:00+10:00":7,"2018-05-30T11:00:00+10:00":9,
"2018-05-30T14:00:00+10:00":2,"2018-05-31T10:00:00+10:00":2,"2018-05-31T11:00:00+10:00":15,
"2018-06-19T10:00:00+10:00":4,"2018-06-27T10:00:00+10:00":3,"2018-07-05T19:00:00+10:00":4},
"participations":[{"created_at":"2018-03-22T09:21:34Z",
"url":"https://swinburneonline.instructure.com/courses/361/quizzes/2194/take?user_id=20257"},
{"created_at":"2018-03-26T23:49:59Z","url":"https://swinburneonline.instructure.com/courses/361/quizzes/
2195/take?user_id=20257"},{"created_at":"2018-03-27T00:21:50Z","url":"https://swinburneonline.instructure.
com/courses/361/quizzes/2197/take?user_id=20257"},{"created_at":"2018-03-27T00:29:31Z","url":
"https://swinburneonline.instructure.com/courses/361/quizzes/2196/take?user_id=20257"}]}

Is it something wrong I am doing?

[Anonymous]

hi,

Yes it is wrong .

You first need to understand what is Loop JsonPath query? and how json path query works?

It is simple, please go through it once.  

if even after that you are not comfortable in making queries for page_views, please do let me know.

Just to get you started,

jsonPath is the path of extracting any specific key  

<$> specifies the root in jsonpath

and LoopJsonPath is the path which specifies the multiple occurence for ex. if your file has multiple page_views and every page_views has same keys to be fetched then you just need to specify "<$.page_views"> in LoopJsonPath query and every key to be fetched in json query column corresponding to key.

This might not clear the whole picture, so do understand it once.

Regards

Chandra Kant

[Parikhharshal]

@CK395: I did $page_views and it worked.

Kept the loop jSonpath query as it is but in jSon query it should have been $page_views instead of $pageviews.

It shows dates like this:

Seems I will have to use Java here. No any other option.