Navigation :

Nested Datasets

Introduction & Fetching Nested Datasets

Nested datasets allow a single query to return an object-style fetch result, where the returned result can include child elements.

Consider the following JSON result returned from a nested dataset example included in the demo Jarvis application. The parts entry is an array generated by performing a nested child dataset query within the top-level dataset.

{
    "data" : [
        {
            "owner"            : "",
            "registration_num" : "",
            "parts" : [
                {
                    "name" : "Gadget",
                    "id"   : 15
                },
                {
                    "name" : "Sprocket",
                    "id"   : 14
                },
                {
                    "name" : "Widget",
                    "id"   : 13
                }
            ],
            "name"        : "Empty Nest",
            "id"          : 52,
            "class"       : "X Class",
            "description" : ""
        }
    ],
    "logged_in"    : 1,
    "group_list"   : "admin,default",
    "username"     : "admin",
    "returned"     : 1,
    "fetched"      : 1,
    "error_string" : ""
}

Configuration

Child datasets are attached by defining a child attribute in the parent datasets definition.

Consider the following example:

<dataset read="*" write="*" key_list="id">
    <child field="parts" dataset="boat_part">
        <link parent="name" child="boat_name"/>
    </child>
    ...

The child element attributes are:

Attribute	Notes
`field`	The name of the parent row field to contain the child rows.
`dataset`	The name of the dataset which will generated the nested child sub-query data. This must be an XML-defined dataset Exec or Plugin datasets can not be used in nested child queries.
`link`	These elements define the link fields between the parent and child records.

The link element attributes are:

Attribute	Notes
`parent`	The column name from the parent row which will be used to link to the child records. This must be one of the fields returned by the parent `select` clause. In the given example, the parent field is `name`, and the associated value selected for this row is `Empty Nest`.
`child`	The corresponding variable within the child sub-query `select` definition. In the given example, the value `Empty Nest` is used as `{{boat_name}}` within the child dataset `select` definition for this sub-query.

General notes:

Child datasets may themselves have grand-child datasets, and so on.
Do not use recursive child relationships. That will not end well.
Per-dataset hooks are invoked only for the level at which they are defined, not for children.

Regarding links and parameters:

If multiple keys or filter fields are required for the sub-key, there will be more than one link element in the parent dataset definition.
When the sub-query is executed, only the linked variables are available as user variables for the sub-query (and associated hooks). The top-level CGI parameters and numbered/named REST arguments are not passed through to the child query.
Safe parameters (e.g. __username) and application default parameters will be passed to the child sub-query.

Regarding performance:

Each child query will generate significant overhead with Jarvis as it manages per-dataset configuration, hooks and security.
In general, nested datasets should be used only for singleton queries.
If you need to perform child sub-queries on large datasets, you may wish to consider writing a custom plugin with an optimized query sequence.

Inserting / Updating Nested Datasets

It is also possible to perform store operations on nested datasets. The insert and update actions will perform nested dataset operations. You may also use mixed, noting that the _ttype parameter must be specified on all child data.

Submitting the following JSON request will (with the correct datasets) insert a single top-level master object, and then will also insert three sub-objects. With correct link definitions, the returned parent ID will be passed through for the inserts on the child elements.

[
    {
        "parts": [
            {
                "name":"Doodad"
            },
            {
                "name":"Whatsit"
            },
            {
                "name":"Hoosit"
            }
        ],
        "name" : "Mother Hubbard",
        "class": "X Class"
    }
]

An example update is as follows. This updates the class of the top-level Boat object, and performs one insert, one update and one delete of the child Boat Part elements.

Note: You must supply the necessary key values (e.g. the id fields in this case) when updating or deleting an existing parent or child row.

[
    {
        "_ttype": "update",
        "id"    : "1017" ,
        "name"  : "Mother Hubbard",
        "class" : "Makkleson",
        "parts" : [
            { "_ttype": "delete", "id": "23",  },
            { "_ttype": "update", "id": "24", "name": "Whatsitt" },
            { "_ttype": "insert"            , "name": "Thingey" },
        ]
    }
]

JSON examples are given. XML examples are left as an exercise for the reader.