PostgreSQL JSON Functions

The PostgreSQL array_to_json() function converts an SQL array to a JSON array and returns the JSON array.

The PostgreSQL json_array_elements() function expands the top-level JSON array into a set of JSON values.

The PostgreSQL json_array_elements_text() function expands the top-level JSON array into a set of text values.

The PostgreSQL json_array_length() function returns the length (the number of top-level elements in the array) of a specified JSON array.

The PostgreSQL json_build_array() function creates and returns a JSON array of possibly heterogeneous types from a variadic parameter list.

The PostgreSQL json_build_object() function creates and returns a JSON object from a variadic parameter list consisting of alternating keys and values.

The PostgreSQL json_each() function expands a specified JSON object into a set of key-value pairs (the keys are of type text and the values ​​are of type JSON).

Expands a top-level JSON object into rows with text keys and text values.

The PostgreSQL json_extract_path() function extracts JSON nested value from a specified JSON value according to the specified path.

Extracts a JSON value by path components and returns it as text; missing paths yield NULL.

The PostgreSQL json_object() function builds a JSON object from a text array, or from two arrays as keys and values respectively.

The PostgreSQL json_object_keys() function returns a set of keys in the specified top-level JSON object.

The PostgreSQL json_populate_record() function converts the specified top-level JSON object to a specified SQL type value.

The PostgreSQL json_populate_recordset() function converts the specified top-level JSON array to a set of specified SQL-typed values.

Recursively remove null-valued object fields from PostgreSQL json; PostgreSQL 18+ can also strip null array elements.

The PostgreSQL json_to_record() function expands the specified top-level JSON object into a row with the corresponding type defined in the AS clause.

Expands a JSON array of objects into rows whose columns are declared in the query’s AS clause.

The PostgreSQL json_typeof() function returns the type of the specified JSON value as a string.

The PostgreSQL jsonb_array_elements() function expands the top-level JSONB array into a set of JSONB values.

The PostgreSQL jsonb_array_elements_text() function expands the top-level JSONB array into a set of text values.

The PostgreSQL jsonb_array_length() function returns the length (the number of top-level elements in the array) of a specified JSONB array.

The PostgreSQL jsonb_build_array() function creates and returns a JSONB array of possibly heterogeneous types from a variadic parameter list.

The PostgreSQL jsonb_build_object() function creates and returns a JSONB object from a variadic parameter list consisting of alternating keys and values.

Expands a top-level JSONB object into rows with a text key and a JSONB value.

Expands a top-level JSONB object into rows with text keys and text values.

The PostgreSQL jsonb_extract_path() function extracts JSONB nested value from a specified JSONB value according to the specified path.

Extracts a JSONB value by path components and returns it as text; missing paths yield NULL.

Inserts JSONB before or after a path target; for object fields, it inserts only when the key is absent.

The PostgreSQL jsonb_object() function builds a JSONB object from a text array, or from two arrays as keys and values respectively.

The PostgreSQL jsonb_object_keys() function returns a set of keys in the specified top-level JSONB object.

The PostgreSQL jsonb_path_exists() function checks whether a specified path returns a value in a given JSON.

Checks whether a JSONPath expression returns any item, including paths that need time zone-aware date/time conversion.

The PostgreSQL jsonb_path_match() function returns the result of executing a JSON path assertion against a specified JSON value.

Evaluates a JSONPath predicate to a SQL boolean, supporting date/time comparisons that need time zone-aware conversion.

The PostgreSQL jsonb_path_query() function fetches the values ​​in a given JSON according to the specified path and returns all matching values ​​as a set.

The PostgreSQL jsonb_path_query_array() fetches the values ​​in a given JSON according to the specified path and returns all matching values ​​as an array.

Returns JSONPath matches as a JSONB array and supports date/time comparisons that need time zone-aware conversion.

The PostgreSQL jsonb_path_query_first() function gets the value in a given JSON according to the specified path and returns the first matching value.

Returns the first JSONPath match as JSONB and supports date/time comparisons that need time zone-aware conversion.

Returns all JSONPath matches as a set and supports date/time comparisons that need time zone-aware conversion.

The PostgreSQL jsonb_populate_record() function converts the specified top-level JSONB object to a specified SQL type value.

The PostgreSQL jsonb_populate_recordset() function converts the specified top-level JSONB array to a set of specified SQL-typed values.

The PostgreSQL jsonb_pretty() function uses whitespace indentation and newlines to convert a given JSONB value into a formatted, more readable text.

Replaces a JSONB value at a path or creates the final path item when it is missing and create_if_missing is true.

Sets a JSONB value at a path and handles SQL NULL input by writing JSON null, deleting the key, raising an error, or returning the target.

Recursively remove null-valued object fields from PostgreSQL jsonb and return jsonb; PostgreSQL 18+ can also strip null array elements.

The PostgreSQL jsonb_to_record() function expands the specified top-level JSONB object into a row with the corresponding type defined in the AS clause.

Expands a JSONB array of objects into rows whose columns are declared in the query’s AS clause.

The PostgreSQL jsonb_typeof() function returns the type of the specified JSONB value as a string.

The PostgreSQL row_to_json() function converts a value of an SQL composite type to a JSON object and returns it.

The PostgreSQL to_json() function converts a SQL value of to a JSON value and returns the result.

The PostgreSQL to_jsonb() function converts a SQL value to a JSONB value and returns the result.