Tools

SQL function reference

# SQL function reference

Use type-aware SQL functions to inspect and mutate Redis collections, streams, RedisJSON values, and grouped rows.

Varc SQL keeps every collection in one row. Type-aware functions read a bounded scalar fact from the row's `value` without flattening the collection. Aggregates collapse those source rows after `WHERE`.

| Function                                     | Redis type                                  | Result                 |
| -------------------------------------------- | ------------------------------------------- | ---------------------- |
| `CARDINALITY(value)`                         | string, hash, list, set, zset, stream, JSON | Integer or NULL        |
| `HASH_GET(value, field)`                     | hash                                        | Binary or NULL         |
| `HASH_HAS(value, field)`                     | hash                                        | Boolean                |
| `LIST_GET(value, index)`                     | list                                        | Binary or NULL         |
| `LIST_CONTAINS(value, item)`                 | list                                        | Boolean                |
| `SET_CONTAINS(value, member)`                | set                                         | Boolean                |
| `ZSET_SCORE(value, member)`                  | zset                                        | Real or NULL           |
| `STREAM_CONTAINS(value, field [, expected])` | stream                                      | Boolean                |
| `JSON_VALUE(value, '$.path')`                | RedisJSON                                   | JSON scalar or NULL    |
| `COUNT(*)`                                   | any                                         | Integer                |
| `COUNT(expr)`                                | any scalar                                  | Integer                |
| `SUM(expr)`                                  | numeric scalar                              | Integer, Real, or NULL |
| `AVG(expr)`                                  | numeric scalar                              | Real or NULL           |
| `MIN(expr)` / `MAX(expr)`                    | non-opaque scalar                           | Scalar or NULL         |

The first argument must be the `value` column. Fields, members, items, expected values, and JSON paths are exact text or binary constants; a list index is an integer constant. Text constants are UTF-8 bytes and `X'…'` supplies arbitrary bytes.

Calling a function on a stable key of the wrong Redis type returns NULL, or `false` for the Boolean existence and membership functions. A key that expires, changes type, or produces an oversized result during evaluation is indeterminate instead; see [Results and limits](/docs/tools/sql-query/results-and-limits).

## Collection mutation functions

Mutation functions are available only as the complete right-hand side of `SET value = ...` in an `UPDATE`. Their first argument must be the bare `value` column. Remaining arguments are evaluated against the original row.

| Function signature                                             | Redis type | Effect                                                 |
| -------------------------------------------------------------- | ---------- | ------------------------------------------------------ |
| `HASH_SET(value, field, item [, field, item ...])`             | hash       | Set one or more field/value pairs.                     |
| `HASH_DELETE(value, field [, field ...])`                      | hash       | Delete one or more fields.                             |
| `HASH_EXPIRE(value, field, ttl_ms_or_NULL)`                    | hash       | Set, clear, or immediately expire one field's TTL.     |
| `LIST_PUSH(value, 'head'/'tail', item [, item ...])`           | list       | Push values at the selected end.                       |
| `LIST_POP(value, 'head'/'tail')`                               | list       | Pop one value from the selected end.                   |
| `LIST_SET(value, index, item)`                                 | list       | Replace the item at a Redis index.                     |
| `LIST_INSERT(value, 'before'/'after', pivot, item)`            | list       | Insert relative to an exact pivot value.               |
| `LIST_REMOVE(value, count, item)`                              | list       | Remove occurrences using Redis `LREM` count semantics. |
| `LIST_TRIM(value, start, stop)`                                | list       | Keep the inclusive Redis index range.                  |
| `SET_ADD(value, member [, member ...])`                        | set        | Add one or more exact members.                         |
| `SET_REMOVE(value, member [, member ...])`                     | set        | Remove one or more exact members.                      |
| `ZSET_SET(value, member, score [, member, score ...])`         | zset       | Add or replace member scores.                          |
| `ZSET_INCREMENT(value, member, delta)`                         | zset       | Increment one member by a finite number.               |
| `ZSET_REMOVE(value, member [, member ...])`                    | zset       | Remove one or more members.                            |
| `STREAM_ADD(value, id, field, item [, field, item ...])`       | stream     | Append an entry at `id`, including `*`.                |
| `STREAM_DELETE(value, id [, id ...])`                          | stream     | Delete one or more entry IDs.                          |
| `STREAM_TRIM_MAXLEN(value, max_len [, approximate [, limit]])` | stream     | Trim by maximum length.                                |
| `STREAM_TRIM_MINID(value, min_id [, approximate [, limit]])`   | stream     | Trim entries older than an ID.                         |
| `JSON_SET(value, path, json)`                                  | RedisJSON  | Store one JSON-encoded value at a path.                |
| `JSON_DELETE(value, path)`                                     | RedisJSON  | Delete a path.                                         |
| `JSON_ARRAY_APPEND(value, path, json [, json ...])`            | RedisJSON  | Append JSON-encoded values to an array.                |
| `JSON_ARRAY_INSERT(value, path, index, json [, json ...])`     | RedisJSON  | Insert JSON-encoded values at an array index.          |
| `JSON_ARRAY_POP(value, path, index)`                           | RedisJSON  | Remove the array item at an index.                     |

Fields, items, members, IDs, paths, and JSON payloads accept text or binary scalar expressions. Text is encoded as UTF-8 and `X'…'` supplies arbitrary bytes. Indexes, counts, lengths, and optional stream trim limits require integer expressions; sorted-set scores and increments accept finite Integer or Real values. The optional stream `approximate` argument is Boolean.

`HASH_EXPIRE(..., NULL)` persists the field. A positive field TTL sets a relative lifetime in milliseconds, while zero or a negative value expires the field immediately. It requires server support for hash-field expiry. JSON mutations require RedisJSON, and their `json` arguments must contain valid JSON text.

```sql
UPDATE redis
SET value = SET_ADD(value, 'admin', 'verified')
WHERE key = 'user:42:roles' AND type = 'set'
RETURNING key, CARDINALITY(value) AS role_count;
```

```sql
UPDATE redis
SET value = STREAM_TRIM_MAXLEN(value, 10000, TRUE, 1000)
WHERE key LIKE 'events:%' AND type = 'stream'
LIMIT 50;
```

A wrong-type mutation fails the current key and stops the statement. It never converts or recreates the key as another type. See [Update keys with SQL](/docs/tools/sql-query/update-keys#results-and-errors) for commit and error behavior.

## CARDINALITY

```sql
CARDINALITY(value) -> integer | NULL
```

Returns a string's byte length; a hash, list, set, or sorted set's item count; a stream's entry count; or the child count of a top-level JSON object or array. A top-level JSON scalar and an unknown type return NULL.

```sql
SELECT key, type, CARDINALITY(value) AS size
FROM redis
WHERE CARDINALITY(value) >= 100
ORDER BY size DESC
LIMIT 50;
```

## HASH_GET

```sql
HASH_GET(value, field) -> binary | NULL
```

Returns the exact bytes stored at `field`. It returns NULL when the field is absent or the key is not a hash.

```sql
SELECT key, HASH_GET(value, 'email') AS email
FROM redis
WHERE type = 'hash'
  AND HASH_GET(value, 'email') LIKE '%@example.com'
LIMIT 100;
```

## HASH_HAS

```sql
HASH_HAS(value, field) -> boolean
```

Returns `true` when the hash contains `field`. An absent field or a stable non-hash value returns `false`.

```sql
SELECT key
FROM redis
WHERE type = 'hash'
  AND HASH_HAS(value, 'last_login')
LIMIT 100;
```

## LIST_GET

```sql
LIST_GET(value, index) -> binary | NULL
```

Returns one list item using Redis indexing. Index `0` is the first item and negative indexes count from the end. An out-of-range index or stable non-list value returns NULL.

```sql
SELECT key, LIST_GET(value, 0) AS first, LIST_GET(value, -1) AS last
FROM redis
WHERE type = 'list'
LIMIT 100;
```

## LIST_CONTAINS

```sql
LIST_CONTAINS(value, item) -> boolean
```

Returns `true` when a list contains the exact byte value. A stable non-list value returns `false`. The connected server must support Redis `LPOS`; Varc reports the function as unsupported before execution when it does not.

```sql
SELECT key
FROM redis
WHERE type = 'list'
  AND LIST_CONTAINS(value, X'00646F6E65')
LIMIT 100;
```

## SET_CONTAINS

```sql
SET_CONTAINS(value, member) -> boolean
```

Tests exact set membership. A missing member or stable non-set value returns `false`.

```sql
SELECT key
FROM redis
WHERE type = 'set'
  AND SET_CONTAINS(value, 'admin')
LIMIT 100;
```

## ZSET_SCORE

```sql
ZSET_SCORE(value, member) -> real | NULL
```

Returns a finite sorted-set score. A missing member or stable non-sorted-set value returns NULL.

```sql
SELECT key, ZSET_SCORE(value, 'daniel') AS score
FROM redis
WHERE type = 'zset'
  AND ZSET_SCORE(value, 'daniel') >= 100
ORDER BY score DESC
LIMIT 50;
```

## STREAM_CONTAINS

```sql
STREAM_CONTAINS(value, field) -> boolean
STREAM_CONTAINS(value, field, expected) -> boolean
```

Searches bounded `XRANGE` windows. With two arguments it returns `true` when any entry contains `field`; with three, the same entry must contain the exact field/value pair. A stable non-stream value returns `false`.

False is final only after the stream traversal completes. A large stream can pause at a segment budget; choose **Continue** to resume from the retained stream position.

```sql
SELECT key
FROM redis
WHERE type = 'stream'
  AND STREAM_CONTAINS(value, 'status', 'failed')
LIMIT 100;
```

## JSON_VALUE

```sql
JSON_VALUE(value, '$.path') -> json scalar | NULL
```

The path must be a constant valid JSONPath beginning with `$`. The result preserves JSON null, Boolean, string, integer, or real type. A missing path, multiple matches, an object or array result, or a stable non-JSON value returns SQL NULL.

The connected Redis server must provide RedisJSON. If it does not, a query that uses `JSON_VALUE` fails its capability check instead of pretending the data is absent.

```sql
SELECT key, JSON_VALUE(value, '$.age') AS age
FROM redis
WHERE type = 'json'
  AND JSON_VALUE(value, '$.age') > 30
ORDER BY age DESC NULLS LAST
LIMIT 50;
```

## COUNT

```sql
COUNT(*) -> integer
COUNT(expr) -> integer
```

`COUNT(*)` counts source rows that pass `WHERE`. `COUNT(expr)` counts non-NULL arguments. Empty global input returns `0`. NULL aggregate inputs are ignored. An indeterminate argument poisons only that aggregate.

```sql
SELECT type, COUNT(*) AS keys
FROM redis
GROUP BY type
ORDER BY keys DESC
LIMIT 20;
```

## SUM

```sql
SUM(expr) -> integer | real | NULL
```

Adds numeric arguments with checked integer or finite real arithmetic. Empty grouped input yields no row; empty global input returns NULL.

## AVG

```sql
AVG(expr) -> real | NULL
```

Averages numeric arguments from retained sum and count. Empty input returns NULL.

## MIN and MAX

```sql
MIN(expr) -> scalar | NULL
MAX(expr) -> scalar | NULL
```

Compare non-opaque scalars with the same rules as `ORDER BY`. Opaque collections are rejected. Empty input returns NULL.

Unsupported aggregate modifiers include `DISTINCT`, `FILTER`, ordered or named arguments, nesting, and `OVER`.

Use type-aware SQL functions to inspect and mutate Redis collections, streams, RedisJSON values, and grouped rows.

Varc SQL keeps every collection in one row. Type-aware functions read a bounded scalar fact from the row’s value without flattening the collection. Aggregates collapse those source rows after WHERE.

Function	Redis type	Result
`CARDINALITY(value)`	string, hash, list, set, zset, stream, JSON	Integer or NULL
`HASH_GET(value, field)`	hash	Binary or NULL
`HASH_HAS(value, field)`	hash	Boolean
`LIST_GET(value, index)`	list	Binary or NULL
`LIST_CONTAINS(value, item)`	list	Boolean
`SET_CONTAINS(value, member)`	set	Boolean
`ZSET_SCORE(value, member)`	zset	Real or NULL
`STREAM_CONTAINS(value, field [, expected])`	stream	Boolean
`JSON_VALUE(value, '$.path')`	RedisJSON	JSON scalar or NULL
`COUNT(*)`	any	Integer
`COUNT(expr)`	any scalar	Integer
`SUM(expr)`	numeric scalar	Integer, Real, or NULL
`AVG(expr)`	numeric scalar	Real or NULL
`MIN(expr)` / `MAX(expr)`	non-opaque scalar	Scalar or NULL

The first argument must be the value column. Fields, members, items, expected values, and JSON paths are exact text or binary constants; a list index is an integer constant. Text constants are UTF-8 bytes and X'…' supplies arbitrary bytes.

Calling a function on a stable key of the wrong Redis type returns NULL, or false for the Boolean existence and membership functions. A key that expires, changes type, or produces an oversized result during evaluation is indeterminate instead; see Results and limits.

Collection mutation functions

Mutation functions are available only as the complete right-hand side of SET value = ... in an UPDATE. Their first argument must be the bare value column. Remaining arguments are evaluated against the original row.

Function signature	Redis type	Effect
`HASH_SET(value, field, item [, field, item ...])`	hash	Set one or more field/value pairs.
`HASH_DELETE(value, field [, field ...])`	hash	Delete one or more fields.
`HASH_EXPIRE(value, field, ttl_ms_or_NULL)`	hash	Set, clear, or immediately expire one field’s TTL.
`LIST_PUSH(value, 'head'/'tail', item [, item ...])`	list	Push values at the selected end.
`LIST_POP(value, 'head'/'tail')`	list	Pop one value from the selected end.
`LIST_SET(value, index, item)`	list	Replace the item at a Redis index.
`LIST_INSERT(value, 'before'/'after', pivot, item)`	list	Insert relative to an exact pivot value.
`LIST_REMOVE(value, count, item)`	list	Remove occurrences using Redis `LREM` count semantics.
`LIST_TRIM(value, start, stop)`	list	Keep the inclusive Redis index range.
`SET_ADD(value, member [, member ...])`	set	Add one or more exact members.
`SET_REMOVE(value, member [, member ...])`	set	Remove one or more exact members.
`ZSET_SET(value, member, score [, member, score ...])`	zset	Add or replace member scores.
`ZSET_INCREMENT(value, member, delta)`	zset	Increment one member by a finite number.
`ZSET_REMOVE(value, member [, member ...])`	zset	Remove one or more members.
`STREAM_ADD(value, id, field, item [, field, item ...])`	stream	Append an entry at `id`, including `*`.
`STREAM_DELETE(value, id [, id ...])`	stream	Delete one or more entry IDs.
`STREAM_TRIM_MAXLEN(value, max_len [, approximate [, limit]])`	stream	Trim by maximum length.
`STREAM_TRIM_MINID(value, min_id [, approximate [, limit]])`	stream	Trim entries older than an ID.
`JSON_SET(value, path, json)`	RedisJSON	Store one JSON-encoded value at a path.
`JSON_DELETE(value, path)`	RedisJSON	Delete a path.
`JSON_ARRAY_APPEND(value, path, json [, json ...])`	RedisJSON	Append JSON-encoded values to an array.
`JSON_ARRAY_INSERT(value, path, index, json [, json ...])`	RedisJSON	Insert JSON-encoded values at an array index.
`JSON_ARRAY_POP(value, path, index)`	RedisJSON	Remove the array item at an index.

Fields, items, members, IDs, paths, and JSON payloads accept text or binary scalar expressions. Text is encoded as UTF-8 and X'…' supplies arbitrary bytes. Indexes, counts, lengths, and optional stream trim limits require integer expressions; sorted-set scores and increments accept finite Integer or Real values. The optional stream approximate argument is Boolean.

HASH_EXPIRE(..., NULL) persists the field. A positive field TTL sets a relative lifetime in milliseconds, while zero or a negative value expires the field immediately. It requires server support for hash-field expiry. JSON mutations require RedisJSON, and their json arguments must contain valid JSON text.

UPDATE redis
SET value = SET_ADD(value, 'admin', 'verified')
WHERE key = 'user:42:roles' AND type = 'set'
RETURNING key, CARDINALITY(value) AS role_count;

UPDATE redis
SET value = STREAM_TRIM_MAXLEN(value, 10000, TRUE, 1000)
WHERE key LIKE 'events:%' AND type = 'stream'
LIMIT 50;

A wrong-type mutation fails the current key and stops the statement. It never converts or recreates the key as another type. See Update keys with SQL for commit and error behavior.

CARDINALITY

CARDINALITY(value) -> integer | NULL

Returns a string’s byte length; a hash, list, set, or sorted set’s item count; a stream’s entry count; or the child count of a top-level JSON object or array. A top-level JSON scalar and an unknown type return NULL.

SELECT key, type, CARDINALITY(value) AS size
FROM redis
WHERE CARDINALITY(value) >= 100
ORDER BY size DESC
LIMIT 50;

HASH_GET

HASH_GET(value, field) -> binary | NULL

Returns the exact bytes stored at field. It returns NULL when the field is absent or the key is not a hash.

SELECT key, HASH_GET(value, 'email') AS email
FROM redis
WHERE type = 'hash'
  AND HASH_GET(value, 'email') LIKE '%@example.com'
LIMIT 100;

HASH_HAS

HASH_HAS(value, field) -> boolean

Returns true when the hash contains field. An absent field or a stable non-hash value returns false.

SELECT key
FROM redis
WHERE type = 'hash'
  AND HASH_HAS(value, 'last_login')
LIMIT 100;

LIST_GET

LIST_GET(value, index) -> binary | NULL

Returns one list item using Redis indexing. Index 0 is the first item and negative indexes count from the end. An out-of-range index or stable non-list value returns NULL.

SELECT key, LIST_GET(value, 0) AS first, LIST_GET(value, -1) AS last
FROM redis
WHERE type = 'list'
LIMIT 100;

LIST_CONTAINS

LIST_CONTAINS(value, item) -> boolean

Returns true when a list contains the exact byte value. A stable non-list value returns false. The connected server must support Redis LPOS; Varc reports the function as unsupported before execution when it does not.

SELECT key
FROM redis
WHERE type = 'list'
  AND LIST_CONTAINS(value, X'00646F6E65')
LIMIT 100;

SET_CONTAINS

SET_CONTAINS(value, member) -> boolean

Tests exact set membership. A missing member or stable non-set value returns false.

SELECT key
FROM redis
WHERE type = 'set'
  AND SET_CONTAINS(value, 'admin')
LIMIT 100;

ZSET_SCORE

ZSET_SCORE(value, member) -> real | NULL

Returns a finite sorted-set score. A missing member or stable non-sorted-set value returns NULL.

SELECT key, ZSET_SCORE(value, 'daniel') AS score
FROM redis
WHERE type = 'zset'
  AND ZSET_SCORE(value, 'daniel') >= 100
ORDER BY score DESC
LIMIT 50;

STREAM_CONTAINS

STREAM_CONTAINS(value, field) -> boolean
STREAM_CONTAINS(value, field, expected) -> boolean

Searches bounded XRANGE windows. With two arguments it returns true when any entry contains field; with three, the same entry must contain the exact field/value pair. A stable non-stream value returns false.

False is final only after the stream traversal completes. A large stream can pause at a segment budget; choose Continue to resume from the retained stream position.

SELECT key
FROM redis
WHERE type = 'stream'
  AND STREAM_CONTAINS(value, 'status', 'failed')
LIMIT 100;

JSON_VALUE

JSON_VALUE(value, '$.path') -> json scalar | NULL

The path must be a constant valid JSONPath beginning with $. The result preserves JSON null, Boolean, string, integer, or real type. A missing path, multiple matches, an object or array result, or a stable non-JSON value returns SQL NULL.

The connected Redis server must provide RedisJSON. If it does not, a query that uses JSON_VALUE fails its capability check instead of pretending the data is absent.

SELECT key, JSON_VALUE(value, '$.age') AS age
FROM redis
WHERE type = 'json'
  AND JSON_VALUE(value, '$.age') > 30
ORDER BY age DESC NULLS LAST
LIMIT 50;

COUNT

COUNT(*) -> integer
COUNT(expr) -> integer

COUNT(*) counts source rows that pass WHERE. COUNT(expr) counts non-NULL arguments. Empty global input returns 0. NULL aggregate inputs are ignored. An indeterminate argument poisons only that aggregate.

SELECT type, COUNT(*) AS keys
FROM redis
GROUP BY type
ORDER BY keys DESC
LIMIT 20;

SUM

SUM(expr) -> integer | real | NULL

Adds numeric arguments with checked integer or finite real arithmetic. Empty grouped input yields no row; empty global input returns NULL.

AVG

AVG(expr) -> real | NULL

Averages numeric arguments from retained sum and count. Empty input returns NULL.

MIN and MAX

MIN(expr) -> scalar | NULL
MAX(expr) -> scalar | NULL

Compare non-opaque scalars with the same rules as ORDER BY. Opaque collections are rejected. Empty input returns NULL.

Unsupported aggregate modifiers include DISTINCT, FILTER, ordered or named arguments, nesting, and OVER.