Xbasic

json_shred Function

Syntax

result as C = JSON_shred(JSON as C, schema as C [, ParentArrayName as C])

Arguments

JSONCharacter

The JSON to be shredded

schemaCharacter

The description of the 'shape' of the input JSON and the definition of any 'key' fields that should be added to the result.

ParentArrayNameCharacter

The name of the top level array in the result.

Returns

resultCharacter

The shredded JSON.

Description

Converts a complex JSON string representing an array of objects with nested arrays into individual arrays that do not have any nested arrays.

Discussion

Consider the following JSON string:

[
    {"Firstname": "John", "Lastname" : "Smith", "City" : "Boston", "State" : "MA", "Children": [
        {"Name" : "Callie", "Age" : 5},
        {"Name" : "Griffin", "Age" :3},
        {"Name" : "Luke", "Age" : 1}
        ]
    }, 
    {"Firstname": "Henry", "Lastname" : "Rhodes", "City" : "New York", "State" : "NY", "Children": [
        {"Name" : "Howard", "Age" : 15},
        {"Name" : "Robert", "Age" : 11}
        ]
    }
]

The JSON is an array in which each object in the array has a nested array of 'children'.

This JSON can be 'shredded' to produce this result:

{
    "__top": [
        {
            "Firstname": "John",
            "Lastname": "Smith",
            "City": "Boston",
            "State": "MA"
        },
        {
            "Firstname": "Henry",
            "Lastname": "Rhodes",
            "City": "New York",
            "State": "NY"
        }
    ],
    "Children": [
        {
            "Name": "Callie",
            "Age": 5
        },
        {
            "Name": "Griffin",
            "Age": 3
        },
        {
            "Name": "Luke",
            "Age": 1
        },
        {
            "Name": "Howard",
            "Age": 15
        },
        {
            "Name": "Robert",
            "Age": 11
        }
    ]
}

The result is an object with two arrays: __top and Children. Notice however, that since the 'Children' array is no longer nested inside each object in the top level array, the linkage between the entries in the 'Children' array and their parent has been lost.

In the above example, the JSON_shred() function was called using a schema that did not specify that any key fields should be added to the Children array.

The schema used to produce the above result was:

[
    {
        "Children" : [
            {
            }
        ]
    }
]

Notice how the schema defines the 'shape' of the input JSON but does not specify that any additional 'key' fields should be added to the Children array.

In order not to loose the relationship between each object in the Children array to its parent, a key field must be added to each object in the Children array that points to its parent record. The desired output from the shredding operation is shown below:

{
    "__top": [
        {
            "Firstname": "John",
            "Lastname": "Smith",
            "City": "Boston",
            "State": "MA"
        },
        {
            "Firstname": "Henry",
            "Lastname": "Rhodes",
            "City": "New York",
            "State": "NY"
        }
    ],
    "Children": [
        {
            "key": "John Smith",
            "Name": "Callie",
            "Age": 5
        },
        {
            "key": "John Smith",
            "Name": "Griffin",
            "Age": 3
        },
        {
            "key": "John Smith",
            "Name": "Luke",
            "Age": 1
        },
        {
            "key": "Henry Rhodes",
            "Name": "Howard",
            "Age": 15
        },
        {
            "key": "Henry Rhodes",
            "Name": "Robert",
            "Age": 11
        }
    ]
}

Notice that each record in the 'Children' array now has a property called 'key' that points to its parent record. To obtain the above result, the following schema is used:

[
    {
        "Children" : [
            {
            	"key": "{../Firstname} {../LastName}"
            }
        ]
    }
]

The schema specifies that each object in the 'Children' array should have a property called 'key' whose value is the "Firstname' and "Lastname" property in the immediate parent object. The syntax used to specify a value from a parent object is ../. You can reference properties at the grand parent level using ../../, and so on.

Here is a more complex example that shows multiple levels of nested arrays in the input JSON. Notice that the "Hobbies" array is an array of strings, not an array of objects. This array is converted into an array of objects in the result.

[
    {"Firstname": "John", "Lastname" : "Smith", "City" : "Boston", "State" : "MA", "Children": [
        {"Name" : "Callie", "Age" : 5},
        {"Name" : "Griffin", "Age" :3, "Hobbies" : ["Leggo","Star Wars"]},
        {"Name" : "Luke", "Age" : 1}
        ]
    }, 
    {"Firstname": "Henry", "Lastname" : "Rhodes", "City" : "New York", "State" : "NY", "Children": [
        {"Name" : "Howard", "Age" : 15},
        {"Name" : "Robert", "Age" : 11, "Hobbies" : ["Soccer"]}
        ]
    }
]

Here is the schema that could be used to shred the above JSON:

[
    {
        "Children" : [
            {
                "Hobbies" : [ 
                    { 
                        "hobbyKey" : "{../../Firstname} {../../Lastname}|{../Name}" 
                    }
                ],
                "key": "{../Firstname} {../LastName}"
            }
        ]
    }
]

And here is the resulting output from the shredding operation:

{
    "__top": [
        {
            "Firstname": "John",
            "Lastname": "Smith",
            "City": "Boston",
            "State": "MA"
        },
        {
            "Firstname": "Henry",
            "Lastname": "Rhodes",
            "City": "New York",
            "State": "NY"
        }
    ],
    "Children": [
        {
            "key": "John Smith",
            "Name": "Callie",
            "Age": 5
        },
        {
            "key": "John Smith",
            "Name": "Griffin",
            "Age": 3
        },
        {
            "key": "John Smith",
            "Name": "Luke",
            "Age": 1
        },
        {
            "key": "Henry Rhodes",
            "Name": "Howard",
            "Age": 15
        },
        {
            "key": "Henry Rhodes",
            "Name": "Robert",
            "Age": 11
        }
    ],
    "Hobbies": [
        {
            "hobbyKey": "John Smith|Griffin",
            "__value": "Leggo"
        },
        {
            "hobbyKey": "John Smith|Griffin",
            "__value": "Star Wars"
        },
        {
            "hobbyKey": "Henry Rhodes|Robert",
            "__value": "Soccer"
        }
    ]
}