Skip to content

Advanced Data Navigation

SlashDB allows for even more control in viewing databases using additional query string parameters.

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 resources, list of arrays, vectors.

Examples:

/Customer?sort=Country - sort Customers by Country ascending

/Customer?sort=-Country - sort Customers by Country descending (minus before column name)

/Customer?sort=-Country,LastName - sort Customers by Country descending and LastName ascending

You may define by which columns and direction (ascending or descending) you want the data to be sorted.

Note that 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 - show only unique countries

limit and offset

Query string parameters 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.

Note that it is highly 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 resources, list of arrays, vectors

Examples:

/Invoice.html?limit=10 - show only first 10 Invoices

/Invoice.html?offset=2 - skip first 2 Invoices and show all the rest

/Invoice.html?limit=10&offset=2 - show 10 Invoices but skip first 2

/Invoice.html?limit=10&offset=2&sort=InvoiceId - show 10 Invoices 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 query string parameter.

Usage:

?nullStr=[placeholder]

Value type: string

Default: <null>

Applicable to: list of resources, 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

This feature comes handy when requesting large amounts of data.
When query string parameter stream is set True, SlashDB utilizes streaming data transfer mechanism of HTTP (Chunked Transfer Encoding). This allows to reduce amount of memory used while creating output and TTFB.
Header Transfer-Encoding: chunked is added to the response.
At this point streaming is supported only for "flat" (no depth or depth=0).
Also HTML format (GUI) does not support streaming.

Usage:

?stream=[true_or_false]

Value type: boolean (True or False)

Default: False

Applicable to: list of resources, list of arrays, vectors, single resource, single array, scalar

Examples:

/InvoiceLine.json?stream=True - stream all InvoiceLines (list of approx. 2240 objects)

depth

This feature is useful if you want to acquire objects together with all related data in single request. The value number indicates how many subsequent relations must be included (nested) in result.

Use with caution as high depth may return large amount of data or even all data in your database, resulting in slow response and possible timeouts.

Usage:

?depth=[depth_level]

Value type: integer

Default: 0 (a flat structure that contains object attributes)

Applicable to: list of resources, single resource

Examples:

/Customer/CustomerId/18.json - show only Customer object details (default depth)

/Customer/CustomerId/18.json?depth=1 - show Customer object with her Invoices and Employees

transpose

This feature converts tabular resources from column oriented to row oriented.

Data in form of list of resources or list of arrays may be transposed in a way that columns become rows and rows become columns. That feature is useful when client application requires series of data. The feature is controlled with presence of query string ?transpose and by assigning true or false value.

Regular (not transposed) 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 resources or arrays.

Examples:

/Employee.json - not transposed resource list
/Employee.json?transpose=false - not transposed resource list
/Employee.json?transpose - transposed resource list
/Employee.json?transpose=true - transposed resource list

wantarray

This feature makes output always as a list.

SlashDB is a Resource Oriented Architecture. When making request to a canonical URL (contains only filtering on Primary Keys, which explicitly identifies a single resource) SlashDB does not pack returned object into form of a list. With query string wantarray=True you may force to put that object into a list anyway.

In case of canonical URLs, if the resource has not been found, we would get an 404, but in wantarray mode, we'll get an empty array [].

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, output not wrapped in a list

/Customer/CustomerId/18.json?wantarray=True - canonical URL, output wrapped in a list

/Customer/CustomerId/18/Country/USA.json - although URL contains filtering on Primary Key it's not canonical because it contains other filter (Country = USA)

/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 will cause a schema document to be returned.

  • Can be streamed using stream query string parameter
  • Can be combined with the depth query string parameter to return nested schemas (stream not supported in this case)
  • The href query string parameter can be provided to include/exclude the href attribute in the schema document (JSON and XML)
  • JSON format supports combining this with the wantarray query string parameter to return an array when the request is made for a URL filtering on a primary key.
  • Can be used with the cardinality query string parameter (JSON and XML)
  • CSV format supports combining this with the header query string parameter to include/exclude the schema document CSV header.

See Schemas for more information.

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 query string parameter, there is a special cardinality query string parameter which sets certain properties/attributes for JSON & XML schema documents.

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 we require a schema for individual an resource, such as for canonical URLs with a primary key column only.

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 resources, 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 query string parameter wildcard allows to change default wildcard character.

By default, SlashDB allows filtering by substring using * (asterisk) character e.g. /Customer/LastName/S* - customers with last name starting with letter "S".

In case you want to filter literally by asterisk character you must tell SlashDB to use different character for wildcard e.g. wildcard=$ or a sequence of characters e.g. wildcard=$$.

Characters that have special meaning in URL e.g. /, ?, #, &, +, ;, % shouldn't be used in wildcard.

Usage:

?wildcard=[string_representing_wildcard]

Value type: string

Default: * (asterisk character)

Applicable to: list of resources, list of arrays, vectors

Examples:

/Customer/Company/* Inc. - find all Customers whose company ends with "Inc."

/Customer/Company/$ Inc.?wildcard=$ - find all Customers whose company ends with "Inc." using different wildcard

separator

Allows to change default character , (comma) which is used when filtering for multiple values. Useful when one of the values contains a comma.

Usage:

?separator=[string_representing_separator]

Value type: string

Default: , (comma character)

Applicable to: list of resources, list of arrays, vectors

Examples:

/Invoice/InvoiceId/1,2,3 - get invoices 1, 2 and 3.

/Invoice/InvoiceId/1$2$3?separator=$ - same as above but using $ as a separator of values

/Invoice/BillingAddress/11, Place Bellecour.html?separator=$ - show Invoices that have BillingAddress "11, Place Bellecour" (contains a comma).

validate

Allows to change the default behaviour for payload validation in post and put requests.

Usage:

?validate=[true_or_false]

Value type: boolean (True or False)

Default: True

Applicable to: POST and PUT requests.

INI settings

json_sdb.validate_payload = [true_or_false]
csv_sdb.validate_payload = [true_or_false]
xml_sdb.validate_payload = [true_or_false]

url string substitution (deprecated)

This feature is deprecated, urlencoding should be used instead.

This feature allows to represent string using another string.

There are rare situations when value in filtering is part of URL syntax. For example "/" character normally would be interpreted as a path separator and couldn't be correctly used for filter value.

By default SlashDB will interpret "__" (double underscore character) as "/" (slash character).

Usage:

?[placeholder]=[meaning_of_the_placeholder]

Value type: string

Default: __=/ (double underscore character is interpreted as forward slash)

Applicable to: list of resources, list of arrays, vector, single resource, single array, scalar

Examples:

/Chinook/Artist/Name/AC__DC - double underscore is treated as forward slash
/Chinook/Artist/Name/AC_DC?_=/ - single underscore is treated as forward slash