tFileInputJson to logRow

One Star

tFileInputJson to logRow

Hi to you all,

I've a problem retrieving data from json url.

The json file that I receive is in this format
{
"result_ok":true,
"total_count":"498",
"page":1,"total_pages":10,
"results_per_page":50,

"data":[

{"id":"1",
"contact_id":"",
"status":"Complete",
"is_test_data":"0",
"datesubmitted":"2012-01-09 08:54:52",
"[question(11)]":"1",
"[question(12)]":"2",
"[question(54), option(10141)]":"3"
}
...

I've used "tFileInputJSON" component to retrieve data from url and I've mapped it with
-----------------------------------------------------
column | JsonPath query
-----------------------------------------------------
contactId | "$.data
  • .contact_id"
    dateSubmitted | "$.data
  • .datesubmitted"
    question54 | "$.data
  • .[question(54), option(10141)]"

  • but when I try to execute job I receive the error "Can't find any data with JSONPath $.data
  • .[question(54), option(10141)]"

    I think that I'm doing something wrong with JSON code...

    can you help me?
  • One Star

    Re: tFileInputJson to logRow

    Hi

    Welcome to Talend Community!

    I'm sure the reason is about '[' and ']' which are predefined in JSONPath.
    I try to find some resolution at goessner.net-JsonPath, but unfortunately I find nothing help.

    Regards,
    Pedro
    One Star

    Re: tFileInputJson to logRow

    Thanks Pedro, I think the same thing with the '[' character... If you want to try it with talend the url to retrieve a json example is https://restapi.surveygizmo.com/v1/Survey/475913/SurveyResponse.json?userSmiley Tongueass=sales1@casusbellisoft.c... (using a tfileInputJSON component followed by a tLogRow to read data) and you'll have to retrieve the "[question(x)]" or "[variable(x)]" fields (putting "$.data
  • .[question(x)]" in jsonPath query field in the section "mapping" of "basic settings" of tFileInputJSON component)

    Do you think that is a talend bug?
  • Calling Talend Open Studio Users

    The first 100 community members completing the Open Studio survey win a $10 gift voucher.

    Start the survey

    2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

    Talend named a Leader.

    Get your copy

    OPEN STUDIO FOR DATA INTEGRATION

    Kickstart your first data integration and ETL projects.

    Download now

    What’s New for Talend Summer ’19

    Watch the recorded webinar!

    Watch Now

    Why Companies Move to the Cloud: 7 Success Stories

    Learn how and why companies are moving to the Cloud

    Read Now

    Agile Data lakes & Analytics

    Accelerate your data lake projects with an agile approach

    Watch

    Definitive Guide to Data Quality

    Create systems and workflow to manage clean data ingestion and data transformation.

    Download