Queries

Overview

Queries are requests for information within Cinchy. You are able to query for any data that you have access to in your platform by combining Cinchy Query Language with the Cinchy Query Builder. There are unlimited use cases for queries, from returning, manipulating, adding, deleting data and more.

Build queries

You can use the Cinchy Query Builder to create and run your queries. The below section uses the following example use case:

• Building a query to return the names of all documentation requests in the [Product].[Product Content Requests] table.

1. From the homepage, select Create > Query (Image 1)

2. Fill out the following information:

The Info Tab

Under the Info tab, you can fill out information on the query if you wish to save it (Image 2):

• Domain: ‌Mandatory. You need to select a Domain your query will reside in. As an admin, you can also create new domains in this screen.
  • Example: [Product]
• Query Name: ‌Mandatory. Must be unique within the Domain.‌
  • Example: "List of Documentation Requests"
• Icon: ‌You can optionally pick a non-default icon, as well as color for your table. This will be displayed in My Network.
• Description: ‌You can give your query a description. This description will be displayed on the home screen to users browsing the marketplace. It will also be searchable.
  • Example: "Retrieve the titles of all documentation requests."
• Query Type (Added in v5.10): Select whether your query is available as a:
  • Virtual Table. This provides a read-only tabular view of the queried data and is useful for sharing reports and queries.
  • API. For when you want your query available programatically.
    • If API is selected, the below fields will also appear. They only affect the metadata:
      • HTTP Method: The method of your API. This will be either GET, POST, PUT, PATCH, or DELETE.
      • Result Format: The results format to push to your API. This will be either JSON, XML, Comma Separated, Tab Separated, or Pipe Separated.
      • Compress JSON: This is defaulted to false. Review Appendix A for more details.
• Return Type: Queries have six different return types:
  • Query Results (Approved Data Only) This is the default return type, it returns a table from a select query with only approved data for tables with Change Approval enabled, or all data for tables without Change Approval. This is generally used for external APIs as you will want to query approved data, rather than drafts.
  • Query Results (Including Draft Data) This return type returns a table from a SELECT query (including draft data) for tables with Change Approval enabled. Use this return type when looking to display results of records that are pending approval.
  • Query Results (Including Version History) This return type returns a table from a SELECT query (including draft data) with historical data for all tables, as seen in the Collaboration Log of any record. This data includes all changes that happened to all records within the scope of the select query.
  • Number of Rows Affected This return type returns a single string response with the number of rows affected if the last statement in the query is an INSERT, UPDATE, or DELETE statement.
  • Execute DDL Script Use this return type when your query contains DDL commands that implement schema changes such as CREATE|ALTER|DROP TABLE, CREATE|ALTER|DROP VIEW, or CREATE|DROP INDEX.
  • Single Value (First Column of First Row) This return type returns a result of 1 row x 1 column, irrespective of the underlying result set.

The Query Tab

In the Query screen, you can create you query using Cinchy Query Language. You can read more about the CQL functions and how to use them here

On the left hand side you have the Object tree, which shows you all the domains, tables, and columns you have access to query within Cinchy. You can search or simply navigate by expanding the domains and tables.

You can drag and drop the columns or table you're looking for into the Query Builder.

In the below example, we have told the Query Builder to select and return the [Title] column from the [Product Content Backlog] table.

Note: "WHERE [Deleted] IS NULL" is included in the query language by default. This makes sure that you aren't returning any deleted records.

Execute queries

To execute your query, click on the triangle "play" button.

Depending on the results type that you selected, you will see your data in the box below the query editor.

Note: For long running queries, you can use the square "stop" button to cancel your task.

Saved Queries and Saved Query Table

A Saved Query, as the name indicates, is a query that is saved in the platform for further reuse either within Cinchy or as an API called by an external system.

Once you are satisfied with your query, you can click "save" to keep a copy. This enables three things:

1. It allows you to access the "Design Controls" tab. Here you can set Design and Execute access on your saved query.
2. You can find your query in the "Saved Queries" table. You can also search the Cinchy homepage for the saved query to execute it (as long as you have the correct permissions).
3. You can access the query directly by either the CinchyID or the domain + name of the Saved Query.
   1. <baseurl>/Query/Execute?queryId=<cinchyid>
   2. <baseurl>/Query/Execute/<domain>/<saved query name>

The Saved Queries table has the following columns of note:

Column Name	Description
Domain	The name of the Domain where you query resides.
Name	The name of your saved query. Must be unique within the domain.
Return Type	The results type you selected for your query. Queries have six different return types: - Query Results (Approved Data Only): This is the default return type, it returns a table from a select query with only approved data for tables with Change Approval enabled, or all data for tables without Change Approval. This is generally used for external APIs as you will want to query approved data, rather than drafts. - Query Results (Including Draft Data): This return type returns a table from a SELECT query (including draft data) for tables with Change Approval enabled. Use this return type when looking to display results of records that are pending approval. - Query Results (Including Version History): This return type returns a table from a SELECT query (including draft data) with historical data for all tables, as seen in the Collaboration Log of any record. This data includes all changes that happened to all records within the scope of the select query. - Number of Rows Affected: This return type returns a single string response with the number of rows affected if the last statement in the query is an INSERT, UPDATE, or DELETE statement. - Execute DDL Script: Use this return type when your query contains DDL commands that implement schema changes such as CREATE
CQL	The expression used to create your query.
Description	A description of what your query does.
Guid	A Globally Unique IDentifier. This is used to differentiate your query at a granular level.
REST API URL	Saved queries in Cinchy are automatically available as REST APIs to allow for external application integration. You can read more about this functionality here.
Query Type	v5.10+ The two values possible for this column are Virtual Table and API, where Virtual Table denotes a query best suited for a read-only tabular view of data, useful for sharing reports and queries, and API is one best suited for programmatic access. This parameter is also configurable in the Saved Query UI.
HTTP Method	v5.10+ The API method associated with the query. This parameter is also configurable in the Saved Query UI. The possible values are - GET - POST - PUT - PATCH - DELETE. Note that: - There is no correlation between, or enforcement of, HTTP methods and the query operations. e.g. a query that has an insert statement and a GET HTTP method will run successfully. - If an Http Method is specified, then the Http method used for the request to the API endpoint needs to match. e.g. If a DELETE request is made to an API endpoint where the Http Method for the corresponding query is set to GET, an error will be returned.
API Version	v5.10+ This column can be used to version groups of endpoints.
UUID	v5.10+ A Universally Unique IDentifier. This can be used to link versions of endpoints together.
Use Legacy URL Structure	v5.10+ The URL structure of an API endpoint in Cinchy v5.10+ has changed. If this column is set to true then your query API endpoint will continue to use the old structure. - Set to true: /API/[Domain]/[Query Name]. - Set to false: /API/v[API Version]/[Domain]/[Query Name]
CompressJSON	v5.10+ JSON compression can: - Help to reduce the amount of time it takes to query and process data. - Reduce the amount of bandwidth needed to transfer data. This can be especially beneficial for applications that require frequent data updates, such as web applications. - Reduce the amount of memory needed to store data. - Set to false: returns JSON API results in a standard format with an array of objects. - Set to true: returns JSON API results with separate arrays for schema and data. This parameter is also configurable in the Saved Query UI. This value can be overridden at the URL level when making requests by adding ?CompressJSON=true or ?CompressJSON=false to the REST API URL.

Constraints:

• v5.10+: you must have a unique HTTP Method + REST API URL field.
• Older version constraint: you must have a unique REST API URL field.

Generate pivot tables

Once you have executed the query, click the Grid drop down list and select Pivot. Here is where you can take your standard table view and slice and dice your data (Image 2).

Generate charts

From within your pivot view, open the drop down list with the value “table” and select the type of chart you want to use to display the data (Image 3).

Build shared visualizations

Once you have a desired visualization, that visualization can be made available for others as an applet in Cinchy. Grab the Pivot URL and send it to your Cinchy builder to create your mini applet that can be shared and leveraged!

To copy the Pivot URL to build have a visualization created, complete the following:

1. From within the Pivot, locate the blue Pivot URL
2. Click Pivot URL button
3. Click the Copy button
4. Send the copied URL to your Cinchy builder to create your applet that can be shared and leveraged!

info

You can also open that visualization by clicking Open in new tab.

Appendix A

Compress JSON

Compressed JSON removes the key:value pair of json's encoding to store keys and values in separate parallel arrays.

For example:

// uncompressed
JSON = {
  data : [
     { field1 : 'data1', field2 : 'data2', field3 : 'data3' },
     { field1 : 'data4', field2 : 'data5', field3 : 'data6' },
     .....
  ]
};
 
//compressed
JSON = {
    data : [ 'data1','data2','data3','data4','data5','data6' ],
    keys : [ 'field1', 'field2', 'field3' ]
};

JSON compression can:

• Help to reduce the amount of time it takes to query and process data
• Reduce the amount of bandwidth needed to transfer data. This can be especially beneficial for applications that require frequent data updates, such as web applications.
• Reduce the amount of memory needed to store data.

Compress JSON is defaulted to false in the Query Builder, and can be changed in the [Saved Queries] table.

The value can also be overridden at the URL level when making requests by adding ?CompressJSON=true or ?CompressJSON=false to the REST API URL.

Example: https://cinchy.net/API/Product/List%20of%20Documentation%20Requests?CompressJSON=true