Description of the JSON Tree export format of the GET Export Report API.
The jsontree
format returns an array of JSON objects for each distinct row
of the report's base table which matches the requested filter
s. 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
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.
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 }, ... ], ... },... ] }
{ "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" }] } ] }
Status Code:
400
and 500
JSON responses with error have this structure:
{ // messages: array of message Strings. "messages":[ ... ] }
{ "messages":[ "The filter \"/student@id & 173\" does not match filter grammar. The \"Operator\" definition is missing or unparseable." ] }
distinct
parameterTree 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 }] }]
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:
ID | NAME |
---|---|
1 | Foo |
2 | Bar |
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" }] } ]
Append the pretty=true
parameter to pretty print jsontree
export results.