Skip to main content

JSON to table tutorial

Tutorial to transform JSON to fields in a different table

Requirements:

Tutorial to transform JSON to fields in a different table

tutorialJSON to tabletransform methodJSON transformJSON to different table

Create a different table transform

  1. Replace the JSON in the API Request editor with the following JSON:

    {
  "api": "hub",
  "action": "createTransform",
  "authToken": "AuthorizedToken",
  "params": {
    "transformName": "TestTransform2",
    "transformActions": [
      {
        "inputFields": [
          "In1"
        ],
        "transformActionName": "jsonToDifferentTableFields",
        "transformParams": {
          "targetDatabaseName": "faircom",
          "targetTableName": "test_out2",
          "mapOfPropertiesToFields": [
            {
              "propertyPath": "out1",
              "name": "out1",
              "type": "VARCHAR",
              "length": 200
            },
            {
              "propertyPath": "out2",
              "name": "out2",
              "type": "DOUBLE"
            }
          ]
        }
      }
    ]
  }
}

  2. Click Apply defaults to JSON request (Apply.PNG) to replace the "authToken" with a valid token from your session.

  3. Click Send request (Runbutton.png).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

Create the integration table

  1. Replace the "params" property values with the following "params" values:

    {
    "api": "hub",
    "action": "createIntegrationTable",
    "authToken": "AuthorizedToken",
    "params": {
        "databaseName": "faircom",
        "tableName": "test2",
        "fields": [
            {
                "name": "In1",
                "type": "JSON",
                "length": 100
            }
        ],
        "transformName": "TestTransform2"
    },
    "requestId": "00000006"
}

  2. Click Apply defaults to JSON request (Apply.PNG) to replace the "authToken" with a valid token from your session.

  3. Click Send request (Runbutton.png).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

Add records to the integration table

  1. Replace the JSON in the API Request editor with the following JSON:

    {
    "api": "db",
    "action": "insertRecords",
     "authToken": "AuthorizedToken",
    "params": {
        "tableName": "test2",
        "dataFormat": "arrays",
        "databaseName": "faircom",
        "fieldNames": [
            "In1"
        ],
        "sourceData": [
            [
                {
                    "out1": "Text data 1.",
                    "out2": 1
                }],
                [{
                    "out1": "Text data 2.",
                    "out2": 2
                }
            ]
        ]
    }
}

  2. Click Apply defaults to JSON request (Apply.PNG) to replace the "authToken" with a valid token from your session.

  3. Click Send request (Runbutton.png).

  4. Observe the response and ensure the action completed successfully.

    Note

    "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

View the results in the table records

  1. In the API Explorer navigation window, navigate to faircom>admin>Tables and select test_out2 .

    Note

    If you do not see test_out2, refresh Tables.

  2. Click the Table Records tab.

  3. Observe the out1 and out2 data fields that were extracted from the JSON object created in Add records to the integration table