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

/Customer/CustomerId/18.json?wantarray=True - canocial 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

---

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).

---

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

---