JSON functions

Devart ODBC Driver for PostgreSQL supports the following JSON and JSONB functions for building, inspecting, converting, and updating JSON data.
Most functions have both json and jsonb variants with the same behavior but different storage formats.

JSON construction and conversion

Function	Return type	Description
`to_json(anyelement)` / `to_jsonb(anyelement)`	`json` / `jsonb`	Converts any SQL value (scalar, array, composite) to JSON/JSONB. Multidimensional arrays become nested JSON arrays.
`array_to_json(anyarray [, pretty boolean])`	`json`	Converts an SQL array to a JSON array. When `pretty` is `true`, formats the result with line breaks and indentation.
`json_array(...)`	`json`	Constructs a JSON array from a value list or from a single-column subquery. Supports `ABSENT ON NULL` to omit `NULL` items.
`row_to_json(record [, pretty boolean])`	`json`	Converts a composite (row) value to a JSON object. The optional flag controls pretty printing.
`json_build_array(VARIADIC any)` / `jsonb_build_array(VARIADIC any)`	`json` / `jsonb`	Builds a JSON/JSONB array from a variadic list of values.
`json_build_object(VARIADIC any)` / `jsonb_build_object(VARIADIC any)`	`json` / `jsonb`	Builds a JSON/JSONB object from variadic key/value arguments; keys are coerced to text.
`json_object(...)` / `jsonb_object(...)`	`json` / `jsonb`	Constructs a JSON object from explicit key/value expressions or from arrays. Supports `ON NULL`, `WITH UNIQUE KEYS`, and `RETURNING` options.
`json_object(text[])`	`json`	Builds a JSON object from a single text array interpreted as alternating key/value pairs.
`json_object(keys text[], values text[])` / `jsonb_object(keys text[], values text[])`	`json` / `jsonb`	Builds a JSON/JSONB object from separate arrays of keys and values.

JSON array and object inspection

Function	Return type	Description
`json_array_elements(json)` / `jsonb_array_elements(jsonb)`	`setof json` / `setof jsonb`	Expands a top-level JSON array into a set of JSON/JSONB values, one per row.
`json_array_elements_text(json)` / `jsonb_array_elements_text(jsonb)`	`setof text`	Expands a top-level JSON array into a set of text values.
`json_array_length(json)` / `jsonb_array_length(jsonb)`	`integer`	Returns the number of elements in a top-level JSON array.
`json_each(json)` / `jsonb_each(jsonb)`	`setof (key text, value json/jsonb)`	Expands a JSON object into a set of key/value rows.
`json_each_text(json)` / `jsonb_each_text(jsonb)`	`setof (key text, value text)`	Similar to `json_each`, but returns values as text.
`json_object_keys(json)` / `jsonb_object_keys(jsonb)`	`setof text`	Returns the keys of a top-level JSON object as a set of text values.

Function	Return type	Description
`json_extract_path(from_json json, VARIADIC path_elems text[])` / `jsonb_extract_path(...)`	`json` / `jsonb`	Extracts a nested element using a text path (equivalent to the `#>` operator).
`json_extract_path_text(from_json json, VARIADIC path_elems text[])` / `jsonb_extract_path_text(...)`	`text`	Same as `json_extract_path`, but returns the result as text (equivalent to the `#>>` operator).

JSON ↔ record mapping

Function	Return type	Description
`json_populate_record(base anyelement, from_json json)` / `jsonb_populate_record(...)`	`anyelement`	Populates a composite (record) value from a JSON object using the `base` value as a type template.
`json_populate_recordset(base anyelement, from_json json)` / `jsonb_populate_recordset(...)`	`setof anyelement`	Populates multiple composite rows from a JSON array of objects using the `base` type as a template.
`json_to_record(json)` / `jsonb_to_record(jsonb)`	`record`	Converts a JSON object to a single record; the target columns and types are declared inline in the `AS` clause.
`json_to_recordset(json)` / `jsonb_to_recordset(jsonb)`	`setof record`	Converts a JSON array of objects to multiple records; the output structure is declared inline in the `AS` clause.

JSON modification (JSONB)

Function	Return type	Description
`jsonb_set(target jsonb, path text[], new_value jsonb [, create_if_missing boolean])`	`jsonb`	Sets or replaces the value at the specified path. When `create_if_missing` is `true`, missing keys or array elements are created. Negative indexes address elements from the end of arrays.
`jsonb_set_lax(target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text]])`	`jsonb`	Variant of `jsonb_set` with additional control over how `NULL` `new_value` is handled (`raise_exception`, `use_json_null`, `delete_key`, or `return_target`).
`jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])`	`jsonb`	Inserts a new value into a JSONB object or array at the specified path. For arrays, `insert_after` controls whether the insertion occurs before or after the given index.
`json_strip_nulls(json)` / `jsonb_strip_nulls(jsonb)`	`json` / `jsonb`	Removes all object fields with JSON `null` values (recursively); `null` elements inside arrays are kept.

JSON path (jsonpath) functions (JSONB)

Function	Return type	Description
`jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent boolean]])`	`boolean`	Returns `true` if the jsonpath expression returns at least one item for `target`. Optional `vars` provides variable bindings; `silent` can suppress certain errors.
`jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent boolean]])`	`boolean`	Evaluates a jsonpath predicate and returns a SQL boolean (or `NULL` if the result is not boolean).
`jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent boolean]])`	`setof jsonb`	Returns all JSONB items matched by the given jsonpath expression as a set of rows.
`jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb [, silent boolean]])`	`jsonb`	Returns all items matched by the jsonpath expression as a single JSONB array.
`jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb [, silent boolean]])`	`jsonb`	Returns the first JSONB item matched by the jsonpath expression, or `NULL` if there is no match.
`jsonb_path_exists_tz(...)` / `jsonb_path_match_tz(...)` / `jsonb_path_query_tz(...)` / `jsonb_path_query_array_tz(...)` / `jsonb_path_query_first_tz(...)`	varies	Time zone-aware variants of the jsonpath functions that support datetime comparisons using time zone rules.

JSON utility functions

Function	Return type	Description
`jsonb_pretty(jsonb)`	`text`	Returns a pretty-printed (indented) textual representation of a JSONB value.
`json_typeof(json)` / `jsonb_typeof(jsonb)`	`text`	Returns the type of the top-level JSON value: `object`, `array`, `string`, `number`, `boolean`, or `null`.