JSON to fields API reference

API reference for the JSON to fields transform method

Request examples

Minimal

{
  "api": "hub",
  "apiVersion": "1.0",
  "action": "createIntegrationTable",
  "params": {
    "tableName": "test1",
    "fields": [
      {
        "name": "In1",
        "type": "json"
      },
      {
        "name": "Out1",
        "type": "varchar"
      }
    ],
    "transformSteps": [
      {
        "transformStepMethod": "jsonToTableFields",
        "mapOfPropertiesToFields": [
            {
                "recordPath": "in1.out1",
                "fieldName": "out1"
            }
        ]
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

The following example is not the maximal example for creating an integration table, rather it is the maximal example for creating a JSON to fields transform.

{
  "api": "hub",
  "apiVersion": "1.0",
  "action": "createIntegrationTable",
  "params": {
    "tableName": "test1",
    "ownerName": "admin",
    "databaseName": "faircom",

    "fields": [
      {
        "name": "In1",
        "type": "json"
      },
      {
        "name": "Out1",
        "type": "varchar"
      },
      {
        "name": "Out2",
        "type": "float"
      }
    ],

    "transformSteps": [
      {
        "transformStepMethod": "jsonToTableFields",
        "mapOfPropertiesToFields": [
            {
                "recordPath": "in1.out1",
                "fieldName": "out1"
            },
            {
                "recordPath": "in1.out2",
                "fieldName": "out2"
            }
        ]
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Request properties ("params")

Property Description Default Type Limits (inclusive)

databaseName

The "databaseName" property specifies the database that contains an object, such as a table or code package. If it is set to null or is omitted, it defaults to the default database of the JSON Action session (see "createSession" and the "defaultDatabaseName" property). 

You specify this property when you want to use a different database instead of the default. 

This property is useful because objects, such as tables and code packages, can have the same name in multiple databases. This feature allows you to create multiple environments in the same server and reuse the same JSON actions in each environment. For example, you can create "dev", "test", "stage", and "prod" databases on the same server and use the "defaultDatabaseName" or "databaseName" properties to specify the desired environment.

It is an error to set "databaseName" to the empty string "".

If no default database is specified during "createSession", the server sets the "defaultDatabaseName" to the "defaultDatabaseName" value specified in the services.json file.

 Defaults to the session's "defaultDatabaseName" property string 1 to 64 bytes

fields

The "fields" property specifies which fields in a table are included in the index and how each field should be indexed.

  • The "fields" array contains one object for each field in an index.
  • Each field has separate index settings such as:
    • Case sensitivity
    • Sort order
    • Comparison algorithm
  • For example, an index containing two fields can use different settings for each field. It can index the first field as case sensitive, ascending, and forward comparison, and the second field as case insensitive, descending, and reverse comparison.
  • When a string field is indexed as case insensitive, the server ignores the case of the ASCII letters in the field value; otherwise, it does an exact comparison.
  • When a field is indexed for reverse comparison, the server starts comparisons with the last byte in the field value rather than the first. This speeds up the comparison process when the last bytes of a field value are most likely to be dissimilar.
  • When a field is sorted ascending, queries that use the index return results in ascending index order for that field; otherwise, they return results in decreasing order.
  • When you configure multiple fields for sorting in an index, queries return results in the order the fields are listed — for example, if an index has birthday and name fields specified in the fields array in that order, with the birthday sorted descending and the name sorted ascending, then queries return results with the most recent birthdays first and multiple names on the same birthday are returned in ascending alphabetical order.
"fields":
[
  {
    "name": "name",
    "caseInsensitive": true,
    "sortDescending": true,
    "reverseCompare": false
  }
]

Required - No default value

 array of objects

"caseInsensitive"

"name"

"reverseCompare"

"sortDescending"

"autotimestamp"

"autoValue"

"defaultValue"

"length"

"name"

"nullable"

"primaryKey"

"scale"

"type"

fields

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. The "createLabel" and "alterLabel" actions return an error if the "group" and "name" properties do not create a unique label name.

The "id" property also uniquely identifies each label, so you can rename a label's group and name without breaking "id" references to the label.

 

 Required - No default value string 1 to 64 bytes

fields

.type

The "type" property specifies the field's data type. See Data types for the limits, valid values, and whether "length" and "scale" are required.

 

Request example

"fields": [
  {
    "name": "j",
    "type": "number"
  }
]
 Required - No default value string

"json"

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

ownerName

The "ownerName" property specifies the account that owns an object, such as a table or code package. See "createSession" and the "defaultOwnerName" property for more details. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

It is an error to set "ownerName" to the empty string "".

If no default owner is specified during "createSession", the server sets the "defaultOwnerName" to the "defaultOwnerName" value specified in the services.json file.

 Optional with default of the session's "defaultOwnerName" property string 1 to 64 bytes

tableName

The "tableName" property contains the unique, user-defined name of a table.

See table name in System specifications for the table naming requirements and limitations.

 

"params": {
  "tableName": "ctreeTable"
}
 Required - No default value string 1 to 64 bytes

transformSteps

 The "transformSteps" property specifies an array of transform objects. Required - No default value array of objects

0 or more objects containing 1 or more of the following properties:

"codeName"

"databaseName"

"ownerName"

"mapOfPropertiesToFields"

"targetDatabaseName"
"targetOwnerName"

"targetTableName"

"transformStepMethod"

"transformStepName"

"transformStepService"

transformSteps

.mapOfPropertiesToFields

The "mapOfPropertiesToFields" property takes fields in the table and maps them to a field containing JSON properties. Each object maps a field in a table to a JSON property in another field.

  • Required properties
    • "recordPath"
    • "fieldName"
  • Optional properties
    • "binaryFormat"
    • "numberFormat"
    • "variantFormat"
    • "dateFormat"
    • "timeFormat"
 Optional with default of [] array

"binaryFormat"

"dateFormat"

"fieldName"

"numberFormat"

"recordPath"

"timeFormat"

"variantFormat"

transformSteps

mapOfPropertiesToFields

.fieldName

 The "fieldName" property specifies the name of a field in a table. Required - No default value string 1 to 64 bytes

transformSteps

mapOfPropertiesToFields

.recordPath

The "recordPath" property specifies the location in a record where the server reads or writes a JSON value. It specifies a field name followed by an optional JSONPath. For more details, see "recordPath".

 

 Required - No default value string 0 to 256 bytes

transformSteps

.transformStepMethod

 The "transformStepMethod" property specifies the type of transform, such as the "javascript" transform method that runs JavaScript to change the table's data, or the "jsonToTableFields" transform method that extracts values from properties in a JSON field and stores them in other fields. For more details, see "transformStepMethod". Required - No default value string enum

"javascript"

"jsonToDifferentTableFields"

"jsonToTableFields"

"tableFieldsToJson"

 

Response examples

Success

{
 "result": {},
 "requestId": "00000007",
 "errorCode": 0,
 "errorMessage": "",
 "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

 

json fields

Was this article helpful?

Give feedback about this article

Related Articles