ajMakeJsonFromSchema function

Description

The ajMakeJsonFromSchema function returns a JSON string based on a Data Relationship Schema. If there are multiple tables with dependency, the JSON will contain multiple levels.

Syntax

ajMakeJsonFromSchema(tables_relationship_header, tables_relationship)

Argument Name	Description
tables_relationship_header (required)	Specify the range which defines the header of the Data Relationship Schema.
tables_relationship (required)	Specify the range which defines the content of the Data Relationship Schema. The empty row would be ignored.

The function will return:

1) Content type: JSON array

2) Method: Within a cell / row cell array. 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"
        }
    ]
}