docs

Custom SQL query returning 8 rows (hide)

Query parameters

This data as json, CSV

idpagereftitlecontentbreadcrumbsreferences
json_api:column-filter-arguments json_api column-filter-arguments Column filter arguments You can filter the data returned by the table based on column values using a query string argument. ?column__exact=value or ?_column=value Returns rows where the specified column exactly matches the value. ?column__not=value Returns rows where the column does not match the value. ?column__contains=value Rows where the string column contains the specified value ( column like "%value%" in SQL). ?column__endswith=value Rows where the string column ends with the specified value ( column like "%value" in SQL). ?column__startswith=value Rows where the string column starts with the specified value ( column like "value%" in SQL). ?column__gt=value Rows which are greater than the specified value. ?column__gte=value Rows which are greater than or equal to the specified value. ?column__lt=value Rows which are less than the specified value. … ["JSON API", "Table arguments"] []
json_api:expand-foreign-keys json_api expand-foreign-keys Expanding foreign key references Datasette can detect foreign key relationships and resolve those references into labels. The HTML interface does this by default for every detected foreign key column - you can turn that off using ?_labels=off . You can request foreign keys be expanded in JSON using the _labels=on or _label=COLUMN special query string parameters. Here's what an expanded row looks like: [ { "rowid": 1, "TreeID": 141565, "qLegalStatus": { "value": 1, "label": "Permitted Site" }, "qSpecies": { "value": 1, "label": "Myoporum laetum :: Myoporum" }, "qAddress": "501X Baker St", "SiteOrder": 1 } ] The column in the foreign key table that is used for the label can be specified in metadata.json - see Specifying the label column for a table . ["JSON API"] []
json_api:id2 json_api id2 Table arguments The Datasette table view takes a number of special query string arguments. ["JSON API"] []
json_api:json-api-discover-alternate json_api json-api-discover-alternate Discovering the JSON for a page Most of the HTML pages served by Datasette provide a mechanism for discovering their JSON equivalents using the HTML link mechanism. You can find this near the top of the source code of those pages, looking like this: <link rel="alternate" type="application/json+datasette" href="https://latest.datasette.io/fixtures/sortable.json"> The JSON URL is also made available in a Link HTTP header for the page: Link: https://latest.datasette.io/fixtures/sortable.json; rel="alternate"; type="application/json+datasette" ["JSON API"] []
json_api:json-api-pagination json_api json-api-pagination Pagination The default JSON representation includes a "next_url" key which can be used to access the next page of results. If that key is null or missing then it means you have reached the final page of results. Other representations include pagination information in the link HTTP header. That header will look something like this: link: <https://latest.datasette.io/fixtures/sortable.json?_next=d%2Cv>; rel="next" Here is an example Python function built using requests that returns a list of all of the paginated items from one of these API endpoints: def paginate(url): items = [] while url: response = requests.get(url) try: url = response.links.get("next").get("url") except AttributeError: url = None items.extend(response.json()) return items ["JSON API"] [{"href": "https://requests.readthedocs.io/", "label": "requests"}]
json_api:json-api-shapes json_api json-api-shapes Different shapes The default JSON representation of data from a SQLite table or custom query looks like this: { "database": "sf-trees", "table": "qSpecies", "columns": [ "id", "value" ], "rows": [ [ 1, "Myoporum laetum :: Myoporum" ], [ 2, "Metrosideros excelsa :: New Zealand Xmas Tree" ], [ 3, "Pinus radiata :: Monterey Pine" ] ], "truncated": false, "next": "100", "next_url": "http://127.0.0.1:8001/sf-trees-02c8ef1/qSpecies.json?_next=100", "query_ms": 1.9571781158447266 } The columns key lists the columns that are being returned, and the rows key then returns a list of lists, each one representing a row. The order of the values in each row corresponds to the columns. The _shape parameter can be used to access alternative formats for the rows key which may be more convenient for your application. There are three options: ?_shape=arrays - "rows" is the default option, shown above ?_shape=objects - "rows" is a list of JSON key/value objects ?_shape=array - an JSON array of objects ?_shape=array&_nl=on - a newline-separated list of JSON objects ?_shape=arrayfirst - a flat JSON array containing just the first value from each row ?_shape=object - a JSON object keyed using the primary keys of the rows _shape=objects looks like this: { "database": "sf-trees", ... "rows": [ { "id": 1, … ["JSON API"] []
json_api:json-api-special json_api json-api-special Special JSON arguments Every Datasette endpoint that can return JSON also accepts the following query string arguments: ?_shape=SHAPE The shape of the JSON to return, documented above. ?_nl=on When used with ?_shape=array produces newline-delimited JSON objects. ?_json=COLUMN1&_json=COLUMN2 If any of your SQLite columns contain JSON values, you can use one or more _json= parameters to request that those columns be returned as regular JSON. Without this argument those columns will be returned as JSON objects that have been double-encoded into a JSON string value. Compare this query without the argument to this query using the argument ?_json_infinity=on If your data contains infinity or -infinity values, Datasette will replace them with None when returning them as JSON. If you pass _json_infinity=1 Datasette will instead return them as Infinity or -Infinity which is invalid JSON but can be processed by some custom JSON parsers. ?_timelimit=MS Sets a custom time limit for the query in ms. You can use this for optimistic queries where you would like Datasette to give up if the query takes too long, for example if you want to implement autocomplete search but only… ["JSON API"] [{"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight.json?sql=select+%27{%22this+is%22%3A+%22a+json+object%22}%27+as+d&_shape=array", "label": "this query without the argument"}, {"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight.json?sql=select+%27{%22this+is%22%3A+%22a+json+object%22}%27+as+d&_shape=array&_json=d", "label": "this query using the argument"}]
json_api:json-api-table-arguments json_api json-api-table-arguments Special table arguments ?_col=COLUMN1&_col=COLUMN2 List specific columns to display. These will be shown along with any primary keys. ?_nocol=COLUMN1&_nocol=COLUMN2 List specific columns to hide - any column not listed will be displayed. Primary keys cannot be hidden. ?_labels=on/off Expand foreign key references for every possible column. See below. ?_label=COLUMN1&_label=COLUMN2 Expand foreign key references for one or more specified columns. ?_size=1000 or ?_size=max Sets a custom page size. This cannot exceed the max_returned_rows limit passed to datasette serve . Use max to get max_returned_rows . ?_sort=COLUMN Sorts the results by the specified column. ?_sort_desc=COLUMN Sorts the results by the specified column in descending order. ?_search=keywords For SQLite tables that have been configured for full-text search executes a search … ["JSON API", "Table arguments"] [{"href": "https://www.sqlite.org/fts3.html", "label": "full-text search"}, {"href": "https://www.sqlite.org/fts5.html#full_text_query_syntax", "label": "advanced SQLite FTS syntax"}, {"href": "https://latest.datasette.io/fixtures/facetable?_where=_neighborhood%20like%20%22%c%%22&_where=_city_id=3", "label": "facetable?_where=_neighborhood like \"%c%\"&_where=_city_id=3"}, {"href": "https://latest.datasette.io/fixtures/facetable?_where=_city_id%20in%20(select%20id%20from%20facet_cities%20where%20name%20!=%20%22Detroit%22)", "label": "facetable?_where=_city_id in (select id from facet_cities where name != \"Detroit\")"}, {"href": "https://latest.datasette.io/fixtures/roadside_attractions?_through={%22table%22:%22roadside_attraction_characteristics%22,%22column%22:%22characteristic_id%22,%22value%22:%221%22}", "label": "an example"}]