PostgreSQL JSON Functions

This page lists PostgreSQL JSON functions that perform operations on JSON values.

  1. array_to_json

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

  2. json_array_elements

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

  3. json_array_elements_text

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

  4. json_array_length

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

  5. json_build_array

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

  6. json_build_object

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

  7. json_each

    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).

  8. json_each_text

    The PostgreSQL json_each_text() function expands a specified top-level member of a JSON object into a collection of key-value pairs (both keys and values ​​are of type text).

  9. json_extract_path

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

  10. json_extract_path_text

    The PostgreSQL json_extract_path_text() function extracts JSON sub-objects according to the specified path from a specified JSON object and returns the result as text.

  11. json_object

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

  12. json_object_keys

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

  13. json_populate_record

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

  14. json_populate_recordset

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

  15. json_strip_nulls

    The PostgreSQL json_strip_nulls() function removes fields with null values ​​in the specified JSON object recursively, and null values ​​in non-object fields are not processed.

  16. json_to_record

    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.

  17. json_to_recordset

    The PostgreSQL json_to_recordset() function expands the specified top-level JSON array (its elements are objects) into a set of rows with the type defined in the AS clause.

  18. json_typeof

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

  19. jsonb_array_elements

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

  20. jsonb_array_elements_text

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

  21. jsonb_array_length

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

  22. jsonb_build_array

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

  23. jsonb_build_object

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

  24. jsonb_each

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

  25. jsonb_each_text

    The PostgreSQL jsonb_each_text() function expands a specified top-level member of a JSONB object into a collection of key-value pairs (both keys and values ​​are of type text).

  26. jsonb_extract_path

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

  27. jsonb_extract_path_text

    The PostgreSQL jsonb_extract_path_text() function extracts JSONB sub-objects according to the specified path from a specified JSONB object and returns the result as text.

  28. jsonb_insert

    The PostgreSQL jsonb_insert() function inserts a new value into a given JSONB value at the specified path.

  29. jsonb_object

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

  30. jsonb_object_keys

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

  31. jsonb_path_exists

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

  32. jsonb_path_exists_tz

    The PostgreSQL jsonb_path_exists_tz() function checks whether a specified path returns a value in a given JSON. This function differs from jsonb_path_exists() in that it provides support for date/time with time zones.

  33. jsonb_path_match

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

  34. jsonb_path_match_tz

    The PostgreSQL jsonb_path_match_tz() function returns the result of executing a JSON path assertion against a specified JSON value. This function differs from jsonb_path_match() in that it provides support for date/time with time zones.

  35. jsonb_path_query

    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.

  36. jsonb_path_query_array

    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.

  37. jsonb_path_query_array_tz

    The PostgreSQL jsonb_path_query_array_tz() function fetches the values ​​in a given JSON according to the specified path and returns all matching values ​​as an array. This function differs from jsonb_path_query_array_tz() in that it provides support for date/time with time zones.

  38. jsonb_path_query_first

    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.

  39. jsonb_path_query_first_tz

    The PostgreSQL jsonb_path_query_first_tz() function gets the value in a given JSON according to the specified path and returns the first matching value. This function jsonb_path_query_first() differs from in that it provides support for date/time with time zones.

  40. jsonb_path_query_tz

    The PostgreSQL jsonb_path_query_tz() function fetches the values ​​in a given JSON according to the specified path and returns all matching values ​​as a set. This function jsonb_path_query() differs from in that it provides support for date/time with time zones.

  41. jsonb_populate_record

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

  42. jsonb_populate_recordset

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

  43. jsonb_pretty

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

  44. jsonb_set

    The PostgreSQL jsonb_set() function replaces or inserts the value at the specified path.

  45. jsonb_set_lax

    The PostgreSQL jsonb_set_lax() function replaces or inserts the value at the specified path. This function differs from jsonb_set() in the method of handling NULL values.

  46. jsonb_strip_nulls

    The PostgreSQL jsonb_strip_nulls() function removes fields with null values ​​in the specified JSONB object recursively, and null values ​​in non-object fields are not processed.

  47. jsonb_to_record

    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.

  48. jsonb_to_recordset

    The PostgreSQL jsonb_to_recordset() function expands the specified top-level JSONB array (its elements are objects) into a set of rows with the conforming type defined in the AS clause.

  49. jsonb_typeof

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

  50. row_to_json

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

  51. to_json

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

  52. to_jsonb

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