The basic attributes of a column are the name of the column, the data type, whether the column is a primary key, and the XPath. The provider uses the XPath to extract nodes from hierarchical data.

Mark up column attributes in the api:info block of the schema file. You can set the XPath in the other:xPath property, as shown in the example below.

<api:info title="people" desc="People and their vehicles." xmlns:other="xmlns:other="http://apiscript.com/ns?v1">
  <!-- You can modify the name, type, and column size here.  -->
  <attr name="personal.age" xs:type="integer" readonly="false" other:xPath="/json/people/personal/age" />
  <attr name="personal.gender" xs:type="string" readonly="false" other:xPath="/json/people/personal/gender" />
  <attr name="personal.name.first" xs:type="string" readonly="false" other:xPath="/json/people/personal/name/first" />
  <attr name="personal.name.last" xs:type="string" readonly="false" other:xPath="/json/people/personal/name/last" />
  <attr name="source" xs:type="string" readonly="false" other:xPath="/json/people/source" />
  <attr name="vehicles" xs:type="string" readonly="false" other:xPath="/json/people/vehicles" />
</api:info>

The following sections provide more detail on using XPaths to extract columns and rows. To see the column definitions in a complete schema, refer to Customizing Schemas.

Defining Column XPaths

The other:xPath property is used to specify the XPath that selects the column's value from JSON. The XPath is an absolute path: absolute paths start with a '/' and contain the full XPath to the data nested in JSON.

<attr name="ID" xs:type="int" key="true" other:xPath="/value/ID" />

Indexed XPath values can also be used to specify an element within the JSON document in the case that multiple elements with the same name are nested at the same level. The indexes are zero based.

<attr name=Employee1 xs:type="string" other:xPath="value[0]"/>
<attr name=Employee2 xs:type="string" other:xPath="value[1]"/>

Notes:

• XPaths and column names (when used to generate the XPath) are case sensitive.
• Any paths specified outside a JSONPath will only be retrieved if they are found prior to a row (JSONPath) being found, with the exception of the last row pushed (which will push all found paths). This is due to each row being pushed in a streaming fashion.

Defining Row XPaths

A row XPath (JSONPath) specifies the path to an element that repeats at the same height -- an object array that the provider splits into rows. With DataModel set to FlattenedDocuments or Relational, you can specify more than one JSONPath in a semicolon-separated list. See Parsing Hierarchical Data for guides to these data modeling strategies.

The connection string can define the JSONPath or you can define a JSONPath as an attribute of an individual schema. Use the api:set keyword to define the row XPaths for a schema. With DataModel set to FlattenedDocuments or Relational, you can specify more than one XPath in a semicolon-separated list.

<api:set attr="JSONPath" value="$.people;$.people.vehicles;$.people.vehicles.maintenance"/>

A wildcard JSONPath can also be used and is helpful in the case that the JSONPaths are all at the same height but contain different names:

<api:set  attr="JSONPath" value="$.feed.*" />

Defining Column Value Formats

You can set the other:valueFormat property to "aggregate" to identify a column as an aggregate.

• The aggregate option will return the aggregate found at the XPath specified. For example, consider the following:
  

      {
        "repeat": {
          "name": "TestAggregate",
          "myobjcol1": { 
              "myobjcol1": "myData1",
              "myobjcol2": "myData2",
              "myobjcol3": "myData3" 
          }
        }
      }
      

  The following example extracts the myobjcol1 element to a column named MyObjCol1:
  

      <api:info>
        <attr name="MyObjCol1" xs:type="string" other:xPath="myobjcol1" other:valueFormat="aggregate"/>
      </api:info>
      <api:set attr="XPath" value="/repeat"/>
      

  This column value will be the following:
  

        { 
          "myobjcol1": "myData1",
          "myobjcol2": "myData2",
          "myobjcol3": "myData3" 
        }
      

Mapping SELECT criteria to query parameters

Some APIs will support filtering the results of a request by specifying a query parameter. If this parameter maps to a WHERE clause, you can use the other:filter property to program this mapping.

• other:filter is a semicolon separated list of <parameter name>:<operator list>. <parameter name> is the name of the query parameter and <operator list> is a comma-separated list of operators used for the mapping. Valid operators are <, <=, =, >, >=, and LIKE.

The below example is for two query parameters, 'modifiedSince' and 'modifiedBefore', which filter the results based on the 'modifiedAt' column.

    <attr name="ModifiedAt"           xs:type="datetime" readonly="false"   other:xPath="content/properties/modifiedAt" other:filter="modifiedBefore:<;modifiedSince:>,>=,=" />
    

This example would take the query statement

SELECT * 
FROM <table> 
WHERE modifedAt < '<datetime>'

and generate a request that appends the query parameter '&modifiedBefore=<url encoded datetime>' to the url.