Function reference

Every msgpack_* function, with signatures and runnable SQL examples.

Encoding & validation

`msgpack_version()`

Returns the extension version string.

SELECT msgpack_version();  -- '1.2.0'

`msgpack_quote(value)`

Encodes a single SQL value as a msgpack BLOB using the smallest valid format:

SELECT hex(msgpack_quote(NULL));   -- C0
SELECT hex(msgpack_quote(0));      -- 00
SELECT hex(msgpack_quote(127));    -- 7F  (positive fixint)
SELECT hex(msgpack_quote(128));    -- CC80  (uint8)
SELECT hex(msgpack_quote(-32));    -- E0  (negative fixint)
SELECT hex(msgpack_quote(-33));    -- D0DF  (int8)
SELECT hex(msgpack_quote(3.14));   -- CB400...  (float64)
SELECT hex(msgpack_quote('hi'));   -- A268 69  (fixstr)
SELECT hex(msgpack_quote(x'DEAD'));-- C402DEAD  (bin8)

`msgpack_valid(mp)` / `msgpack_valid(mp, path)`

Returns 1 if mp is a well-formed msgpack BLOB, 0 otherwise. With a path argument, returns 1 if the element at that path is well-formed.

SELECT msgpack_valid(msgpack_quote(42));          -- 1
SELECT msgpack_valid(x'FF');                      -- 0  (0xFF is reserved)
SELECT msgpack_valid(msgpack_array(1,2,3), '$[1]'); -- 1

`msgpack_error_position(mp)`

Returns the byte offset (1-based) of the first encoding error in mp, or 0 if the BLOB is valid. Useful for diagnosing corrupt data.

SELECT msgpack_error_position(msgpack_quote(42));  -- 0  (valid)
SELECT msgpack_error_position(x'C1');              -- 1  (0xC1 is never-used byte)

Construction

`msgpack(mp)`

Validates mp and returns it unchanged. Raises an error if mp is not a well-formed msgpack BLOB. Passing NULL returns NULL.

SELECT hex(msgpack(msgpack_array(1,2)));  -- same bytes
SELECT msgpack('not a blob');             -- error

`msgpack_array(v1, v2, ...)`

Returns a msgpack array containing the encoded values. Any number of arguments is accepted (including zero for an empty array). BLOB arguments that are themselves valid msgpack are embedded directly as nested elements; raw BLOBs are stored as bin type.

SELECT msgpack_to_json(msgpack_array(1, 'hello', NULL, 3.14));
-- [1,"hello",null,3.14]

SELECT msgpack_to_json(msgpack_array());
-- []

-- Nested array
SELECT msgpack_to_json(
  msgpack_array(msgpack_array(1,2), msgpack_array(3,4))
);
-- [[1,2],[3,4]]

`msgpack_object(key1, val1, key2, val2, ...)`

Returns a msgpack map. Arguments must appear in key/value pairs; keys must be TEXT. Duplicate keys are allowed (last writer wins on extraction, matching JSON1 behaviour). Raises an error if an odd number of arguments is given.

SELECT msgpack_to_json(msgpack_object('x', 1, 'y', 2));
-- {"x":1,"y":2}

-- Nested map
SELECT msgpack_to_json(
  msgpack_object('user', msgpack_object('name', 'Bob', 'age', 25))
);
-- {"user":{"name":"Bob","age":25}}

Extraction

`msgpack_extract(mp, path)` / `msgpack_extract(mp, path1, path2, ...)`

Returns the element at path as a SQL value. Arrays and maps are returned as BLOBs. A missing path returns NULL.

With two or more path arguments, returns a new msgpack array whose elements correspond to each path in order. Missing paths produce nil elements in the result array.

SELECT msgpack_extract(msgpack_object('a',1,'b',2), '$.a');  -- 1
SELECT msgpack_extract(msgpack_array(10,20,30), '$[2]');     -- 30

-- Multi-path → array
SELECT msgpack_to_json(
  msgpack_extract(msgpack_object('a',1,'b',2,'c',3), '$.a','$.c')
);
-- [1,3]

`msgpack_type(mp)` / `msgpack_type(mp, path)`

Returns the type string of the root element or the element at path. See Type system for the full list. Returns NULL if the path does not exist.

SELECT msgpack_type(msgpack_quote(42));                       -- integer
SELECT msgpack_type(msgpack_quote('hi'));                     -- text
SELECT msgpack_type(msgpack_array(1,2), '$[0]');              -- integer
SELECT msgpack_type(msgpack_object('a', msgpack_array(1)));   -- map

`msgpack_array_length(mp)` / `msgpack_array_length(mp, path)`

Returns the number of elements in the array or map at mp (or at path inside mp). Returns NULL for scalar values.

SELECT msgpack_array_length(msgpack_array(1,2,3));    -- 3
SELECT msgpack_array_length(msgpack_object('a',1));   -- 1
SELECT msgpack_array_length(msgpack_quote(99));        -- NULL
SELECT msgpack_array_length(
  msgpack_object('arr', msgpack_array(10,20,30)), '$.arr'
);                                                    -- 3

Mutation

All mutation functions are copy-on-write: they return a new msgpack BLOB and leave the original unchanged.

Each function accepts multiple path, value pairs in a single call, applied left to right.

`msgpack_set(mp, path, val, ...)`

Inserts or replaces the element at each path. If the path does not exist, it is created (if the parent exists). Equivalent to JSON1's json_set.

SELECT msgpack_to_json(msgpack_set(msgpack_object('a',1), '$.b', 2));
-- {"a":1,"b":2}

SELECT msgpack_to_json(msgpack_set(msgpack_object('a',1), '$.a', 99));
-- {"a":99}

`msgpack_insert(mp, path, val, ...)`

Inserts only — no-op when the path already exists.

SELECT msgpack_to_json(msgpack_insert(msgpack_object('a',1), '$.a', 99));
-- {"a":1}  (unchanged — 'a' exists)

SELECT msgpack_to_json(msgpack_insert(msgpack_object('a',1), '$.b', 2));
-- {"a":1,"b":2}

`msgpack_replace(mp, path, val, ...)`

Replaces only — no-op when the path does not exist.

SELECT msgpack_to_json(msgpack_replace(msgpack_object('a',1), '$.b', 2));
-- {"a":1}  (unchanged — 'b' missing)

SELECT msgpack_to_json(msgpack_replace(msgpack_object('a',1), '$.a', 99));
-- {"a":99}

`msgpack_remove(mp, path, ...)`

Removes each element at path. Missing paths are silently ignored.

SELECT msgpack_to_json(msgpack_remove(msgpack_object('a',1,'b',2), '$.a'));
-- {"b":2}

SELECT msgpack_to_json(msgpack_remove(msgpack_array(10,20,30), '$[1]'));
-- [10,30]

`msgpack_array_insert(mp, path, val, ...)`

Inserts val before the element at the array index specified in path. Use $[#] to append to the end of the array.

SELECT msgpack_to_json(msgpack_array_insert(msgpack_array(1,3), '$[1]', 2));
-- [1,2,3]

SELECT msgpack_to_json(msgpack_array_insert(msgpack_array(1,2), '$[#]', 3));
-- [1,2,3]

`msgpack_patch(mp, patch)`

Applies an RFC 7386 merge-patch to mp. patch must be a msgpack map. Keys in patch whose values are nil remove the corresponding key from mp; all other keys are set.

SELECT msgpack_to_json(
  msgpack_patch(
    msgpack_from_json('{"a":1,"b":2}'),
    msgpack_from_json('{"b":null,"c":3}')
  )
);
-- {"a":1,"c":3}

JSON conversion

`msgpack_from_json(json_text)`

Parses a JSON text string and returns the equivalent msgpack BLOB. Supports all JSON value types: null, true, false, numbers, strings, arrays, and objects.

SELECT hex(msgpack_from_json('null'));        -- C0
SELECT hex(msgpack_from_json('true'));        -- C3
SELECT hex(msgpack_from_json('false'));       -- C2
SELECT hex(msgpack_from_json('42'));          -- 2A
SELECT hex(msgpack_from_json('"hello"'));     -- A568656C6C6F
SELECT msgpack_to_json(msgpack_from_json('[1,2,3]'));       -- [1,2,3]
SELECT msgpack_to_json(msgpack_from_json('{"a":1}'));       -- {"a":1}

`msgpack_to_json(mp)` / `msgpack_to_jsonb(mp)` (alias)

Serializes a msgpack BLOB to a JSON text string. Type mapping:

msgpack type	JSON output
nil	`null`
false	`false`
true	`true`
integer	number
float32 / float64	number (`null` for NaN/Infinity)
text (UTF-8)	`"string"` with JSON escaping
bin	lowercase hex string (e.g. `"deadbeef"`)
array	`[...]`
map	`{...}`
ext	`null`
timestamp (ext −1)	`null` (use `msgpack_timestamp_s/ns` for numeric access)

SELECT msgpack_to_json(msgpack_array(1, 'hi', NULL, true));
-- [1,"hi",null,true]   -- note: SQL TRUE is integer 1, not msgpack true

`msgpack_pretty(mp)` / `msgpack_pretty(mp, indent)`

Returns a multi-line, indented JSON string. indent controls the number of spaces per level (default 2). Useful for debugging stored BLOBs.

SELECT msgpack_pretty(msgpack_from_json('{"a":1,"b":[2,3]}'));
-- {
--   "a": 1,
--   "b": [
--     2,
--     3
--   ]
-- }

Aggregates

Both aggregate functions also work as window functions with an OVER clause.

`msgpack_group_array(value)`

Accumulates values from every row in the group into a single msgpack array.

CREATE TABLE scores(player TEXT, score INTEGER);
INSERT INTO scores VALUES ('Alice',10),('Bob',20),('Carol',30);

SELECT msgpack_to_json(msgpack_group_array(score)) FROM scores;
-- [10,20,30]

-- As a window function
SELECT player,
       msgpack_to_json(msgpack_group_array(score) OVER ()) AS all_scores
FROM scores;

`msgpack_group_object(key, value)`

Accumulates key/value pairs into a single msgpack map. Later rows with a duplicate key overwrite earlier ones.

SELECT msgpack_to_json(msgpack_group_object(player, score)) FROM scores;
-- {"Alice":10,"Bob":20,"Carol":30}

Table-valued functions

Table-valued functions expand a msgpack BLOB into a result set. Both functions emit one row per element and share the same column schema:

Column	Type	Description
`key`	TEXT or INTEGER	Map key (text) or array index (integer)
`value`	any	The element value as a SQL scalar; arrays/maps as BLOBs
`type`	TEXT	Type string (see Type system)
`atom`	any	Scalar value; `NULL` for arrays and maps
`id`	INTEGER	Unique node identifier within this traversal
`parent`	INTEGER	`id` of the parent node; `NULL` for the root
`fullkey`	TEXT	Full path expression to this element (e.g. `$.a[2]`)
`path`	TEXT	Path to the parent container

`msgpack_each(mp)` / `msgpack_each(mp, path)`

Iterates the direct children of the root element (or of the element at path). Does not recurse into nested arrays or maps.

SELECT key, value, type
FROM msgpack_each(msgpack_object('a', 1, 'b', 'hello', 'c', NULL));

key	value	type
a	1	integer
b	hello	text
c	NULL	null

-- Iterate an array
SELECT key, value FROM msgpack_each(msgpack_array(10, 20, 30));
-- 0 | 10
-- 1 | 20
-- 2 | 30

-- Start at a nested path
SELECT key, value
FROM msgpack_each(msgpack_object('arr', msgpack_array(1,2,3)), '$.arr');
-- 0 | 1
-- 1 | 2
-- 2 | 3

`msgpack_tree(mp)` / `msgpack_tree(mp, path)`

Recursively traverses all nodes in the subtree rooted at mp (or at path), in depth-first pre-order. Unlike msgpack_each, it descends into nested containers.

SELECT fullkey, type, atom
FROM msgpack_tree(msgpack_from_json('{"x":[1,2],"y":3}'));

fullkey	type	atom
`$`	map	NULL
`$.x`	array	NULL
`$.x[0]`	integer	1
`$.x[1]`	integer	2
`$.y`	integer	3

-- Count all leaf nodes (non-containers) in a nested structure
SELECT count(*)
FROM msgpack_tree(msgpack_from_json('{"a":{"b":[1,2,3]}}'))
WHERE type NOT IN ('array','map');
-- 3

Typed constructors

These functions create msgpack BLOBs with explicit control over the encoded type and width, bypassing the automatic "smallest encoding" and auto-embed rules used by msgpack_quote.

`msgpack_nil()` / `msgpack_true()` / `msgpack_false()`

Return single-byte msgpack BLOBs for the nil, true, and false constants. Unlike msgpack_quote(NULL), these are explicit MessagePack types.

SELECT hex(msgpack_nil());    -- C0
SELECT hex(msgpack_true());   -- C3
SELECT hex(msgpack_false());  -- C2

`msgpack_bool(value)`

Converts an integer to a msgpack boolean: 0 → false (0xC2), non-zero → true (0xC3).

SELECT hex(msgpack_bool(1));   -- C3  (true)
SELECT hex(msgpack_bool(0));   -- C2  (false)

`msgpack_float32(value)`

Encodes a number as a 32-bit IEEE 754 float (5 bytes), regardless of whether a more compact encoding exists.

SELECT hex(msgpack_float32(3.14));  -- CA4048F5C3
SELECT hex(msgpack_float32(0));     -- CA00000000

`msgpack_int8(value)` / `msgpack_int16(value)` / `msgpack_int32(value)`

Encode a signed integer in the specified fixed width. Raises an error if the value is out of range for the requested type.

Function	Range	msgpack bytes
`msgpack_int8`	−128 to 127	2
`msgpack_int16`	−32 768 to 32 767	3
`msgpack_int32`	−2 147 483 648 to 2 147 483 647	5

SELECT hex(msgpack_int8(-1));     -- D0FF
SELECT hex(msgpack_int16(1000));  -- D103E8
SELECT hex(msgpack_int32(100000));-- D2000186A0

`msgpack_uint8(value)` / `msgpack_uint16(value)` / `msgpack_uint32(value)` / `msgpack_uint64(value)`

Encode an unsigned integer in the specified fixed width. Raises an error if the value is out of range.

Function	Range	msgpack bytes
`msgpack_uint8`	0–255	2
`msgpack_uint16`	0–65 535	3
`msgpack_uint32`	0–4 294 967 295	5
`msgpack_uint64`	0–2⁶⁴−1	9

SELECT hex(msgpack_uint8(200));    -- CC C8
SELECT hex(msgpack_uint16(1000));  -- CD03E8
SELECT hex(msgpack_uint64(42));    -- CF000000000000002A

`msgpack_bin(blob)`

Encodes a BLOB as msgpack bin type, bypassing the auto-embed logic. Use this when you want to store raw bytes that happen to be valid msgpack without having them interpreted as a nested element.

-- msgpack_quote auto-embeds valid msgpack; msgpack_bin does not
SELECT hex(msgpack_bin(x'2A'));         -- C4012A  (bin8, 1 byte)
SELECT hex(msgpack_bin(x'DEADBEEF'));   -- C404DEADBEEF

`msgpack_ext(type_code, data)`

Creates a msgpack extension type. type_code is a signed integer in [−128, 127]. Uses fixext formats when the data length is 1, 2, 4, 8, or 16 bytes.

SELECT hex(msgpack_ext(1, x'AABB'));    -- D501AABB  (fixext2, type=1)
SELECT hex(msgpack_ext(42, x'0102'));   -- D52A0102

Timestamp

MessagePack defines an extension type (type code −1) for timestamps. These functions create and decode timestamp values.

`msgpack_timestamp(seconds)`

Creates a timestamp ext from a numeric value. Integer input is treated as whole seconds; real (float) input splits into seconds and nanoseconds. Automatically uses the most compact encoding (ts32, ts64, or ts96).

-- Integer seconds → ts32 (fixext4, 6 bytes)
SELECT hex(msgpack_timestamp(1712345678));  -- D7FF660EA...

-- Real seconds with fractional nanoseconds → ts64 (fixext8, 10 bytes)
SELECT hex(msgpack_timestamp(1712345678.5));

`msgpack_timestamp_s(mp)`

Extracts the whole-seconds component from a msgpack timestamp BLOB.

SELECT msgpack_timestamp_s(msgpack_timestamp(1712345678));     -- 1712345678
SELECT msgpack_timestamp_s(msgpack_timestamp(1712345678.5));   -- 1712345678

`msgpack_timestamp_ns(mp)`

Extracts the nanoseconds component (0–999 999 999) from a msgpack timestamp.

SELECT msgpack_timestamp_ns(msgpack_timestamp(1712345678));    -- 0
SELECT msgpack_timestamp_ns(msgpack_timestamp(1712345678.5));  -- 500000000

Function reference

Encoding & validation

msgpack_version()

msgpack_quote(value)

msgpack_valid(mp) / msgpack_valid(mp, path)

msgpack_error_position(mp)

Construction

msgpack(mp)

msgpack_array(v1, v2, ...)

msgpack_object(key1, val1, key2, val2, ...)

Extraction

msgpack_extract(mp, path) / msgpack_extract(mp, path1, path2, ...)

msgpack_type(mp) / msgpack_type(mp, path)

msgpack_array_length(mp) / msgpack_array_length(mp, path)

Mutation

msgpack_set(mp, path, val, ...)

msgpack_insert(mp, path, val, ...)

msgpack_replace(mp, path, val, ...)

msgpack_remove(mp, path, ...)

msgpack_array_insert(mp, path, val, ...)

msgpack_patch(mp, patch)

JSON conversion

msgpack_from_json(json_text)

msgpack_to_json(mp) / msgpack_to_jsonb(mp) (alias)

msgpack_pretty(mp) / msgpack_pretty(mp, indent)