sections
1 row where breadcrumbs contains "Canned queries" and references = "[{"href": "https://latest.datasette.io/fixtures/neighborhood_search?text=town", "label": "https://latest.datasette.io/fixtures/neighborhood_search?text=town"}]"
This data as json, CSV (advanced)
id ▼ | page | ref | title | content | breadcrumbs | references |
---|---|---|---|---|---|---|
sql_queries:canned-queries-named-parameters | sql_queries | canned-queries-named-parameters | Canned query parameters | Canned queries support named parameters, so if you include those in the SQL you will then be able to enter them using the form fields on the canned query page or by adding them to the URL. This means canned queries can be used to create custom JSON APIs based on a carefully designed SQL statement. Here's an example of a canned query with a named parameter: select neighborhood, facet_cities.name, state from facetable join facet_cities on facetable.city_id = facet_cities.id where neighborhood like '%' || :text || '%' order by neighborhood; In the canned query metadata (here Using YAML for metadata as metadata.yaml ) it looks like this: databases: fixtures: queries: neighborhood_search: sql: |- select neighborhood, facet_cities.name, state from facetable join facet_cities on facetable.city_id = facet_cities.id where neighborhood like '%' || :text || '%' order by neighborhood title: Search neighborhoods Here's the equivalent using JSON (as metadata.json ): { "databases": { "fixtures": { "queries": { "neighborhood_search": { "sql": "select neighborhood, facet_cities.name, state\nfrom facetable\n join facet_cities on facetable.city_id = facet_cities.id\nwhere neighborhood like '%' || :text || '%'\norder by neighborhood", "title": "Search neighborhoods" } } } } } Note that we are using SQLite string concatenation here - the || operator - to add wildcard % characters to the string provided by the user. You can try this canned query out here: https://latest.datasette.io/fixtures/neighborhood_search?text=town In this example the :text named parameter is automatically extracted from the query using a regular expression. … | ["Running SQL queries", "Canned queries"] | [{"href": "https://latest.datasette.io/fixtures/neighborhood_search?text=town", "label": "https://latest.datasette.io/fixtures/neighborhood_search?text=town"}] |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [sections] ( [id] TEXT PRIMARY KEY, [page] TEXT, [ref] TEXT, [title] TEXT, [content] TEXT, [breadcrumbs] TEXT, [references] TEXT );