creating json structure (loop in side loops ) and call an API to insert records.

Six Stars

creating json structure (loop in side loops ) and call an API to insert records.

Hi There,

 

I am using mysql to read some records in below format and trying to create json format as given below ...

 

 

tried using twrite json field like  -->  tmysqlinput --> tmap (for joins) --> twritejsonfield-->tflowtoiterate-->trest (here it inserts records ).

but when i try to loops multiple times (here I have 7 language codes coming as separate elements (columns) and all of them should go as a array of records with other columns ... how can I achieve this JSON fromat )?

SQL as below  :- 

ROW_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
JOB_ID int,
P_CMO_CODE VARCHAR( 12 ) NOT NULL,
P_CNTY_CD_LOC VARCHAR( 3 )NOT NULL,
P_TY_CD VARCHAR( 1 )NOT NULL,
P_GROUP VARCHAR( 1 )NOT NULL,
P_ID VARCHAR( 12 )NOT NULL,
P_LAST_NAM VARCHAR( 35 )NOT NULL,
P_FST_NAM VARCHAR( 15 ),
P_MI_NAM VARCHAR( 1 ),
P_SFX_NAM VARCHAR( 5 ),
P_TITL_NAM VARCHAR( 5 ),
P_SSN_NUM VARCHAR( 9 ),
P_FEIN_ID VARCHAR( 9 ),
GL_LINE1_AD VARCHAR( 50 )NOT NULL,
GL_LINE2_AD VARCHAR( 50 ),
GL_CITY_NAM VARCHAR( 20 )NOT NULL,
GL_ST_CD VARCHAR( 2 )NOT NULL,
GL_ZIP_CD VARCHAR( 9 )NOT NULL,
GL_PHON_NUM VARCHAR( 10 ),
GL_FAX_NUM VARCHAR( 10 ),
GL_EMAIL_AD VARCHAR( 50 ),
GM_LINE1_AD VARCHAR( 50 ),
GM_LINE2_AD VARCHAR( 50 ),
GM_CITY_NAM VARCHAR( 20 ),
GM_ST_CD VARCHAR( 2 ),
GM_ZIP5_CD VARCHAR( 9 ),
P_CNTY_CD_MAILING VARCHAR( 3 ),
GM_PHON_NUM VARCHAR( 10 ),
GM_FAX_NUM VARCHAR( 10 ),
P_LANG_CD1 VARCHAR( 2 ),
P_LANG_CD2 VARCHAR( 2 ),
P_LANG_CD3 VARCHAR( 2 ),
P_LANG_CD4 VARCHAR( 2 ),
P_LANG_CD5 VARCHAR( 2 ),
P_LANG_CD6 VARCHAR( 2 ),
P_LANG_CD7 VARCHAR( 2 ),
P_GENDER_CD VARCHAR( 1 ),
P_SPECL_CTR INTEGER ,
P_SPECL_CD1 VARCHAR( 3 )NOT NULL,
P_SPECL_BEG_DT1 DATE,
P_SPECL_END_DT1 DATE,
P_SPECL_CD2 VARCHAR( 3 ),
P_SPECL_BEG_DT2 DATE,
P_SPECL_END_DT2 DATE,
P_SPECL_CD3 VARCHAR( 3 ),
P_SPECL_BEG_DT3 DATE,
P_SPECL_END_DT3 DATE,
P_SPECL_CD4 VARCHAR( 3 ),
P_SPECL_BEG_DT4 DATE,
P_SPECL_END_DT4 DATE,
P_SPECL_CD5 VARCHAR( 3 ),
P_SPECL_BEG_DT5 DATE,
P_SPECL_END_DT5 DATE,
P_SPECL_CD6 VARCHAR( 3 ),
P_SPECL_BEG_DT6 DATE,
P_SPECL_END_DT6 DATE,
P_SPECL_CD7 VARCHAR( 3 ),
P_SPECL_BEG_DT7 DATE,
P_SPECL_END_DT7 DATE,
P_SPECL_CD8 VARCHAR( 3 ),
P_SPECL_BEG_DT8 DATE,
P_SPECL_END_DT8 DATE,
P_SPECL_CD9 VARCHAR( 3 ),
P_SPECL_BEG_DT9 DATE,
P_SPECL_END_DT9 DATE,
P_SPECL_CD10 VARCHAR( 3 ),
P_SPECL_BEG_DT10 DATE,
P_SPECL_END_DT10 DATE,
P_AGEMIN VARCHAR( 2 ),
P_AGEMAX VARCHAR( 2 ),
P_HANDICAP VARCHAR( 1 ),
P_HOURS_MO VARCHAR( 1 ),
P_OPENHR_MO VARCHAR( 4 ),
P_CLOSEHR_MO VARCHAR( 4 ),
P_HOURS_TU VARCHAR( 1 ),
P_OPENHR_TU VARCHAR( 4 ),
P_CLOSEHR_TU VARCHAR( 4 ),
P_HOURS_WE VARCHAR( 1 ),
P_OPENHR_WE VARCHAR( 4 ),
P_CLOSEHR_WE VARCHAR( 4 ),
P_HOURS_TH VARCHAR( 1 ),
P_OPENHR_TH VARCHAR( 4 ),
P_CLOSEHR_TH VARCHAR( 4 ),
P_HOURS_FR VARCHAR( 1 ),
P_OPENHR_FR VARCHAR( 4 ),
P_CLOSEHR_FR VARCHAR( 4 ),
P_HOURS_SA VARCHAR( 1 ),
P_OPENHR_SA VARCHAR( 4 ),
P_CLOSEHR_SA VARCHAR( 4 ),
P_HOURS_SU VARCHAR( 1 ),
P_OPENHR_SU VARCHAR( 4 ),
P_CLOSEHR_SU VARCHAR( 4 ),
NPI VARCHAR( 10 ),
CRT_DT DATE,
UPD_DT DATE,
JOB_NAME VARCHAR(50),
ERROR_CODE Varchar(1000),
ERROR_MSG VARCHAR(1000)
)

 

 

Json Structure as below

 

{
    "uiid":"766868787",
    "correlationId":"84376587458",
  "provider": [
      {
          "planCode": "214",
          "planName": "PEACHSTATE",
          "mcoId": null,
          "programTypeCd": null,
          "effectiveStartDate":"2019-04-01",
          "effectiveEndDate": null,
          "npi": null,
          "stateProviderId": "providerId12",
          "pcpFlag": null,
          "genderCd": null,
          "sexLimitsCd": null,
          "ageLowLimit": null,
          "ageHighLimit": null,
          "classificationCd": null,
          "groupFlag": "1",
          "groupName": null,
          "firstName": null,
          "lastName": null,
          "nameSuffix": null,
          "titleName": null,
          "ssn": null,
          "fein": null,
          "emailAddress": null,
          "middleName": null,
          "acceptObInd": null,
          "handicappedAccesibleInd": false,
          "reasonForChange": null,
          "createdOn": null,
          "createdBy": null,
          "updatedOn": "2019-11-18T11:59:12.000+00:00",
          "updatedBy": null,
          "providerLanguages": [
           {
            "languagetypeCd":null,
            "providerSpeaksInd":null,
            "prgrmTypeCd":"Medicaid",
            "effectiveStartDate":"2019-04-01",
            "effectiveEndDate":null,
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":null,
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
          "providerSpeciality": [
           {
            "specialityCd":null,
            "primarySpecialityInd":null,
            "prgmTypeCd":null,
            "effectiveStartDate":"2019-11-18",
            "effectiveEndDate":null,
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":"121",
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
          "providerType": [
           {
            "typeCd":null,
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":"121",
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
          "providerHospitalAffilation": [
           {
            "hospitalName":null,
            "effectiveStartDate":"2019-01-01",
            "effectiveEndDate":null,
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":"121",
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
          "providerPhoneNumber": [
           {
            "phoneNumber":"12345678",
            "effectiveStartDate":"2019-11-15",
            "effectiveEndDate":null,
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":null,
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
          "providerOfficeHours": [
           {
            "dayOfWeek":null,
            "openFrom":null,
            "closeAt":"11:20:30",
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":"121",
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
          "providerNormalizedAddress": [
           {
            "address":"Test street",
            "fullAddress":null,
            "city":null,
            "state":null,
            "zipCode":null,
            "countyCode":null,
            "lattitude":null,
            "longitude":null,
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":null,
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
          "providerPanelLimit": [
           {
            "panelSize":12,
            "enrolledCount":null,
            "panelStatus":null,
            "holdCd":null,
            "typeCd":null,
            "createdOn":"2019-11-18T11:59:12.000+00:00",
            "createdBy":null,
            "updatedOn":"2019-11-18T11:59:12.000+00:00",
            "updatedBy":null
           }
           ],
           "providerAddress":[
               {
                "addressType":null,
                "addressLine1":null,
                "addressLine2":null,
                "city":null,
                "state":null,
                "zipCode":null,
                "countyCode":null,
                "faxNumber":null,
                "effectiveStartDate":"2019-11-18",
                "effectiveEndDate":null,
                "createdOn":"2019-11-18T11:59:12.000+00:00",
                "createdBy":"121",
                "updatedOn":"2019-11-18T11:59:12.000+00:00",
                "updatedBy":null
               }
               ]
      }
  ]
}

Eight Stars

Re: creating json structure (loop in side loops ) and call an API to insert records.

The tWriteJsonField component seems a little lacking on features while working with complex json objects. I have tried using a custom Talend Component "tJSONDoc" (https://github.com/jlolling/talendcomp_tJSONDoc)

The documentation can be a little confusing in the beginning.
HTH

PS: I am still learning this component, and so far, I have had multiple success overall using this component in creating complex objects.
Six Stars

Re: creating json structure (loop in side loops ) and call an API to insert records.

Hi ,

I am not able to find the solution with this , could anyone explain me how to import this object and work on it pls ...

Eight Stars

Re: creating json structure (loop in side loops ) and call an API to insert records.

Can you try installing the custom component? https://community.talend.com/t5/Design-and-Development/How-to-Install-Custom-Components/td-p/120495

 

The github page I shared has the documentation. I would suggest you try installing the component and then checking out the documentation. Thnx.

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog