You can search tables and views to find the data you need quickly. Views are especially useful for finding data that may be spread across multiple folders or projects. The Synapse UI offers two ways to search tables and views, or can search from the programmatic clients.
Searching Tables and Views via the Synapse UI
Using Simple Search
On the web, simple search mode displays radio buttons or sliders to the left of a table or view. These are facets that you can use to search the data. Each facet corresponds to a column in the table or view.
If the simple search is not visible on a table or view, then no facets were specified by the table or view author. In this case, use the advanced search described below to query for data.
To use simple search facets, navigate to a table or view. Select the features you are interested in to filter the results. Note that the slider for range in simple search is inclusive, meaning the smallest and largest numbers in the selected range will be included in your search results.
Setting Up Simple Search
If you are a table or view owner, you can choose what facets appear in a simple search menu. To select the facets, click on the Tools menu and select Show View Schema or Show Table Schema. Click on the Edit Schema button to view the Edit Columns window. You can choose what facets appear by selecting Values or Range from the dropdown menus under the Facet option. Values can be thought of as categories whereas Range is a date or number. Selecting a blank field will remove the facet from the search window.
Note: If you change the Column Type in the schema, you must set its facet selection again.
Using Advanced Search Queries
The data within tables and views can also be retrieved by using a SQL-like query language from the Synapse web interface. To query a table or view, select the funnel icon in the upper righthand menu to reveal the advanced search bar.
By default, the advanced search bar will be pre-populated with a query to capture all of the data contained in the table or view. For example:
SELECT * FROM syn3079449
You may specify columns explicitly after the SELECT statement to subset the data:
SELECT age, gender FROM syn3079449
To keep all columns in the query and filter rows that meet a certain condition, incorporate a WHERE statement:
SELECT * FROM syn3079449 WHERE age > 50
Add an ORDER BY statement and a column name to sort the results by that column. The ASC statement sorts the data returned in ascending order.
SELECT * FROM syn3079449 WHERE age > 50 ORDER BY "treatmentArm" ASC
COUNT, SELECT AS, and GROUP_CONCAT SQL statements are also supported. To count the number of rows:
SELECT count(*) FROM syn3079449
Select and rename a subset of columns:
SELECT age AS "Age at Diagnosis", gender AS "Gender" FROM syn3079449
Group rows by name and identify distinct differences:
SELECT count(distinct(treatmentArm)) AS "Number of Treatments", gender FROM syn3079449 group by gender
To list out the distinct treatment arms that were studied, by gender:
SELECT GROUP_CONCAT(distinct(treatmentArm) SEPARATOR ', ') AS "Available Treatments", gender as "By Gender" FROM syn3079449 group by gender
See the REST API docs for a list of all queries that can be performed.
Toggling Between Simple and Advanced Search
You can toggle from the simple search to the advanced search without losing your query results. For example, if you selected treatment arm A, age of 23:64, and gender as female, the query will be preserved in the advanced search bar. However, this feature is unidirectional because the advanced search allows for parameters that are not available with facets. Therefore, switching from advanced to simple search will result in resetting the search query. Synapse will warn you before your search is reset.
Warning: When toggling back to simple search, the query will be reset.
Searching Tables and Views Programatically
Tables and views can also be queried directly from the programmatic clients, which accept all of the SQL-like language used above. For example, to query for the contents of syn12345678:
Command Line
synapse query 'SELECT * FROM syn12345678'
Python
query = syn.tableQuery('SELECT * FROM syn12345678')
R
query <- synTableQuery('SELECT * FROM syn12345678')