Showpad Query Language

Showpad Query Language (ShowQL) is a powerful and user-friendly query language. It enables you to retrieve the exact content you need from the Showpad Library, with fine-grained filtering abilities. It's designed to help you effortlessly discover the exact assets you need within your Showpad content Library, while using a human-readable syntax.

Whether you're an experienced developer or new to Showpad, the following sections will guide you step by step through the process of effectively using ShowQL to meet your content search and filtering needs.

There are a few essential points to consider when working with ShowQL:

• When referring to other objects, use their object IDs. For example, when querying divisions, users, or tags, use the respective division, user, or tag ID. Querying based on names is not supported.

• Archived assets are included by default. If you want to exclude them, you need to explicitly do so in your query.

• Personal assets are included by default in your results.

Prerequisites

• Showpad Enterprise package
• Showpad Administrator or promoted user account

Example Use Cases

ShowQL covers a wide range of use cases, providing great flexibility and control over asset retrieval. Here are some examples of how you can leverage ShowQL in your applications:

• Asset Expiration Warnings: It can be quite useful to know which assets will be expiring soon. In the this query, we look for assets expiring within the next week:

  expiresAt < 1w AND expiresAt > 0d

  The query filters assets based on their expiration date, ensuring that you can take action before they expire.

• Checking Empty Language and Country Fields: You can easily see if your Showpad Library is up to date by checking that your assets have the necessary language and country metadata. The following query lets you identify assets that are missing this information:

  (languages IS EMPTY OR countries IS EMPTY) AND (archivedAt IS EMPTY)

  The query first searches for assets missing language or country information (the statement in parenthesis). Once it has that information, it then checks that those assets are not archived. You'll learn more about this kind of query in the combine criteria and operator precedence sections.

Base Endpoint

The base endpoint for ShowQL calls is:

https://{{subdomain}}.api.showpad.com

Every request must be prefixed with the base endpoint. For example, this is a ShowQL query looking for a specific division:

curl --request POST \
  --url https://{{subdomain}}.api.showpad.com/v4/assets/query \
  --header 'Authorization: Bearer MyApiToken' \
  --header 'Content-Type: application/json' \
  --data '{ "query": "division = \"4643adcc1621814e91c489b34bb428ed\"" }'

Query Concepts

To create queries using ShowQL, it's important to understand the construction and logic of queries. This will ensure that you're able to use ShowQL more effectively in locating and filtering assets within your Showpad content Library.

Simple Queries

A simple query consists of:

Identifier	Comparison Operator	Value
This specifies where the search will be performed. These are discussed in more detail in the Identifiers section.	This defines how the identifier and the value are evaluated. These are discussed in more detail in the Supported Operators section.	This is what your query is searching for. The value searched for must be the same data type as the identifier. These are discussed in more detail in the Data Types section.

Example

This query will return all assets in the division with ID: 4643adcc1621814e91c489b34bb428ed.

division = "4643adcc1621814e91c489b34bb428ed"

As you can see, this query has the required elements:

Identifier	Comparison Operator	Value
division	=	"4643adcc1621814e91c489b34bb428ed"

Combine Criteria

The simple queries example showed a query using one statement. You can also combine multiple statements in a single query. These queries combine the statements using logical OR and AND operators.

AND	OR
True - The query returns data which matches both statements. False - Data which doesn't match one or both statements is not returned.	True - The query returns data which matches at least one of the statements. False - Data which doesn't match at least one of the statements is not returned.

Example

This query will return all shareable assets in the division with ID: 4643adcc1621814e91c489b34bb428ed.

division = "4643adcc1621814e91c489b34bb428ed" AND
isShareable = true

Even though there are multiple criteria, this query has the required elements, with an additional condition:

Identifier	Comparison Operator	Value	Logical Operator
division	=	"4643adcc1621814e91c489b34bb428ed"	AND
isShareable	=	true

Exclude Criteria

Not only can you combine criteria, you can also exclude them. This is done via a logical NOT operator.

NOT
True - The query returns only the results that do not include the excluded criteria. False - Data that doesn't exclude the criteria is not returned.

Example

This query will return all assets that are not in the division with ID: 4643adcc1621814e91c489b34bb428ed.

NOT (division = "4643adcc1621814e91c489b34bb428ed")

Operator Precedence

The more operators you use, it becomes necessary to specify the order in which they're used. This is where precedence comes in. You can use parentheses to set the precedence of your query criteria to group conditions. The logic within the parenthese is given higher precedence and evaluated before any other criteria.

Example

This query will first look for shareable assets within the specified division, then it will determine if the division is shared.

(division = "4643adcc1621814e91c489b34bb428ed" AND isShareable = true) OR
isDivisionShared = true

Case Sensitivity

ShowQL is case-insensitive. This means that identifiers and operators can be provided in mixed casing. In other words, an "a" is treated the same as an "A". This flexibility allows you to create queries without worrying about correct letter casing.

caution

While identifiers are not case-sensitive, values are case-sensitive.

Data Types

Type	Description	Examples
string	Enclose string values in double quotes, special characters are escaped using a "" character in front of them.	"my-value" "I said "Hello""
number	Optionally signed whole numbers are supported.	5 +5 -5
boolean	Boolean values can be either true or false.	true or TRUE false or FALSE
datetime	ShowQL supports both absolute and relative datetime formats: Absolute datetime The values are expressed in UTC. Date components are all mandatory. All time components are optional. If they are missing, by default they are set to 0 (e.g., "2022-03-23" is the same as "2022-03-23 00:00:00"). When specifying time components, the final "Z" is optional. Enclose these in double quotes. Supported formats include: "YYYY-MM-DD" "YYYY-MM-DD hh:mm" "YYYY-MM-DD hh:mm:ss" "YYYY-MM-DDThh:mm:ssZ" Relative datetime Use these to describe a moment in time relative to the current UTC datetime. Specify relative datetime by using a number followed by a duration designator: y - year M - month w - week d - day h - hour m - minute s - second Note: These components must be used individually. They can not be combined.	Absolute: "2022-03-23" "2022-03-23 22:23" "2022-03-23 22:23:53" "2022-03-23T22:23:53Z" Relative -2w : two weeks ago -1d : yesterday 1w : next week +1y : next year

Identifiers

The table below outlines the identifiers you can use when querying assets. Each identifier has a specific type, which determines the values that can be used in a query. Keep in mind that not all operators are compatible with every identifier. Similarly, not all identifiers support the ordering functionality.

String Data Type

Identifier	Available Operators	Supports Ordering	Examples
checksum	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	checksum = "3cd7a0db76ff9dca48979e24c39b408c"
description	=, !=, , !, IS (NOT) EMPTY, (NOT) IN Note: When using ~ and !~, results may vary. Be sure the value is lowercase and has no special characters.	No	description IS EMPTY description ~ "tb303"
division	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	division = "4643adcc1621814e91c489b34bb428ed"
externalId	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	externalId = "f7bf05d6-9131-4b3d-8ce6-55b331cc38f4"
id	=, !=, IS (NOT) EMPTY, (NOT) IN	No	id = "a3e72bbfae0b87381106b1beb685ea88"
name	=, !=, , !, IS (NOT) EMPTY, (NOT) IN Note: When using ~ and !~, the value must be lowercase and between 2 and 64 characters in length.	Yes	name = "my-file.pdf" name ~ "original"
status	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	status = "active"
type	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	type = "document"

String Lists

Identifier	Available Operators	Supports Ordering	Examples
authors	, !, IS (NOT) EMPTY, (NOT) IN	No	authors ~ "c64ac7472f03f86a53e0b6759dffa914"
countries	, !, IS (NOT) EMPTY, (NOT) IN	No	countries ~ "BE"
downloadableExternal	, !, IS (NOT) EMPTY, (NOT) IN	No	downloadableExternal IS EMPTY
downloadableInternal	, !, IS (NOT) EMPTY, (NOT) IN	No	downloadableInternal ~ "pdf"
languages	, !, IS (NOT) EMPTY, (NOT) IN	No	languages IS EMPTY languages ~ "nl"
tags	, !, IS (NOT) EMPTY, (NOT) IN	No	tags ~ "5debee5405cfd4e2797b553037ceb78c"

Boolean Data Type

Identifier	Available Operators	Supports Ordering	Examples
hasLockedPages	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	hasLockedPages = true
isAnnotatable	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isAnnotatable = true
isDivisionShared	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isDivisionShared = true
isEditable	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isEditable = false
isOnlyEntireDocumentShareable	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isOnlyEntireDocumentShareable = false
isProcessedUsed	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isProcessedUsed = false
isRenderExternalAllowed	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isRenderExternalAllowed = false
isSensitive	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isSensitive = true
isShareable	=, !=, IS (NOT) EMPTY, (NOT) IN	Yes	isShareable = true

Datetime Data Type

Identifier	Available Operators	Supports Ordering	Examples
archivedAt	=, !=, < , >, <=, >=, IS (NOT) EMPTY, (NOT) IN	Yes	archivedAt IS NOT EMPTY
createdAt	=, !=, < , >, <=, >=, IS (NOT) EMPTY, (NOT) IN	Yes	createdAt > -1M
expiresAt	=, !=, < , >, <=, >=, IS (NOT) EMPTY, (NOT) IN	Yes	releasedAt > -1w
lastUploadedAt	=, !=, < , >, <=, >=, IS (NOT) EMPTY, (NOT) IN	Yes	lastUploadedAt > -1d
updatedAt	=, !=, < , >, <=, >=, IS (NOT) EMPTY, (NOT) IN	Yes	updatedAt > -1w

Number Data Type

Identifier	Available Operators	Supports Ordering	Examples
pageCount	=, !=, <, >, <=, >=, IS (NOT) EMPTY, (NOT) IN	Yes	pageCount > 100
size	=, !=, <, >, <=, >=, IS (NOT) EMPTY, (NOT) IN	Yes	size > 5242880

Supported Operators

In this section, we will cover the different operators you can use when constructing queries in ShowQL:

Operator	Description
=, !=	= (Equals) - This allows you to compare the equality of the identifier to a given value. Note that this operator is not supported when comparing string list values; instead, use the contains operator (~). Example: division = "4643adcc1621814e91c489b34bb428ed" != (Not Equal To) - This tests the inequality of the identifier to a given value. Example: division != "4643adcc1621814e91c489b34bb428ed"
, !	~ (Contains) - This checks whether the identifier contains the specified value. You can use this operator on both string list and string identifiers. Examples: name ~ "sample" tags ~ "5debee5405cfd4e2797b553037ceb78c" !~ (Does Not Contain) - This checks whether the identifier does not contain the specified value. You can use this operator on both string list and string identifiers. Examples: countries !~ "BE" languages !~ "nl"
<, >, <=, >=	These operators are used to compare identifiers with numeric or datetime values. < (Less Than) - This specifies that the result should be lower than the given value. Example: size < 1024 > (Greater Than) - This specifies that the result should be higher than the given value. Example: createdAt > "2023-01-01" <= (Less Than or Equal To) - This specifies that the result should be lower or the same as the given value. >= (Greater Than or Equal To) - This specifies that the result should be higher or the same as the given value.
IS EMPTY, IS NOT EMPTY	IS EMPTY - The identifer should be empty. This includes missing values and empty strings (""). IS NOT EMPTY - The identifer should not be empty. Missing values and empty strings ("") are excluded. Examples: tags IS EMPTY languages IS NOT EMPTY
IN, NOT IN	IN - The identifer should be within the defined values or lists. NOT IN - The identifer should not be within the defined values or lists. Examples: division IN ("4643adcc1621814e91c489b34bb428ed") countries NOT IN ("BE", "NL")

Order Results

By default, assets in ShowQL are ordered by their createdAt date, in ascending order. You can customize the order of your query results using an ORDER BY clause, followed by the preferred order:

• ASC - Ascending order (from smallest to largest)
• DESC - Descending order (from largest to smallest)

Single Identifier Ordering

To order your results by a specific identifier, specify the identifier in the ORDER BY clause in your query.

Example

In this example, the results will be returned in descending order based on the values of the lastUploadedAt identifier.

division = "4643adcc1621814e91c489b34bb428ed"
ORDER BY lastUploadedAt DESC

Multiple Identifier Ordering

If you want to order your results by multiple identifiers to resolve order conflicts, you can specify each identifier with its preferred ordering.

Example

In this example, the results will first be ordered:

1. First by the size value in descending order.
2. Then by the lastUploadedAt value in ascending order.

division = "4643adcc1621814e91c489b34bb428ed"
ORDER BY size DESC, lastUploadedAt ASC

caution

Not all identifiers support ordering. Be sure to check the Identifiers section to see if your chosen identifier supports ordering.

Additional Examples

The following are typical queries.

Recently Uploaded Videos

Assets uploaded within the last week:

(type = "video" AND lastUploadedAt > -1w) AND
(archivedAt IS EMPTY)

Largest Documents

Find the largest documents and order them from largest to smallest:

type = "document" AND archivedAt IS EMPTY
ORDER BY size DESC

Assets with Tags

Get all assets with the specified tag identifiers, but not a specific tag identifier:

((tags ~ "5debee5405cfd4e2797b553037ceb78c" AND tags ~ "ba16a6afc2d4247862c9986aa09772fd") OR
(tags ~ "b68d760b609e335953fb8a4056365580")) AND
(tags !~ "951aca1a3576e1d1488f29e74756ee60") AND
archivedAt IS EMPTY

Tagged Assets Excluding Videos

All assets with the specified tag identifier that are not videos:

tags ~ "5debee5405cfd4e2797b553037ceb78c" AND
type != "video" AND
archivedAt IS EMPTY

Unshareable Assets

All active assets that are not shareable:

isShareable = false AND
archivedAt IS EMPTY

Filter by external ID

externalId = "my-external-id"