JSON Record Content List (Explicit Types)

Describes a list of data fields (as name/value pairs), a data table schema, nested data records (if any), along with a number of additional properties for a data record entity of a specific ID.

Unlike a JSON Record Content List structure, this structure includes a data table schema (of data column/field data types) and uses specific JSON types to represent the value of data fields in the data record.

Tip: A data record entity (in the root or master data table) can contain one or more data tables that each contain one or more data record entities (nested data record entities).

A nested data record entity can itself contain one or more data tables that each contain one or more nested data record entities, and so on for potentially multiple levels of nested data tables and data record entities.

A data record entity that contains a data table of nested data record entities is considered to be the parent of the data record entities contained in that data table (which are considered to be the children).

See the Data Entities page of the Technical Overview section for further detail on data set and data record entities.

Note: Date/time values are stored in the OL Connect database as Unix timestamps, i.e. the total number of seconds from the starting time of UTC (Universal Time, Coordinated) to the current time (with zero time zone offset). For instance: 1675950179.

In this JSON structure, dates are represented the same way.

Structure

The structure consists of an object with the following name/value pairs:

• id – the data record entity identifier (type of number)

• schema – the data table schema for the data record entity, consisting of an object with the following name/value pairs:

• columns – a list of the data columns/fields in the data table schema and their corresponding data types, consisting of an object with one or more name/value pairs:

• <name> – the name (name) and data type of the data field (value of either BOOLEAN, STRING, HTMLSTRING, INTEGER, FLOAT, DATETIME or CURRENCY) (type of string)

• tables – a list of any nested data tables in the data record entity, consisting of an object with one or more name/value pairs:

• <name> – the name (name) of the data table and the data table schema for the data record entities it contains, consisting of an object with the following name/value pair:

• columns – a list of the data columns/fields in the data table schema and their corresponding data types, consisting of an object with one or more name/value pairs:

• <name> – the name (name) and data type of the data field (value of either BOOLEAN, STRING, HTMLSTRING, INTEGER, FLOAT, DATETIME or CURRENCY) (type of string)

• fields – a list of the data fields in the data record entity and their corresponding data values, consisting of an object with one or more name/value pairs:

• <name> – the name (name) and data value of the data field (type of either string, number, or boolean)

• tables – a list of any nested data tables in the data record entity, consisting of an object with one or more name/value pairs:

• <name> – the name (name) of the data table and a list the data record entities it contains, consisting of an array of objects each with the following name/value pairs:

• id – the data record entity identifier (type of number)

• fields – a list of the data fields in the data record entity and their corresponding data values, consisting of an object with one or more name/value pairs:

• <name> – the name (name) and data value of the data field (type of either string, number, or boolean)

Specific to data record entities that are children of a data set entity (data record entities in the root or master data table), an additional name/value pair is included:

• datasetid – the data set entity identifier of parent entity (type of number)

If a data record entity contains boundary information (set from the data source during data mapping), then an additional name/value pair is also included:

• boundaries – the boundaries for the data record, consisting of an object with the following name/value pairs:

• start – the starting boundary value for the data record (type of number)

• end – the ending boundary value for the data record (type of number)

Example

The following are examples of this structure:

{
    "schema": {
        "columns": {
            "ID": "STRING",
            "Gender": "STRING",
            "FirstName": "STRING",
            "LastName": "STRING",
            "ExtraData": "STRING"
        }
    },
    "id": 12345,
    "datasetid": 34567,
    "fields": {
        "ID": "CU00048376",
        "Gender": "M.",
        "FirstName": "Benjamin",
        "LastName": "Verret",
        "ExtraData": ""
    }
}

{
    "schema": {
        "columns": {
            "ItemNumber": "STRING",
            "ItemDesc": "STRING",
            "ItemUnitPrice": "CURRENCY",
            "ItemOrdered": "INTEGER",
            "ItemTotal": "CURRENCY",
            "ExtraData": "STRING"
        }
    },
    "id": 45678,
    "fields": {
        "ItemNumber": "PSM002",
        "ItemDesc": "PSM Production (unlimited)",
        "ItemUnitPrice": "495.00",
        "ItemOrdered": 2,
        "ItemTotal": "990.00",
        "ExtraData": ""
    }
}

{
    "schema": {
        "columns": {
            "ID": "STRING",
            "Date": "DATETIME",
            "DueDate": "DATETIME",
            "InvNumber": "STRING",
            "Gender": "STRING",
            "FirstName": "STRING",
            "LastName": "STRING",
            "TotalOrdered": "INTEGER",
            "InvSubTotal": "CURRENCY",
            "InvTaxTotal": "CURRENCY",
            "InvTotal": "CURRENCY",
            "ExtraData": "STRING"
        },
        "tables": {
            "detail": {
                "columns": {
                    "ItemNumber": "STRING",
                    "ItemDesc": "STRING",
                    "ItemUnitPrice": "CURRENCY",
                    "ItemOrdered": "INTEGER",
                    "ItemTotal": "CURRENCY",
                    "ExtraData": "STRING"
                }
            }
        }
   },
    "id": 23456,
    "datasetid": 12345,
    "fields": {
        "ID": "CU00048376",
        "Date": "2023-01-26T16:47:35Z",
        "DueDate": "2023-02-23T14:00:00Z",
        "InvNumber": "INV9441991",
        "Gender": "M.",
        "FirstName": "Benjamin",
        "LastName": "Verret",
        "TotalOrdered": 3,
        "InvSubTotal": "1485.00",
        "InvTaxTotal": "111.38",
        "InvTotal": "1596.38",
        "ExtraData": ""
    },
    "tables": {
        "detail": [
            {
                "id": 45678,
                "fields": {
                    "ItemNumber": "PSM002",
                    "ItemDesc": "PSM Production (unlimited)",
                    "ItemUnitPrice": "495.00",
                    "ItemOrdered": 2,
                    "ItemTotal": "990.00",
                    "ExtraData": ""
                }
            },
            {
                "id": 45679,
                "fields": {
                    "ItemNumber": "PSM005",
                    "ItemDesc": "Upgrade (Starter to Web)",
                    "ItemUnitPrice": "495.00",
                    "ItemOrdered": 1,
                    "ItemTotal": "495.00",
                    "ExtraData": ""
                }
            }
        ]
    }
}

{
    "schema": {
        "columns": {
            "ID": "STRING",
            "Gender": "STRING",
            "FirstName": "STRING",
            "LastName": "STRING",
            "ExtraData": "STRING"
        }
    },
    "id": 12345,
    "datasetid": 34567,
    "boundaries": {
        "start": 0,
        "end": 4
    },
    "fields": {
        "ID": "CU00048376",
        "Gender": "M.",
        "FirstName": "Benjamin",
        "LastName": "Verret",
        "ExtraData": ""
    }
}