Skip to main content

Fields to JSON tutorial

Tutorial to transform fields to JSON in the same table

The tutorials in this section show how to take new records that are added to an integration table, transform them into JSON, and store that JSON in a new field in the same table and record.

Requirements:

  • Ensure the FairCom server is installed and running.

  • Confirm server access by running the FairCom API Explorer. The typical URL is https://localhost:8443/ .

Tutorial for the table to JSON transform method

tutorialtable to JSONtransform methodJSON transformfields to JSON

Create a transform

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

    {
    "api": "transform",
    "action": "createTransform",
    "params": {
        "transformName": "TestTransform1",
        "transformActions": [
            {
                "transformStepName": "First inputs to output",
                "inputFields": [
                    "In1",
                    "In2"
                ],
                "outputFields": [
                    "Out1"
                ],
                "transformStepMethod": "tableFieldsToJson",
                "transformParams": {
                    "mapOfFieldsToProperties": [
                        {
                            "propertyPath": "In1",
                            "fieldName": "In1"
                        },
                        {
                            "propertyPath": "In2",
                            "fieldName": "In2"
                        }
                    ],
                    "metadata": {
                        "Step description": "Copy the data in In1 and In2 to Out1"
                    }
                }
            },
            {
                "transformStepName": "Second inputs to output",
                "inputFields": [
                    "In3",
                    "In4"
                ],
                "outputFields": [
                    "Out2"
                ],
                "transformActionName": "tableFieldsToJson",
                "transformParams": {
                    "mapOfFieldsToProperties": [
                        {
                            "propertyPath": "In3",
                            "fieldName": "In3"
                        },
                        {
                            "propertyPath": "In4",
                            "fieldName": "In4"
                        }
                    ],
                    "metadata": {
                        "Step description": "Copy the data in In3 and In4 to Out2"
                    }
                }
            }
        ]
    },
    "responseOptions": {
        "binaryFormat": "base64",
        "dataFormat": "arrays",
        "numberFormat": "number"
    },
    "authToken": "replaceWithAuthTokenFromCreateSession",
    "apiVersion": "1.0",
    "requestId": "00000002"
}

  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 an integration table

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

    {
    "api": "hub",
    "action": "createIntegrationTable",
    "params": {
        "databaseName": "faircom",
        "tableName": "test1",
        "fields": [
            {
                "name": "In1",
                "type": "VARCHAR",
                "length": 100
            },
            {
                "name": "In2",
                "type": "INTEGER"
            },
            {
                "name": "In3",
                "type": "VARCHAR",
                "length": 100
            },
            {
                "name": "In4",
                "type": "INTEGER"
            },
            {
                "name": "Out1",
                "type": "VARCHAR",
                "length": 200
            },
            {
                "name": "Out2",
                "type": "VARCHAR",
                "length": 200
            }
        ],
        "transformName": "TestTransform1"
    },
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

  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 new integration table

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

    {
    "api": "db",
    "action": "insertRecords",
    "params": {
        "tableName": "test1",
        "dataFormat": "arrays",
        "databaseName": "faircom",
        "fieldNames": [
            "In1",
            "In2",
            "In3",
            "In4"
        ],
        "sourceData": [
            [
                "1",
                2,
                "3",
                4
            ]
        ]
    },
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

  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 Table Records

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

  2. Click the Table Records tab.

  3. Observe the columns and data that the transform created and stored by using the horizontal scrollbar.

    Note

    The In1, In2, In3, and In4 fields show the inserted data fields. The out1 and out2 fields show the data from In1, In2, In3, and In4 , but are transformed to JSON.

    The "out1" field will look like this:

    {
"In1": "1",
"In2": 2
}

    The "out2" field will look like this:

    {
"In3": "3",
"In4": 4
}