GET Export Report JSON Tree Format

Abstract

Description of the JSON Tree export format of the GET Export Report API.

Table of Contents

The jsontree format returns an array of JSON objects for each distinct row of the report's base table which matches the requested filters. The JSON object properties are either the requested <column> ids or requested descendant <relationship> ids.

It is required to specify table primaryKeyColumns attributes to activate the jsontree format.

See the htmltree export format for an HTML analog of jsontree.

See the json export format for a denormalized return of JSON value arrays.

Format Parameter: jsontree

Response Content-Type: application/json

Pull Reports™ Standard Edition Format

This format is only available under the Pull Reports™ Standard Edition license. To evaluate this format with a Pull Reports™ Community Edition license, enable evaluation mode.

OK Response

Status Code: 200

JSON responses without error have this structure:

{
// meta: contains meta information about the results. 
"meta":{
    // filters: (Optional) contains information about the filters applied to this 
    // result set if applicable. 
    "filters": [
          {"source":string    // String representation of the original filter parameter.
           ,"readable":string // Contains the readable filter String with Table and Column 
                              // DisplayName's substituted for id's.
          },...
    ]
    // subqueries: (Optional) contains information about the subqueries applied to this 
    // export result. Only present if a <subquery>'s parent <table> is included 
    //  within the export result and the <subquery> has a description.
    "subqueries": [
          {"tablePath":string    // The table resource path to the <subquery>
           ,"displayName":string // The displayName of the <subquery> parent <table>
           ,"description":string
          },...
    ]
    // columns: meta information about the columns of the result set. The order 
    // of child objects matches the order of array elements in each 
    // "data" array.
    ,"columns": [
          {"id":string           // The id of the <column>
           ,"displayName":string // The displayName of the <column>
           ,"tablePath":string   // The table resource path to the <column>
           }
           ...
    ]
    // sorts: (Optional) contains information about the column sorts applied
    // to this export result if applicable.
    ,"sorts":[{
        "tablePath":string
        ,"columnId":string
        ,"direction":string
        ,"source":string // the value of the "sort" term.
    },...]
    // The total number of results to be returned based on the filter and columns
    // parameters. This can differ from the length of the data array
    // if the limit and offset parameters are specified. 
    ,"totalCount":integer
    // Only present when license is missing or invalid
    ,"exportLimitedReason":string
}
// data: array of Objects representing each distinct row from the report's base
// table which matches the requested filters. Object properties are either the
// requested column ids or descendant relationship ids.
// All values are coerced to a JavaScript type appropriate for their database type
// (i.e. String, date, number). <column>s with a <url_template> are represented via
// a JavaScript Object with attributes "url" and "value"
,"data": [
   {"columnId1": value,
    "columnId2": value,
    "columnId3": {"url":string,"value":string},
    // The value of a relationship property of cardinality="one" is a JSON Object
    // with column and relationship id properties.
    "relationshipIdOfCardinalityOne": {
      "relationshipColumnId1": value,
      "relationshipColumnId2": value,
    },
    // The value of a relationship property of cardinality="many" is a JSON Array
    // of Objects each with column and relationship id properties.
    "relationshipIdOfCardinalityMany": [{
        "relationshipColumnId1": value,
        "relationshipColumnId2": value
      }, ...
    ], ...
   },...
]
}

Example 1. Example JSON Output Format

{
"meta": {
    "filters":[
         {
             "source":"/student@fname like 'J%'"
             ,"readable":"'Student', 'First Name' like 'J%'"
         }
         ,{
             "source":"/student@lname = 'Hernadez'"
             ,"readable":"'Student', 'Last Name' = 'Hernandez'"
         }
    ]
    ,"columns":[
         {
             "id":"id"
             ,"displayName":"Student ID"
             ,"tablePath":"/student"
         }
         ,{
             "id":"fname"
             ,"displayName":"First Name"
             ,"tablePath":"/student"
         }
         ,{
             "id":"lname"
             ,"displayName":"Last Name"
             ,"tablePath":"/student"
         }
         ,{
             "id":"sportname"
             ,"displayName":"Sport Name"
             ,"tablePath":"/student/sports"
         }
         ,{
             "id":"addressState"
             ,"displayName":"Address State"
             ,"tablePath":"/address/state"
         }
         ,{
             "id":"addressCity"
             ,"displayName":"Address City"
             ,"tablePath":"/address/city"
         }
    ]
    ,"totalCount":2
}
,"data":[
    {
        "id":177
        ,"fname":"Joe"
        ,"lname":{"url":"/some/url/177","value":"Hernandez"}
        ,"address": {
             "state":"New York"
             ,"city":"Albany"
        }
        ,"sports": [{
             "sportname":"Soccer"
        }]
    }
    ,{
        "id":213
        ,"fname":"Julia"
        ,"lname":{"url":"/some/url/213","value":"Hernandez"}
        ,"address": {
             "state":"New York"
             ,"city":"Albany"
        }
        ,"sports": [{
             "sportname":"Tennis"
          },{
             "sportname":"Cross Country"
        }]
    }
]
}

Error Response

Status Code: 400 and 500

JSON responses with error have this structure:

{
 // messages: array of message Strings.
 "messages":[
     ...
 ]
}

Example 2. Example JSON Error Output Format

{
 "messages":[
      "The filter \"/student@id & 173\" does not match filter grammar. The \"Operator\" definition is missing or unparseable."
  ]
}

Behavior of the `distinct` parameter

Tree formats return distinct results from the report's base table regardless of the value of the distinct parameter. The distinction is on the base table's primaryKeyColumns. The same is true for any descendant <relationship> table which has at least one relationship descendant of cardinality many included in the export results.

The distinct parameter only effects the distinct return of relationship descendant tables which themselves have no relationship descendants of cardinality many included in the export results. These are "leaf" relationship tables in the tree export graph.

To demonstrate, take a report with the following table structure:

Base Table A
- Child Table B, cardinality="one"
- Child Table C, cardinality="many"
  - Child Table D, cardinality="many"

A GET Export Report request with distinct=true which includes columns from each Table A, B, C, and D in the columns parameter would return distinct results from each table for these reasons:

Table A columns would be distinct on its primaryKeyColumns because it is the report's base table.
Table B results would be distinct because of Table B's many-to-one join to Table A.
Table C columns would be distinct on its primaryKeyColumns because it has at least one relationship table of cardinality many, Table D, included in the export results.
Table D results would be distinct on the Table D columns included within the export results because of the distinct=true parameter.

Here is an example return of two Table A records from this tree export with distinct=true.

// Base Table A: first record
[{
  "pk":1,
  "column1": "Foo",
  "childB":{"column2":"Baz"},
  "childC":[{
    "name":"ABC",
    "childD":[{"column3":"XYZ"}] // distinct results due to distinct=true
  }]
},
// Base Table A: second record
{
  "pk":2,
  "column1": "Bar",
  "childB":{"column2":"Bah"},
  "childC":[{
    "name":"CBA",
    "childD":[{"column3":"ZYX"}] // distinct results due to distinct=true
  }]
}]

If requested with distinct=false, Table A, B, and C results would continue to be distinct. However, Table D results would not be distinct because it is a leaf relationship table node with no descendant relationships of cardinality many.

// Base Table A: first record
[{
  "pk":1,
  "column1": "Foo",
  "childB":{"column2":"Baz"},
  "childC":[{
    "name":"ABC",
    "childD":[{"column3":"XYZ"},{"column3":"XYZ"}] // no distinct results due to distinct=false
  }]
},
// Base Table A: second record
{
  "pk":2,
  "column1": "Bar",
  "childB":{"column2":"Bah"},
  "childC":[{
    "name":"CBA",
    "childD":[{"column3":"ZYX"},{"column3":"ZYX"}] // no distinct results due to distinct=false
  }]
}]

Behavior of the `filter` parameter

Tree formats apply filter restrictions to the base table export results but not descendant relationship results.

To demonstrate, take a report with the following table structure:

Base table A. Columns: ID, NAME
- Child table B. Columns: ID, TABLE_A_ID, TYPE

Table A and B have the following values:

Table 1. Table A values

ID	NAME
1	Foo
2	Bar

Table 2. Table B values

ID	TABLE_A_ID	TYPE
10	1	TYPE1
11	1	TYPE2
12	2	TYPE1

Requesting tree format export results with filter=/A/B@type = 'TYPE2' would only return the first row from Table A because its join to Table B matched the filter /A/B@type = 'TYPE2'. However, both records from Table B are present in the B relationship property because the filter only restricts the base table results.

...
,"data": [
  {"id": 1,
   "name": "Foo",
   "B": [
     { "id": 10, "type": "TYPE1" },
     { "id": 11, "type": "TYPE2" }]
  }
]

Pretty printing the export results

Append the pretty=true parameter to pretty print jsontree export results.