Skip to main content

REST — Data Joins

  • Updated

REST — Data Joins

Joins are the relationship data objects have to each other. Join information is accessible and editable via REST.

light bulb

All requests require a Session Id URL parameter and basic request headers. In the following topic, headers are omitted in the interest of brevity.

Join JSON

Joins are represented as JSON objects with the following properties:

NameTypeWritableDescription
IdstringnoThe unique Id of this join
EntityFromstringrequired-createThe “from” (or “left”) data object of this join
EntityTostringrequired-createThe “to” (or “right”) data object of this join
JoinTypeconstyes (“Inner”)Join Type
RelationshipTypeconstyes (“OneToOne”)Join Relation Type
Weightintegeryes (0)The weight of this join
JoinColumnsarray of JoinColumn JSONrequiredThe data fields which are joined

Example

{
  "Id":               "Shippers.Orders",
  "EntityFrom":       "Shippers",
  "EntityTo":         "Orders",
  "JoinType":         "Inner",
  "RelationshipType": "OneToMany",
  "Weight":           0,
  "JoinColumns": [
    {
      "ColumnFrom": "ShipperID",
      "ColumnTo":   "ShipVia"
    }
  ]
}

JoinColumn JSON

The JoinColumn objects of a join indicate which columns are used to join the data objects. Objects can be joined on multiple join columns (which are AND-ed). Each join requires one or more sets of join columns.

JoinColumn objects have the following properties:

NameTypeWritableDescription
ColumnFromstringrequiredThe join data field for the “from” (or “left”) data object
ColumnTostringrequiredThe join data field for the “to” (or “right”) data object

Example

"JoinColumns": [ 
  {
    "ColumnFrom": "ShipperID",
    "ColumnTo":   "ShipVia"  }
]

List All Joins in the Configuration

GET /rest/Joins

List all the joins in the current configuration. Output is an array of objects, each representing an individual join.

NameTypeDescription
IdstringThe unique Id of this join

Available parameters

NameTypeDescription
entitystringShow only joins that join this data object

Using curl

curl http://{webservice}/rest/Joins?sid={sid} -X GET

Example Response

Status: 200 OK

[
  {
    "Id": "Employees.Orders"
  },
  {
    "Id": "Employees.EmployeeTerritories"
  },
  ...
]

Show Properties of a Specific Join

Show the properties of the join specified by its Id.

GET /rest/Joins/{Id}

Using curl

curl http://{webservice}/rest/Joins/{Id}?sid={sid} -X GET

Example response

Status: 200 OK

{
  "Id":               "Orders.OrderDetails",
  "EntityFrom":       "Orders",
  "EntityTo":         "OrderDetails",
  "JoinType":         "Inner",
  "RelationshipType": "OneToOne",
  "Weight":           0,
  "JoinColumns": [
    {
      "ColumnFrom": "OrderID",
      "ColumnTo":   "OrderID"
    }
  ]
}

Create a New Join

POST /rest/Joins

Using curl

curl http://{webservice}/rest/Joins?sid={sid} -X POST ^
	-d @newJoin.txt

newJoin.txt

{
   "EntityFrom":"Shippers",
   "EntityTo":"Orders",
   "RelationshipType":"OneToMany",
   "JoinColumns":[
      {
         "ColumnFrom":"ShipperID",
         "ColumnTo":"ShipVia"
      }
   ]
}

Example response

Status: 201 Created

{
  "Id":               "Shippers.Orders",
  "EntityFrom":       "Shippers",
  "EntityTo":         "Orders",
  "JoinType":         "Inner",
  "RelationshipType": "OneToMany",
  "Weight":           0,
  "JoinColumns": [
    {
      "ColumnFrom": "ShipperID",
      "ColumnTo":   "ShipVia"
    }
  ]
}

Edit Properties of a Join

Only supply the properties to be edited.

PATCH /rest/Joins/{Id}

Using curl

curl http://{webservice}/rest/Joins/{Id}?sid={sid} -X PATCH ^
	-d "{'JoinType':'LeftOuter'}"

Example response

Status: 204 No Content

Delete a Join

DELETE /rest/Joins/{Id}

Using curl

curl http://{webservice}/rest/Joins/{Id}?sid={sid} -X DELETE

Example response

Status: 204 No Content

Related articles