LabKey SQL 

LabKey SQL is a SQL dialect that supports (1) most standard SQL functionality and (2) provides extended functionality that is unique to LabKey, including:

  • Security. Before execution, all SQL queries are checked against the user's security roles/permissions.
  • Lookup columns. Lookup columns use an intuitive syntax to access data in other tables to achieve what would normally require a JOIN statement. For example: "SomeTable.ForeignKey.FieldFromForeignTable" For details see Lookups. The special lookup column "Datasets" is injected into each study dataset and provides a syntax shortcut when joining the current dataset to another dataset that has compatible join keys. See example usage.
  • Cross-folder querying. Queries can be scoped to folders broader than the current folder and can draw from tables in folders other than the current folder. See Cross-Folder Queries.
  • Parameterized SQL statements. The PARAMETERS keyword lets you define parameters for a query.  An associated API gives you control over the parameterized query from JavaScript code. See Parameterized SQL Queries.
  • Pivot tables. The PIVOT...BY and PIVOT...IN expressions provide a syntax for creating pivot tables. See Pivot Queries.
  • User-related functions. USERID() and ISMEMBEROF(groupid) let you control query visibility based on the user's group membership. 
  • Ontology-related functions. (Premium Feature) Access preferred terms and ontology concepts from SQL queries. See Ontology SQL.
  • Lineage-related functions. (Premium Feature) Access ancestors and descendants of samples and data class entities. See Lineage SQL Queries.
  • Annotations. Override some column metadata using SQL annotations. See Use SQL Annotations.

Reference Tables:


ASAliases can be explicitly named using the AS keyword. Note that the AS keyword is optional: the following select clauses both create an alias called "Name":

    SELECT LCASE(FirstName) AS Name
    SELECT LCASE(FirstName) Name

Implicit aliases are automatically generated for expressions in the SELECT list.  In the query below, an output column named "Expression1" is automatically created for the expression "LCASE(FirstName)":

    SELECT LCASE(FirstName)

ASCENDING, ASCReturn ORDER BY results in ascending value order.

    ORDER BY Weight ASC 

Defined valid datatype keywords which can be used as cast/convert targets, and to what java.sql.Types name each keyword maps. Keywords are case-insensitive.



CAST(TimeCreated AS DATE)

CAST(WEEK( as INTEGER) as WeekOfYear,

DESCENDING, DESCReturn results in descending value order.
DISTINCTReturn distinct, non duplicate, values.

    FROM Demographics 
EXISTSReturns a Boolean value based on a subquery. Returns TRUE if at least one row is returned from the subquery.

The following example returns any plasma samples which have been assayed with a score greater than 80%. Assume that ImmuneScores.Data.SpecimenId is a lookup field (aka foreign key) to Plasma.Name. 

    SELECT Plasma.Name
    FROM Plasma
        (SELECT *
        FROM assay.General.ImmuneScores.Data
        WHERE SpecimenId = Plasma.Name
        AND ScorePercent > .8)
FROMThe FROM clause in LabKey SQL must contain at least one table. It can also contain JOINs to other tables. Commas are supported in the FROM clause:

    FROM TableA, TableB
    WHERE TableA.x = TableB.x

Nested joins are supported in the FROM clause:

    FROM TableA LEFT JOIN (TableB INNER JOIN TableC ON ...) ON...

To refer to tables in LabKey folders other than the current folder, see Cross-Folder Queries.
GROUP BYUsed with aggregate functions to group the results.  Defines the "for each" or "per".  The example below returns the number of records "for each" participant:

    SELECT ParticipantId, COUNT(Created) "Number of Records"
    FROM "Physical Exam"
    GROUP BY ParticipantId

HAVINGUsed with aggregate functions to limit the results.  The following example returns participants with 10 or more records in the Physical Exam table:

    SELECT ParticipantId, COUNT(Created) "Number of Records"
    FROM "Physical Exam"
    GROUP BY ParticipantId
    HAVING COUNT(Created) > 10

HAVING can be used without a GROUP BY clause, in which case all selected rows are treated as a single group for aggregation purposes.

    SELECT *
    FROM "Physical Exam"
    FULL JOIN "Lab Results"
    ON "Physical Exam".ParticipantId = "Lab Results".ParticipantId 
LIMITLimits the number or records returned by the query.  The following example returns the 10 most recent records:

    SELECT *
    FROM "Physical Exam"
    ORDER BY Created DESC LIMIT 10

NULLIF(A,B) Returns NULL if A=B, otherwise returns A.
ORDER BY One option for sorting query results; may produce unexpected results when dataregions or views also have sorting applied. If you can instead use a sort on the custom view or via, the API, those methods are preferred (see below). Use ORDER BY with LIMIT to improve performance:

    SELECT ParticipantID,
    Height_cm AS Height
    FROM "Physical Exam"

For best ORDER BY results, be sure to a) SELECT the columns on which you are sorting, and b) if sorting on an expression include the expression in the SELECT and sort by the alias of the expression. For example:

    SELECT A, B, A+B AS C @hidden ... ORDER BY C
is preferable to:

Troubleshooting: "Why is the ORDER BY clause not working as expected?"

When authoring queries in LabKey SQL, the query is typically processed as a subquery within a parent query. This parent query may apply it's own sorting overriding the ORDER BY clause in the subquery. This parent "view layer" provides default behavior like pagination, lookups, etc. but may also inadvertantly apply an unexpected additional sort.

Two recommended solutions for predictable sorting:
(1) Define the sort in the parent query using the grid view customizer. This may involve adding a new named view of that query to use as your parent query.
(2) Use the "sort" property in the selectRows API call.

PARAMETERSQueries can declare parameters using the PARAMETERS keyword. Default values data types are supported as shown below:

    SELECT *
    FROM "Physical Exam"
    WHERE Temp_C = X

Parameter names will override any unqualified table column with the same name.  Use a table qualification to disambiguate.  In the example below, R.X refers to the column while X refers to the parameter:

    SELECT *
    FROM Table R
    WHERE R.X = X


Parameter values can be passed via JavaScript API calls to the query. For details see Parameterized SQL Queries.

PIVOT/PIVOT...BY/PIVOT...INRe-visualize a table by rotating or "pivoting" a portion of it, essentially promoting cell data to column headers. See Pivot Queries for details and examples.
SELECTSELECT queries are the only type of query that can currently be written in LabKey SQL.  Sub-selects are allowed both as an expression, and in the FROM clause.

Aliases are automatically generated for expressions after SELECT.  In the query below, an output column named "Expression1" is automatically generated for the expression "LCASE(FirstName)":

    SELECT LCASE(FirstName) FROM...

UNION, UNION ALLThe UNION clause is the same as standard SQL.  LabKey SQL supports UNION in subqueries. 
VALUES ... ASA subset of VALUES syntax is supported. Generate a "constant table" by providing a parenthesized list of expressions for each row in the table. The lists must all have the same number of elements and corresponding entries must have compatible data types. For example:

VALUES (1, 'one'), (2, 'two'), (3, 'three') AS t;

You must provide the alias for the result ("AS t" in the above), aliasing column names is not supported. The column names will be 'column1', 'column2', etc.
WHEREFilter the results for certain values. Example:

    SELECT *
    FROM "Physical Exam"
    WHERE YEAR(Date) = 2010 

Define a "common table expression" which functions like a subquery or inline view table. Especially useful for recursive queries.

Usage Notes: If there are UNION clauses that do not reference the common table expression (CTE) itself, the server interprets them as normal UNIONs. The first subclause of a UNION may not reference the CTE. The CTE may only be referenced once in a FROM clause or JOIN clauses within the UNION. There may be multiple CTEs defined in the WITH. Each may reference the previous CTEs in the WITH. No column specifications are allowed in the WITH (as some SQL versions allow).

Exception Behavior: Testing indicates that PostgreSQL does not provide an exception to LabKey Server for a non-ending, recursive CTE query. This can cause the LabKey Server to wait indefinitely for the query to complete. MS SQL Server does provide an exception to the server which allows the LabKey Server to end the query attempt. 

A non-recursive example:

     SELECT *
     FROM "/Studies/Study A/".study.Demographics
     SELECT *
     FROM "/Studies/Study B/".study.Demographics
SELECT ParticipantId from AllDemo

A recursive example: In a table that holds parent/child information, this query returns all of the children and grandchildren (recursively down the generations), for a given "Source" parent.


WITH Derivations AS 
   -- Anchor Query. User enters a 'Source' parent 
   SELECT Item, Parent
   FROM Items 
   WHERE Parent = Source

   -- Recursive Query. Get the children, grandchildren, ... of the source parent
   SELECT i.Item, i.Parent 
   FROM Items i INNER JOIN Derivations p  
   ON i.Parent = p.Item 
SELECT * FROM Derivations



The following constant values can be used in LabKey SQL queries.

CAST('Infinity' AS DOUBLE) Represents positive infinity.
CAST('-Infinity' AS DOUBLE) Represents negative infinity.
CAST('NaN' AS DOUBLE) Represents "Not a number".
TRUE Boolean value.
FALSE Boolean value.


String OperatorsNote that strings are delimited with single quotes. Double quotes are used for column and table names containing spaces.
 ||String concatenation. For example:    
    SELECT ParticipantId,
    City || ', ' || State AS CityOfOrigin
    FROM Demographics

If any argument is null, the || operator will return a null string. To handle this, use COALESCE with an empty string as it's second argument, so that the other || arguments will be returned:
    City || ', ' || COALESCE (State, '')
 LIKEPattern matching. The entire string must match the given pattern. Ex: LIKE 'W%'.
 NOT LIKENegative pattern matching. Will return values that do not match a given pattern. Ex: NOT LIKE 'W%'
Arithmetic Operators 
Comparison operators 
 !=Does not equal
 <> Does not equal
 > Is greater than
 < Is less than
 >=Is greater than or equal to
 <=Is less than or equal to
 BETWEENBetween two values. Values can be numbers, strings or dates.
 INExample: WHERE City IN ('Seattle', 'Portland')
 NOT INExample: WHERE City NOT IN ('Seattle', 'Portland')
Bitwise Operators 
 &Bitwise AND
 |Bitwise OR
 ^Bitwise exclusive OR
Logical Operators 
 ANDLogical AND
 ORLogical OR
 NOTExample: WHERE NOT Country='USA'

Operator Order of Precedence

Order of PrecedenceOperators
 1 - (unary) , + (unary), CASE
 2 *, / (multiplication, division) 
 3 +, -, & (binary plus, binary minus)
 4 & (bitwise and)
 5 ^ (bitwise xor)
 6 | (bitwise or)
 7 || (concatenation)
 9 =, IS, IS NOT, <>, !=  
10 NOT
11 AND
12 OR

Aggregate Functions - General

COUNTThe special syntax COUNT(*) is supported as of LabKey v9.2.
GROUP_CONCATAn aggregate function, much like MAX, MIN, AVG, COUNT, etc. It can be used wherever the standard aggregate functions can be used, and is subject to the same grouping rules. It will return a string value which is comma-separated list of all of the values for that grouping. A custom separator, instead of the default comma, can be specified. Learn more here.
The example below specifies a semi-colon as the separator:

    SELECT Participant, GROUP_CONCAT(DISTINCT Category, ';') AS CATEGORIES FROM SomeSchema.SomeTable

To use a line-break as the separator, use the following:

    SELECT Participant, GROUP_CONCAT(DISTINCT Category, chr(10)) AS CATEGORIES FROM SomeSchema.SomeTable  
stddev(expression)Standard deviation
stddev_pop(expression)Population standard deviation of the input values.
variance(expression)Historical alias for var_samp.
var_pop(expression)Population variance of the input values (square of the population standard deviation).
median(expression)The 50th percentile of the values submitted.

Aggregate Functions - PostgreSQL Only

bool_and(expression)Aggregates boolean values. Returns true if all values are true and false if any are false.
bool_or(expression)Aggregates boolean values. Returns true if any values are true and false if all are false.
bit_and(expression)Returns the bitwise AND of all non-null input values, or null if none.
bit_or(expression)Returns the bitwise OR of all non-null input values, or null if none.
every(expression)Equivalent to bool_and(). Returns true if all values are true and false if any are false.
corr(Y,X)Correlation coefficient.
covar_pop(Y,X)Population covariance.
covar_samp(Y,X)Sample covariance.
regr_avgx(Y,X)Average of the independent variable: (SUM(X)/N).
regr_avgy(Y,X)Average of the dependent variable: (SUM(Y)/N).
regr_count(Y,X)Number of non-null input rows.
regr_intercept(Y,X)Y-intercept of the least-squares-fit linear equation determined by the (X,Y) pairs.
regr_r2(Y,X)Square of the correlation coefficient.
regr_slope(Y,X)Slope of the least-squares-fit linear equation determined by the (X,Y) pairs.
regr_sxx(Y,X)Sum of squares of the independent variable.
regr_sxy(Y,X)Sum of products of independent times dependent variable.
regr_syy(Y,X)Sum of squares of the dependent variable.
stddev_samp(expression)Sample standard deviation of the input values.
var_samp(expression)Sample variance of the input values (square of the sample standard deviation).

SQL Functions - General

Many of these functions are similar to standard SQL functions -- see the JBDC escape syntax documentation for additional information.

abs(value)Returns the absolute value.
acos(value)Returns the arc cosine.
age(date1, date2)

Supplies the difference in age between the two dates, calculated in years.

age(date1, date2, interval)

The interval indicates the unit of age measurement, either SQL_TSI_MONTH or SQL_TSI_YEAR.

age_in_months(date1, date2)Behavior is undefined if date2 is before date1.
age_in_years(date1, date2)Behavior is undefined if date2 is before date1.
asin(value)Returns the arc sine.
atan(value)Returns the arc tangent.
atan2(value1, value2)Returns the arctangent of the quotient of two values.

CASE can be used to test various conditions and return various results based on the test. You can use either simple CASE or searched CASE syntax. In the following examples "value#" indicates a value to match against, where "test#" indicates a boolean expression to evaluate. In the "searched" syntax, the first test expression that evaluates to true will determine which result is returned. Note that the LabKey SQL parser sometimes requires the use of additional parentheses within the statement.

    CASE (value) WHEN (value1) THEN (result1) ELSE (result2) END
    CASE (value) WHEN (value1) THEN (result1) WHEN (value2) THEN (result2) ELSE (resultDefault) END
    CASE WHEN (test1) THEN (result1) ELSE (result2) END
    CASE WHEN (test1) THEN (result1) WHEN (test2) THEN (result2) WHEN (test3) THEN (result3) ELSE (resultDefault) END


    SELECT "StudentName",
    CASE WHEN (Division = 'Grades 3-5') THEN (Scores.Score*1.13) ELSE Score END AS AdjustedScore,
    FROM Scores 

ceiling(value)Rounds the value up.
coalesce(value1,...,valueN)Returns the first non-null value in the argument list. Use to set default values for display.
concat(value1,value2)Concatenates two values. 
contextPath()Returns the context path starting with “/” (e.g. “/labkey”). Returns the empty string if there is no current context path. (Returns VARCHAR.)
cos(radians)Returns the cosine.
cot(radians)Returns the cotangent.
curdate()Returns the current date.
curtime()Returns the current time
dayofmonth(date)Returns the day of the month (1-31) for a given date.
dayofweek(date)Returns the day of the week (1-7) for a given date. (Sun=1 and Sat=7)
dayofyear(date)Returns the day of the year (1-365) for a given date.
degrees(radians)Returns degrees based on the given radians.
exp(n)Returns Euler's number e raised to the nth power. e = 2.71828183 
floor(value)Rounds down to the nearest integer.
folderName()LabKey SQL extension function. Returns the name of the current folder, without beginning or trailing "/". (Returns VARCHAR.)
folderPath()LabKey SQL extension function. Returns the current folder path (starts with “/”, but does not end with “/”). The root returns “/”. (Returns VARCHAR.)
greatest(a, b, c, ...)Returns the greatest value from the list expressions provided. Any number of expressions may be used. The expressions must have the same data type, which will also be the type of the result. The LEAST() function is similar, but returns the smallest value from the list of expressions. GREATEST() and LEAST() are not implemented for SAS databases.

When NULL values appear in the list of expressions, different database implementations as follows:

- PostgreSQL & MS SQL Server ignore NULL values in the arguments, only returning NULL if all arguments are NULL.
- Oracle and MySql return NULL if any one of the arguments are NULL. Best practice: wrap any potentially nullable arguments in coalesce() or ifnull() and determine at the time of usage if NULL should be treated as high or low.


SELECT greatest(score_1, score_2, score_3) As HIGH_SCORE
FROM MyAssay 

hour(time)Returns the hour for a given date/time.
ifdefined(column_name)IFDEFINED(NAME) allows queries to reference columns that may not be present on a table. Without using IFDEFINED(), LabKey will raise a SQL parse error if the column cannot be resolved. Using IFDEFINED(), a column that cannot be resolved is treated as a NULL value. The IFDEFINED() syntax is useful for writing queries over PIVOT queries or assay tables where columns may be added or removed by an administrator.
ifnull(testValue, defaultValue)If testValue is null, returns the defaultValue.  Example: IFNULL(Units,0)
isequalLabKey SQL extension function. ISEQUAL(a,b) is equivalent to (a=b OR (a IS NULL AND b IS NULL))
ismemberof(groupid)LabKey SQL extension function. Returns true if the current user is a member of the specified group.
javaConstant(fieldName)LabKey SQL extension function. Provides access to public static final variable values.  For details see LabKey SQL Utility Functions.
lcase(string)Convert all characters of a string to lower case.
least(a, b, c, ...)Returns the smallest value from the list expressions provided. For more details, see greatest() above.
left(string, integer)Returns the left side of the string, to the given number of characters. Example: SELECT LEFT('STRINGVALUE',3) returns 'STR'
length(string)Returns the length of the given string.
locate(substring, string) locate(substring, string, startIndex)Returns the location of the first occurrence of substring within string.  startIndex provides a starting position to begin the search. 
log(n)Returns the natural logarithm of n.
log10(n)Base base 10 logarithm on n.
lower(string)Convert all characters of a string to lower case.
ltrim(string)Trims white space characters from the left side of the string. For example: LTRIM('     Trim String')
minute(time)Returns the minute value for the given time. 
mod(dividend, divider)Returns the remainder of the division of dividend by divider.
moduleProperty(module name,  property name)

LabKey SQL extension function. Returns a module property, based on the module and property names. For details see LabKey SQL Utility Functions

month(date)Returns the month value (1-12) of the given date. 
monthname(date)Return the month name of the given date.
now()Returns the system date and time.
overlapsLabKey SQL extension function. Supported only when PostgreSQL is installed as the primary database.    

The LabKey SQL syntax above is translated into the following PostgreSQL syntax:    
pi()Returns the value of π.
power(base, exponent)Returns the base raised to the power of the exponent. For example, power(10,2) returns 100.
quarter(date)Returns the yearly quarter for the given date where the 1st quarter = Jan 1-Mar 31, 2nd quarter = Apr 1-Jun 30, 3rd quarter = Jul 1-Sep 30, 4th quarter = Oct 1-Dec 31
radians(degrees)Returns the radians for the given degrees.
rand(), rand(seed)Returns a random number between 0 and 1.
repeat(string, count)Returns the string repeated the given number of times. SELECT REPEAT('Hello',2) returns 'HelloHello'.
round(value, precision)Rounds the value to the specified number of decimal places. ROUND(43.3432,2) returns 43.34
rtrim(string)Trims white space characters from the right side of the string. For example: RTRIM('Trim String     ')
second(time)Returns the second value for the given time.
sign(value)Returns the sign, positive or negative, for the given value. 
sin(value)Returns the sine for the given value.
startswith(string, prefix)Tests to see if the string starts with the specified prefix. For example, STARTSWITH('12345','2') returns FALSE.
sqrt(value)Returns the square root of the value.
substring(string, start, length)Returns a portion of the string as specified by the start location (1-based) and length (number of characters). For example, substring('SomeString', 1,2) returns the string 'So'.

Returns the tangent of the value.

timestampadd(interval, number_to_add, timestamp)

Adds an interval to the given timestamp value. The interval value must be surrounded by quotes. Possible values for interval:


Example: TIMESTAMPADD('SQL_TSI_QUARTER', 1, "Physical Exam".date) AS NextExam

timestampdiff(interval, timestamp1, timestamp2)

Finds the difference between two timestamp values at a specified interval. The interval must be surrounded by quotes.

Example: TIMESTAMPDIFF('SQL_TSI_DAY', SpecimenEvent.StorageDate, SpecimenEvent.ShipDate)

Note that PostgreSQL does not support the following intervals:


As a workaround, use the 'age' functions defined above.

truncate(numeric value, precision)Truncates the numeric value to the precision specified. This is an arithmetic truncation, not a string truncation.
  TRUNCATE(123.4567,1) returns 123.4
  TRUNCATE(123.4567,2) returns 123.45
  TRUNCATE(123.4567,-1) returns 120.0

May require an explict CAST into NUMERIC, as LabKey SQL does not check data types for function arguments.

TRUNCATE(CAST(Temperature AS NUMERIC),1) as truncTemperature
FROM PhysicalExam
ucase(string), upper(string)Converts all characters to upper case.
userid()LabKey SQL extension function. Returns the userid, an integer, of the logged in user. 
username()LabKey SQL extension function. Returns the current user display name. VARCHAR
version()LabKey SQL extension function. Returns the current schema version of the core module as a numeric with four decimal places. For example: 20.0070
week(date)Returns the week value (1-52) of the given date.
year(date)Return the year of the given date.  Assuming the system date is March 4 2023, then YEAR(NOW()) return 2023.

SQL Functions - PostgreSQL Specific

LabKey SQL supports the following PostgreSQL functions.
See the  PostgreSQL docs for usage details.

 PostgreSQL Function  Docs 
 ascii(value)Returns the ASCII code of the first character of value.   
Removes characters in trimchars from the start and end of string. trimchars defaults to white space.

BTRIM(' trim    ') returns TRIM 
BTRIM('abbatrimabtrimabba', 'ab') returns trimabtrim

 character_length(value),  char_length(value)

Returns the number of characters in value.
 chr(integer_code)Returns the character with the given integer_code.  

CHR(70) returns F
 concat_ws(sep text,
val1 "any" [, val2 "any" [,...]]) -> text
Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored. See the PostgreSQL docs.

concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22
See the PostgreSQL docs.
See the PostgreSQL docs.
 is_distinct_from(a, b) OR
 is_not_distinct_from(a, b)
Not equal (or equal), treating null like an ordinary value. 
 initcap(string)Converts the first character of each separate word in string to uppercase and the rest to lowercase. 
Pads string to length int by prepending characters fillchars
 md5(text)Returns the hex MD5 value of text
 octet_length(string) Returns the number of bytes in string.
 overlapsSee above for syntax details.
 quote_ident(string)Returns string quoted for use as an identifier in an SQL statement. 
 quote_literal(string)Returns string quoted for use as a string literal in an SQL statement.
 regexp_replace See PostgreSQL docs for details: reference doc, example doc
 repeat(string, int)Repeats string the specified number of times.
Searches string for matchString and replaces occurrences with replaceString.
Pads string to length int by postpending characters fillchars.
 similar_to(A,B,C)String pattern matching using SQL regular expressions. 'A' similar to 'B' escape 'C'. See the PostgreSQL docs.
Splits string on delimiter and returns fragment number int (starting the count from 1).

SPLIT_PART('mississippi', 'i', 4) returns 'pp'.
 string_to_arraySee Array Functions in the PostgreSQL docs.
Returns the position of substring in string. (Count starts from 1.)

Extracts the number of characters specified by charCount from string starting at position fromPosition.

SUBSTR('char_sequence', 5, 2) returns '_s' 

Convert string to ASCII from another encoding.
 to_hex(int)Converts int to its hex representation.
Characters in string matching a character in the fromString set are replaced by the corresponding character in toString.
 to_charSee Data Type Formatting Functions in the PostgreSQL docs.
See Data Type Formatting Functions in the PostgreSQL docs. 
 to_timestampSee Data Type Formatting Functions in the PostgreSQL docs.
 to_numberSee Data Type Formatting Functions in the PostgreSQL docs.
 unnestSee Array Functions in the PostgreSQL docs.

JSON and JSONB Operators and Functions

LabKey SQL supports the following PostgreSQL JSON and JSONB operators and functions. Note that LabKey SQL does not natively understand arrays and some other features, but it may still be possible to use the functions that expect them.
See the PostgreSQL docs for usage details.

PostgreSQL Operators and FunctionsDocs
->, ->>, #>, #>>, @>, <@, ?, ?|, ?&, ||, -, #-
LabKey SQL supports these operators via a pass-through function, json_op. The function's first argument is the operator's first operand. The first second is the operator, passed as a string constant. The function's third argument is the second operand. For example, this Postgres SQL expression:
a_jsonb_column --> 2
can be represented in LabKey SQL as:
json_op(a_jsonb_column, '-->', 2)
parse_json, parse_jsonbCasts a text value to a parsed JSON or JSONB data type. For example,
'{"a":1, "b":null}'::jsonb
CAST('{"a":1, "b":null}' AS JSONB)
can be represented in LabKey SQL as:
parse_jsonb('{"a":1, "b":null}')
to_json, to_jsonbConverts a value to the JSON or JSONB data type. Will treat a text value as a single JSON string value
array_to_jsonConverts an array value to the JSON data type.
row_to_jsonConverts a scalar (simple value) row to JSON. Note that LabKey SQL does not support the version of this function that will convert an entire table to JSON. Consider using "to_jsonb()" instead.
json_build_array, jsonb_build_arrayBuild a JSON array from the arguments
json_build_object, jsonb_build_objectBuild a JSON object from the arguments
json_object, jsonb_objectBuild a JSON object from a text array
json_array_length, jsonb_array_lengthReturn the length of the outermost JSON array
json_each, jsonb_eachExpand the outermost JSON object into key/value pairs. Note that LabKey SQL does not support the table version of this function. Usage as a scalar function like this is supported:
SELECT json_each('{"a":"foo", "b":"bar"}') AS Value
json_each_text, jsonb_each_textExpand the outermost JSON object into key/value pairs into text. Note that LabKey SQL does not support the table version of this function. Usage as a scalar function (similar to json_each) is supported.
json_extract_path, jsonb_extract_pathReturn the JSON value referenced by the path
json_extract_path_text, jsonb_extract_path_textReturn the JSON value referenced by the path as text
json_object_keys, jsonb_object_keysReturn the keys of the outermost JSON object
json_array_elements, jsonb_array_elementsExpand a JSON array into a set of values
json_array_elements_text, jsonb_array_elements_textExpand a JSON array into a set of text values
json_typeof, jsonb_typeofReturn the type of the outermost JSON object
json_strip_nulls, jsonb_strip_nullsRemove all null values from a JSON object
jsonb_insertInsert a value within a JSON object at a given path
jsonb_prettyFormat a JSON object as indented text
jsonb_setSet the value within a JSON object for a given path. Strict, i.e. returns NULL on NULL input.
jsonb_set_laxSet the value within a JSON object for a given path. Not strict; expects third argument to specify how to treat NULL input (one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_target').
jsonb_path_exists, jsonb_path_exists_tzChecks whether the JSON path returns any item for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_match, jsonb_path_match_tzReturns the result of a JSON path predicate check for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_query, jsonb_path_query_tzReturns all JSON items returned by the JSON path for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_query_array, jsonb_path_query_array_tzReturns as an array, all JSON items returned by the JSON path for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_query_first, jsonb_path_query_first_tzReturns the first JSON item returned by the JSON path for the specified JSON value. The "_tz" variant is timezone aware.

SQL Functions - MS SQL Server Specific

LabKey SQL supports the following SQL Server functions.
See the SQL Server docs for usage details.

MS SQL Server FunctionDescription
ascii(value)Returns the ASCII code of the first character of value.  
char(int), chr(int)Returns an character for the specified ascii code int
Returns the position of searchString in string, starting the search at index.
concat_ws(sep text,
val1 "any" [, val2 "any" [,...]]) -> text
Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored.

concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22
difference(string,string)Returns the difference between the soundex values of two expressions as an integer.
See the MS SQL docs.
isnumeric(expression)Determines whether an expression is a valid numeric type. See the MS SQL docs.
len(string)Returns the number of characters in string. Trailing white space is excluded. 
patindex(pattern,string)Returns the position of the first occurrence of pattern in string. See the MS SQL docs
quotenameSee the MS SQL docs.

replace(string,pattern, replacement)

Replaces all occurences of pattern with replacement in the string provided. See the MS SQL docs.
replicate(string,int)Replicate string the specified number of times.
reverse(string)Returns the string in reverse character sequence.
right(string,index)Returns the right part of string to the specified index.
soundexSee the MS SQL docs.
space(int)Returns a string of white space characters.
str(float,length,decimal)See the MS SQL docs
Inserts replaceWith into string. Deletes the specified length of characters in string at the start position and then inserts replaceWith. See the MS SQL docs.

General Syntax

Syntax ItemDescription
Case SensitivitySchema names, table names, column names, SQL keywords, function names are case-insensitive in LabKey SQL.
Comments Comments that use the standard SQL syntax can be included in queries. '--' starts a line comment. Also, '/* */' can surround a comment block:

-- line comment 1
-- line comment 2
/* block comment 1
    block comment 2 */

IdentifiersIdentifiers in LabKey SQL may be quoted using double quotes. (Double quotes within an identifier are escaped with a second double quote.) 

SELECT "Physical Exam".*
LookupsLookups columns reference data in other tables.  In SQL terms, they are foreign key columns.  See Lookups for details on creating lookup columns. Lookups use a convenient syntax of the form "Table.ForeignKey.FieldFromForeignTable" to achieve what would normally require a JOIN in SQL. Example:


String LiteralsString literals are quoted with single quotes ('). Within a single quoted string, a single quote is escaped with another single quote.

   SELECT * FROM TableName WHERE FieldName = 'Jim''s Item' 

Date/Time Literals

Date and Timestamp (Date&Time) literals can be specified using the JDBC escape syntax

{ts '2001-02-03 04:05:06'}

{d '2001-02-03'}

Related Topics


Was this content helpful?

Log in or register an account to provide feedback

expand all collapse all