restSQL: SQL Resource GET Query

Method	GET
Path	/res/{resName}?{name}={value}
Query Params	Zero or more name-value pairs. Use column labels, not names, as defined in the SQL Resource query. Value encoding: Use equals or not equals value. See Equals/Not Equals Operators for more detail. Include '%' anywhere in the value for the wildcard, which is URL encoded as `%25`. This will use the a SQL LIKE instead of equals operator. See Like Operator for more detail. Use null or not null. See Null/Not Null Operators for more detail. Surround a commad-separated list in parenthesis. See In Operator for more detail. Prefix one of four url encoded comparison operators at the beginning of the value. See Comparison Operators for more detail. Special parameters: `_limit`={integer}, `_offset`={integer}, `_output`={media-type}
Request body	None
Response body	Results as XML	Response.xsd	Content-Type: application/xml or application/json

Description

Returns rows matching query parameters. A no-parameter request returns all rows. Empty readResponse document is returned if the no rows match criteria.

Results are ordered by the primary key(s), in ascending order. In a hierarchical query, the children are ordered within the parent by the child table's primary key(s).

Queries on hierarchical resources with _limit parameters are constrained by the total row count of all the children plus any parents that are childless. For example, supposing a query is limited to 100. If there are thousands of parents, but the first parent (PK order) has no children, the second has 44 children, and the third 130 children, then the results will be the first parent with 0 children, the second parent with 44 children and the third parent with 55 children. The limit is imposed on the SQL query that returns results in a flat, denormalized manner and then restSQL transforms this into a hiearchical representation.

XML Examples

GET /restsql/res/Actor?first_name=JULIANNE&last_name=DENCH HTTP/1.1
Accept: application/xml

HTTP/1.1 200 OK
Content-Type: application/xml

<readResponse>
   <actor actor_id="123" first_name="JULIANNE" last_name="DENCH" />
</readResponse>

A wildcarded query uses a % symbol, as in SQL, which matches any number of characters. It must be URL-encoded to %25 in a URL.

GET /restsql/res/Actor?last_name=S%25 HTTP/1.1
Accept: application/xml

HTTP/1.1 200 OK
Content-Type: application/xml

<readResponse>
   <actor actor_id="180" first_name="JEFF" last_name="SILVERSTONE" />
   <actor actor_id="195" first_name="JAYNE" last_name="SILVERSTONE" />
   <actor actor_id="78" first_name="GROUCHO" last_name="SINATRA" />
   <actor actor_id="31" first_name="SISSY" last_name="SOBIESKI" />
   <actor actor_id="44" first_name="NICK" last_name="STALLONE" />
   <actor actor_id="24" first_name="CAMERON" last_name="STREEP" />
   <actor actor_id="116" first_name="DAN" last_name="STREEP" />
   <actor actor_id="192" first_name="JOHN" last_name="SUVARI" />
   <actor actor_id="9" first_name="JOE" last_name="SWANK" />
</readResponse>

A limited query:

GET /restsql/res/Actor?_limit=10&_offset=0 HTTP/1.1
Accept: application/xml

HTTP/1.1 200 OK
Content-Type: application/xml

<readResponse>
   <actor actor_id="1" first_name="PENELOPE" last_name="GUINESS" />
   <actor actor_id="2" first_name="NICK" last_name="WAHLBERG" />
   <actor actor_id="3" first_name="ED" last_name="CHASE" />
   <actor actor_id="4" first_name="JENNIFER" last_name="DAVIS" />
   <actor actor_id="5" first_name="JOHNNY" last_name="LOLLOBRIGIDA" />
   <actor actor_id="6" first_name="BETTE" last_name="NICHOLSON" />
   <actor actor_id="7" first_name="GRACE" last_name="MOSTEL" />
   <actor actor_id="8" first_name="MATTHEW" last_name="JOHANSSON" />
   <actor actor_id="9" first_name="JOE" last_name="SWANK" />
   <actor actor_id="10" first_name="CHRISTIAN" last_name="GABLE" />
</readResponse>

A hierarchical SQL resource:

GET /restsql/res/ActorFilm?first_name=JULIANNE&last_name=DENCH HTTP/1.1
Accept: application/xml

HTTP/1.1 200 OK
Content-Type: application/xml

<readResponse>
   <actor actor_id="123" first_name="JULIANNE" last_name="DENCH">
      <film year="2600" title="ADAPTATION HOLES" film_id="3" />
      <film year="2600" title="ATLANTIS CAUSE" film_id="43" />
      <film year="2600" title="BERETS AGENT" film_id="67" />
      <film year="2600" title="BULL SHAWSHANK" film_id="105" />
      <film year="2600" title="CHOCOLATE DUCK" film_id="148" />
   </actor>
</readResponse>

A query with comparison operators. This one finds find actors with surname > 'WILLIAMS'.

GET /restsql/res/Actor?surname=%3EWILLIAMS HTTP/1.1
Accept: application/xml

HTTP/1.1 200 OK
Content-Type: application/xml

<readResponse>
	<actor id="147" first_name="FAY" surname="WINSLET" />
	<actor id="156" first_name="FAY" surname="WOOD" />
	<actor id="164" first_name="HUMPHREY" surname="WILLIS" />
	<actor id="168" first_name="WILL" surname="WILSON" />
	<actor id="186" first_name="JULIA" surname="ZELLWEGER" />
</readResponse>

JSON Examples

GET /restsql/res/Actor?first_name=JULIANNE&last_name=DENCH HTTP/1.1
Accept: application/json

HTTP/1.1 200 OK
Content-Type: application/json

{ "actors": [
      { "actor_id": 123, "first_name": "JULIANNE", "surname": "DENCH" }
   ]
}

A wildcarded query uses a % symbol, as in SQL, which matches any number of characters. It must be URL-encoded to %25 in a URL.

GET /restsql/res/Actor?last_name=S%25 HTTP/1.1
Accept: application/json

HTTP/1.1 200 OK
Content-Type: application/json

{ "actors": [
      { "actor_id": 180, "first_name": "JEFF", "surname": "SILVERSTONE" },
      { "actor_id": 195, "first_name": "JAYNE", "surname": "SILVERSTONE" }
      { "actor_id": 78, "first_name": "GROUCHO", "surname": "SINATRA" },
      { "actor_id": 31, "first_name": "SISSY", "surname": "SOBIESKI" },
      { "actor_id": 44, "first_name": "NICK", "surname": "STALLONE" },
      { "actor_id": 24, "first_name": "CAMERON", "surname": "STREEP" },
      { "actor_id": 116, "first_name": "DAN", "surname": "STREEP" },
      { "actor_id": 192, "first_name": "JOHN", "surname": "SUVARI" },
      { "actor_id": 9, "first_name": "JOE", "surname": "SWANK" },
   ]
}

Find acgtors with surnames that are not null and limit the response to ten rows

GET /restsql/res/Actor?surname=!null&_limit=10&_offset=0 HTTP/1.1
Accept: application/json

HTTP/1.1 200 OK
Content-Type: application/json

{ "actors": [
      { "actor_id": 1, "first_name": "PENELOPE", "surname": "GUINESS" },
      { "actor_id": 2, "first_name": "NICK", "surname": "WAHLBERG" },
      { "actor_id": 3, "first_name": "ED", "surname": "CHASE" },
      { "actor_id": 4, "first_name": "JENNIFER", "surname": "DAVIS" },
      { "actor_id": 5, "first_name": "JOHNNY", "surname": "LOLLOBRIGIDA" },
      { "actor_id": 6, "first_name": "BETTE", "surname": "NICHOLSON" },
      { "actor_id": 7, "first_name": "GRACE", "surname": "MOSTEL" },
      { "actor_id": 8, "first_name": "MATTHEW", "surname": "JOHANSSON" },
      { "actor_id": 9, "first_name": "JOE", "surname": "SWANK" },
      { "actor_id": 10, "first_name": "CHRISTIAN", "surname": "GABLE" }
   ]
}

A hierarchical SQL resource:

GET /restsql/res/ActorFilm?first_name=JULIANNE&last_name=DENCH HTTP/1.1
Accept: application/json

HTTP/1.1 200 OK
Content-Type: application/json

{ "actors": [
      { "actor_id": 123, "first_name": "JULIANNE", "surname": "DENCH" },
         "films": [
            { "year": 2006, "title": "ADAPTATION HOLES", "film_id": 3 },
            { "year": 2006, "title": "ATLANTIS CAUSE", "film_id": 43 },
            { "year": 2006, "title": "BERETS AGENT", "film_id": 67 },
            { "year": 2006, "title": "BULL SHAWSHANK", "film_id": 105 },
            { "year": 2006, "title": "CHOCOLATE DUCK", "film_id": 138 },
         ]
      }
   ]
}

A query with comparison operators. This one finds find actors with surname > 'WILLIAMS'.

GET /restsql/res/Actor?surname=%3EWILLIAMS HTTP/1.1
Accept: application/json

HTTP/1.1 200 OK
Content-Type: application/json

{ "actors": [
      { "actor_id": 147, "first_name": "FAY", "surname": "WINSLET" },
      { "actor_id": 156, "first_name": "FAY", "surname": "WOOD" },
      { "actor_id": 164, "first_name": "HUMPHREY", "surname": "WILLIS" },
      { "actor_id": 168, "first_name": "WILL", "surname": "WILSON" },
      { "actor_id": 186, "first_name": "JULIA", "surname": "ZELLWEGER" }
   ]
}

Without an Accept header, the output format defaults to application/xml. An alternative to the Accept header is using the _output query parameter with the mime type, as in:

GET /restsql/res/ActorFilm/123?_output=application/json HTTP/1.1

will return JSON output. See Media Types for more discussion.