Automate schema migrations using DizzleORM and GitHub Actions - Manage thousands of tenants with this workflow
15.2kLog InSign Up
PostgreSQL Tutorial/JSON Functions

PostgreSQL JSON Functions

This page provides you with the most commonly used PostgreSQL JSON functions that allow you to manage JSON data effectively.

Section 1. Creating JSON data

This section covers the functions that help you create JSON data in SQL:

Section 2. Searching JSON data

This section provides you with functions that search for JSON elements based on JSON paths.

  • JSON path – Show you how to construct simple JSON paths to locate values or elements within a JSON document.
  • jsonb_path_query() – Query data on a JSON document based on a JSON path expression and return matched JSONB data.
  • jsonb_path_query_array() – Query data on a JSON document based on a JSON path expression and return matched elements as a JSON array.
  • jsonb_path_query_first() – Evaluate a JSON path expression against a JSON document and return the first match.
  • jsonb_path_exists() – Return true if a JSON path returns any elements in a JSON document.
  • jsonb_path_match() – Return true if any part of a JSON document matches a JSON path expression or false otherwise.

Section 3. Querying JSON data

This section shows you how to query and extract elements in JSON documents.

  • JSONB operators – Show you how to use JSONB operators to extract and query JSON data effectively.
  • Extracting JSON data – Extract an array element of a JSON array or extract the value associated with a specified key from a JSON object and return the value as a JSONB value or a text string.
  • jsonb_extract_path() – Extract a JSON sub-object from JSONB data at a specified path.
  • jsonb_extract_path_text() – Extract a JSON sub-object as text from JSONB data at a specified path.

Section 4. Modifying JSON data

This section discusses the function that inserts and updates values in JSON documents.

  • jsonb_insert() – Insert a new value into a JSON document based on a path and return the modified JSON document.
  • jsonb_set() – Replace existing values in a JSON document based on a path and return the modified JSON document.
  • jsonb_strip_nulls() – Delete all object fields that have null values from a specified JSON document.

Section 5. Working with JSON arrays

This section introduces you to JSON functions that work with JSON arrays including getting array length and expanding array elements into JSONB values and text.

Section 6. Working with JSON objects

This section shows you how to use JSON functions that handle JSON objects.

  • jsonb_each() – Expand the keys and values of the top-level JSON object into a set of key/value pairs. The values are JSON values.
  • jsonb_each_text() – Expand the top-level JSON object into a set of key/value pairs. The values are of the text type.
  • jsonb_object_keys() –  Return a set of keys in the top-level JSON object.
  • jsonb_to_record() – Convert a top-level JSON object into a PostgreSQL record type defined by an AS clause.

Section 7. Aggregating JSON data

This section shows you how to use JSON aggregate functions that collect data from multiple rows into a JSON array or object.

  • jsonb_agg() – aggregate a list of values including NULL into a JSON array.
  • jsonb_object_agg() – aggregate a list of key/value pairs into a JSON object.

Section 8. JSON utility functions

This section discusses the JSON utility functions for getting types of JSONB values and formats JSON values into a human-readable format.

  • jsonb_typeof() – Return the type of top-level JSON value as a text string.
  • jsonb_pretty() – Format a JSON value into human-readable, indented format, making it easier to read.
PreviousPostgreSQL ROW_NUMBER Function
NextPostgreSQL to_jsonb() Function

Last updated on

Was this page helpful?