Modifying Output
SlashDB allows you to control your view of a database resource with various URL query string parameters (modifiers) that you can attach to the end of a resource URL. Some of these parameters (e.g. sort, distinct) are analogous to SQL keywords for transforming data output. Others control how the SlashDB engine will render output or retrieve metadata for a resource (e.g. stream, schema)
sort
This feature enables sorting list of records.
Usage:
?sort=[column_name]
?sort=-[column_name]
?sort=[column_name_A],[column_name_B]
Value type: string
Default: depends on database collation
Applicable to: List of objects, list of arrays, vectors.
Examples:
/Customer?sort=Country - sort Customer by Country ascending
/Customer?sort=-Country - sort Customer by Country descending (hyphen before column name)
/Customer?sort=-Country,LastName - sort Customer by Country descending and LastName ascending
You may define by which columns and direction (ascending or descending) you want the data to be sorted.
Info
Sorting is executed by the database engine using the ORDER BY statement; sorting results on different database types may return slightly different results for the same dataset. This particularly applies to sorting NULL and empty string ('') values.
For example, when sorting a string column in ascending order, MySQL/MSSQL will first place NULL values, then empty values, and finally order the rest of the records alphabetically. PostgreSQL will place empty values first, then order records alphabetically, and NULL values will be placed at the end of the record set.
See your database documentation for details about its sorting implementation.
distinct
This feature removes duplicate entries in tables, arrays and vectors.
Usage:
?distinct
Value type: (no value)
Default: (not used)
Applicable to: list of arrays, vectors
Examples:
/Customer/Country.html?distinct - return only unique Country records
limit and offset
URL query string parameters (modifiers) limit and offset allow you to limit the number of returned records, or skip over records retrieved from a result set. This feature makes it possible to implement a pagination mechanism.
Tip
It is recommended to use these query parameters with the sort parameter to ensure that records are returned in a consistent order.
Usage:
?limit=[number_of_rows_to_acquire]
?offset=[number_of_rows_to_skip]
?limit=[number_of_rows_to_acquire]&offset=[number_of_rows_to_skip]
?limit=[number_of_rows_to_acquire]&offset=[number_of_rows_to_skip]&sort=[column_name]
Value type: integer
Default: no limit and no offset
Applicable to: List of objects, list of arrays, vectors
Examples:
/Invoice.html?limit=10 - return only first 10 Invoice records
/Invoice.html?offset=2 - skip first 2 Invoice records and return all the rest
/Invoice.html?limit=10&offset=2 - return 10 Invoice records but skip first 2
/Invoice.html?limit=10&offset=2&sort=InvoiceId - return 10 Invoice records but skip first 2, sorted by InvoiceId
nullStr
Set a custom placeholder value for NULL values. By default, when passing NULL values in URLs, the placeholder <null> is used. If you want to define your own placeholder, use the nullStr URL query string parameter (modifier).
Usage:
?nullStr=[placeholder]
Value type: string
Default: <null>
Applicable to: List of objects, list of arrays, vectors
Examples:
/Customer/Company/$null.html?nullStr=$null - retrieve all Customer records where the Company is NULL, using $null as the placeholder for NULL.
stream
Tip
This feature is useful when requesting large amounts of data. It speeds up response time and reduces server memory usage.
When the URL query string parameter (modifier) stream is set to true, SlashDB utilizes the streaming data transfer mechanism of the HTTP protocol (Chunked Transfer Encoding). This reduces the amount of memory used on the server while sending output, and the time to first byte of the response.
The HTTP header Transfer-Encoding: chunked is added to the response.
Info
Streaming is currently supported for flat records only (no depth) Responses in HTML format do not support streaming.
Usage:
?stream=[true_or_false]
Value type: boolean (true or false)
Default: false
Applicable to: List of objects, list of arrays, vectors, single resource, single array, scalar
Examples:
/InvoiceLine.json?stream=true - stream all InvoiceLine records (list of approx. 2240 objects)
depth
This feature is useful if you want to retrieve records together with related records in a single request. The value specifies how many levels of relations to return in the response.
Relations are found by identifying foreign keys in the parent record, and then following those relations to retrieve their respective tables.
Caution
A high depth value may return large amounts of data from the database, resulting in slow responses and possible timeouts. See the
depth_limit INI parameter.
Usage:
?depth=[depth_level]
Value type: integer
Default: 0 (no depth)
Applicable to: List of objects, single resource
Examples:
/Customer/CustomerId/18.json - return a single Customer record (no depth)
/Customer/CustomerId/18.json?depth=1 - return Customer record with related Invoice and Employee records (depth=1)
transpose
A list of objects or an array may be transposed so that columns become rows, and rows become columns. transpose may be true or false.
Tip
This feature is useful for applications that require series of data.
Regular output:
[
{
"InvoiceDate": "2009-01-01T00:00:00",
"BillingCountry": "Germany",
"Total": 1.98
},
{
"InvoiceDate": "2009-01-02T00:00:00",
"BillingCountry": "Norway",
"Total": 3.96
},
{
"InvoiceDate": "2009-01-03T00:00:00",
"BillingCountry": "Belgium",
"Total": 5.94
}
]
Transposed:
{
"InvoiceDate": [
"2009-01-01T00:00:00",
"2009-01-02T00:00:00",
"2009-01-03T00:00:00"
],
"BillingCountry": [
"Germany",
"Norway",
"Belgium"
],
"Total": [
1.98,
3.96,
5.94
]
}
Usage:
?transpose
?transpose=true
?transpose=false
Value type: boolean (true or false) or no value
Default: false
Applicable to: CSV, JSON formats. List of objects or arrays.
Examples:
/Employee.json - regular list of objects
/Employee.json?transpose=false - regular list of objects
/Employee.json?transpose - transposed list of objects
/Employee.json?transpose=true - transposed list of objects
wantarray
This feature forces objects to be wrapped in a list.
When making request to a canonical URL (a URL containing a filter on only the primary key, which explicitly identifies a single record) SlashDB will return the record as an object. The modifier wantarray=true will change the output so that the object is inside a list.
Under normal circumstances, if a resource is not found, a 404 error is returned. When using wantarray, an empty array will be returned.
Usage:
?wantarray=[true_or_false]
Value type: boolean (true or false)
Default: false
Applicable to: single resource, single array, scalar
Examples:
/Customer/CustomerId/18.json - canonical URL, object not wrapped in a list
/Customer/CustomerId/18.json?wantarray=true - canonical URL, output wrapped in a list
/Customer/CustomerId/18/Country.json?wantarray=true - wantarray applied on a scalar
/Customer/CustomerId/18/Country,City.json?wantarray=true - wantarray applied on an array
schema
Returns a schema document for the requested resource. Supported formats are JSON, XML, and CSV. Schemas are returned in JSON Schema, XSD, and CSV formats, respectively. This parameter does not take a value; its presence in the query string return a schema document.
Important
See Schemas for more information.
- Can be streamed using stream
- Can be combined with depth to return nested schemas (stream not supported in this case)
- The href URL query string parameter (modifier) can be provided to include/exclude the href attribute in the schema document (JSON and XML)
- JSON format supports combining this with wantarray to return an array when the request is made for a URL filtering on a primary key.
- Can be used with cardinality (JSON and XML)
- CSV format supports combining this with headers to include/exclude the schema document CSV header.
Usage:
?schema
Value type: N/A
Default: N/A
Applicable to: JSON, XML, CSV formats. Resources, vectors, arrays, scalars.
Examples:
/Invoice/InvoiceId/1.json?schema - return JSON schema for Invoice resource with InvoiceId = 1
/Invoice/InvoiceId/1.json?schema&depth=1 - return JSON schema for Invoice resource with InvoiceId = 1 with related resource schemas included in the response
/Invoice.xml?schema - return XML schema for Invoice resource
/Invoice.csv?schema - return CSV schema for Invoice resource
cardinality
When using the schema URL query string parameter (modifier), there is a special cardinality URL query string parameter (modifier) which sets certain properties/attributes for JSON & XML schema documents.
Info
For JSON documents, the maxItems property will be set to the value given for cardinality when retrieving multiple resources (or if using the wantarray parameter). Otherwise, the maxItems property will not be included in the document. This value should be a positive integer.
For XML documents, the maxOccurs attribute will be set to the value given for cardinality when retrieving a resource.
Otherwise, maxOccurs will be set to the value unbounded.
This value can be a string or a positive integer.
cardinality=1 is useful when a schema is needed for an individual resource.
Usage:
?cardinality=[value]
Value type:
- integer greater than 0 (JSON and XML)
- string "n" (XML only)
- string "unbounded" (XML only)
Default: unbounded (XML) / not present (JSON)
Applicable to: List of objects, list of arrays, vectors
Examples:
/Invoice.json?schema&cardinality=5 - JSON schema document contains maxItems: 5 property. When validated, the validator will ensure that there are no more than 5 instances of the Invoice object in the JSON document.
/Invoice.xml?schema&cardinality=5 - XSD contains maxOccurs="5" attribute. When validated, the validator will ensure that there are no more than 5 instances of the Invoice element in the XML document.
wildcard
The URL query string parameter (modifier) wildcard allows you to change the default wildcard character.
By default, SlashDB allows filtering by substring using * (asterisk) character e.g. /Customer/LastName/S* - return Customer records with LastName starting with letter "S".
If you need to include the asterisk character as a value in a filter, you can use the wildcard modifier, e.g. wildcard=$, or a sequence of
characters, e.g. wildcard=$$.
Caution
Characters that have special meaning in URL e.g. /, ?, #, &, +, ;, %
shouldn't be used as wildcards.
Usage:
?wildcard=[string_representing_wildcard]
Value type: string
Default: * (asterisk)
Applicable to: List of objects, list of arrays, vectors
Examples:
/Customer/Company/* Inc. - return Customer records whose Company ends with "Inc."
/Customer/Company/$ Inc.?wildcard=$ - return Customer records whose Company ends with "Inc." using $ as a wildcard
separator
Allows you to change the default character , (comma) used for filtering multiple values. Used for filtering
with values that contains commas.
Usage:
?separator=[string_representing_separator]
Value type: string
Default: , (comma)
Applicable to: List of objects, list of arrays, vectors
Examples:
/Invoice/InvoiceId/1,2,3 - get Invoice records 1, 2 and 3.
/Invoice/InvoiceId/1$2$3?separator=$ - same as above but using $ as the separator
/Invoice/BillingAddress/11, Place Bellecour.html?separator=$ - get Invoice records that have BillingAddress = "11, Place Bellecour".
validate
Changes the default behaviour for payload validation in POST and PUT requests.
Info
Payloads are validated by default.
Usage:
?validate=[true_or_false]
Value type: boolean (true or false)
Default: true
Applicable to: POST and PUT requests.
INI settings