ajJSONFromSchema function

Description

The ajJSONFromSchema function returns a JSON string based on a Data Relationship Schema. If there are multiple tables with dependency, the JSON will contain multiple levels. If a data dictionary is defined in the given data relationship schema, it will be used for data type conversion.

Syntax

ajJSONFromSchema(data_relationship_header, data_relationship)

Argument Name	Argument Type	Description
data_relationship_header (required)	Range / Array	The range which defines the header of the Data Relationship Schema.
data_relationship (required)	Range / Array	The range which defines the content of the Data Relationship Schema. The empty row would be ignored.

The function will return:

1) Return Value: JSON string

2) Return Type: Single Value / Multiple values (array formula). It split the returned JSON string into multiple cells when the string length is longer than a single cell character limit - 32767. The remaining string will be extended in the row array.

Example

Assume there are two tables, Customer and Email.

Example 1

Define the Data Relationship as shown below.

Returned JSON:

{
    "customerList": [
        {
            "Customer Level": "5",
            "Date of Birth": 28127,
            "Emails": [
                {
                    "Email": "CHAN.TAN.MAN@hotmail.com",
                    "Type": "Work"
                    },
                {
                    "Email": "CHAN.TAN.MAN@gmail.com",
                    "Type": "Personal"
                }
            ],
            "Gender": "M",
            "Name": "CHAN TAI MAN",
            "Telephone": "22205555"
        },
        {
            "Customer Level": "2",
            "Date of Birth": 32239,
            "Emails": [
                {
                    "Email": "JERRY.SZE@yahoo.com",
                    "Type": "Personal"
                },
                {
                    "Email": "JERRY.SZE@axisoft.com",
                    "Type": "Work"
                }
            ],
            "Gender": "M",
            "Name": "JERRY SZE",
            "Telephone": "22203333"
        },
        {
            "Customer Level": "4",
            "Date of Birth": 32205,
            "Gender": "F",
            "Name": "MERRY SO",
            "Telephone": "22201111"
        },
        {
            "Customer Level": "1",
            "Date of Birth": 28827,
            "Gender": "M",
            "Name": "TOM HUI",
            "Telephone": "22204444"
        },
        {
            "Customer Level": "3",
            "Date of Birth": 29718,
            "Gender": "F",
            "Name": "VICKY CHAN",
            "Telephone": "22202222"
        }
    ]
}

• The JSON has two levels.
• The key field is Name.
• Customer records 'CHAN TAI MAN' and 'JERRY SZE' have email records by using the Name as joining key. For the other Customer records, only Customer level information will appear in the JSON.
• The Date of Birth of the Customer table becomes a number as its value type of the cell is Date.

Example 2

In this case, Multiple Row is set as FALSE and then only the first matched row in the Email table would appear in the JSON.

Returned JSON:

{
    "customerList":[
        {
            "Name":"CHAN TAI MAN",
            "Date of Birth":28127,
            "Gender":"M",
            "Customer Level":"5",
            "Telephone":"22205555",
            "Emails":{
                "Email":"CHAN.TAN.MAN@hotmail.com",
                "Type":"Work"
            }
        },
        {
            "Name":"JERRY SZE",
            "Date of Birth":32239,
            "Gender":"M","Customer Level":"2",
            "Telephone":"22203333",
            "Emails":{
                "Email":"JERRY.SZE@yahoo.com",
                "Type":"Personal"
            }
        },
        {
            "Name":"MERRY SO",
            "Date of Birth":32205,
            "Gender":"F",
            "Customer Level":"4",
            "Telephone":"22201111"
        },
        {
            "Name":"TOM HUI",
            "Date of Birth":28827,
            "Gender":"M",
            "Customer Level":"1",
            "Telephone":"22204444"
        },
        {
            "Name":"VICKY CHAN",
            "Date of Birth":29718,
            "Gender":"F","Customer Level":"3",
            "Telephone":"22202222"
        }
    ]
}       

Example 3

In this case, the Data Dictionary is used.

In the Data Dictionary,

• The field Gender of the Customer table is converted to lowercase.
• The field Date of Birth of the Customer table is converted to 'yyyy-MM-dd' format.
• The field Email of the Email table is converted to uppercase.

Returned JSON:

{
    "customerList":[
        {
            "Name":"CHAN TAI MAN",
            "Date of Birth":"1977-01-02",
            "Gender":"m",
            "Customer Level":"5",
            "Telephone":"22205555",
            "Emails":{
                "Email":"CHAN.TAN.MAN@HOTMAIL.COM",
                "Type":"Work"
            }
        },
        {
            "Name":"JERRY SZE",
            "Date of Birth":"1988-04-06",
            "Gender":"m",
            "Customer Level":"2",
            "Telephone":"22203333",
            "Emails":
            {
                "Email":"JERRY.SZE@YAHOO.COM",
                "Type":"Personal"
            }
        },
        {
            "Name":"MERRY SO",
            "Date of Birth":"1988-03-03",
            "Gender":"f","Customer Level":"4",
            "Telephone":"22201111"
        },
        {
            "Name":"TOM HUI",
            "Date of Birth":"1978-12-03",
            "Gender":"m",
            "Customer Level":"1",
            "Telephone":"22204444"
        },
        {
            "Name":"VICKY CHAN",
            "Date of Birth":"1981-05-12",
            "Gender":"f",
            "Customer Level":"3",
            "Telephone":"22202222"
        }
    ]
}                               

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Does not use ajAddress in Table address or Data dictionary address.
Duplicate table name in the data relationship table.
For REST API, the root table was configured with Multiple Row = TRUE.
Invalid table name, it shall begin with an uppercase letter and contain alphabets, numbers, and underscores only.
Key is not provided but parent table contain multiple row.
Parent table does not exist.
Parent table name is empty but key is not empty.
Table address is empty.
Table is empty.
Table name is a reserved Java keyword. For example, new, return, default etc.
Data relationship header range is invalid, it must contain at least one column.
Data relationship range is invalid, it must contain at least one column.