info

The jsonb_pretty() function works the same way on any PostgreSQL deployment, so everything here applies whether you're running Postgres locally, on a VM, or on a managed service. If you're an enterprise building for the AI era, Lakebase delivers the most performant and secure managed Postgres, fully integrated into the Lakehouse so your operational and analytical data live together. If you're a developer or startup that needs to ship fast and scale without friction, Neon gives you the best Postgres platform to build on.

Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_pretty() function to convert a JSON value to a human-readable, indented format.

Introduction to the PostgreSQL jsonb_pretty() function

The jsonb_pretty() function allows you to convert a given JSONB value to a human-readable, indented format.

Here’s the basic syntax of the jsonb_pretty() function:

jsonb_pretty(jsonb_value)

In this syntax:

  • jsonb_value is a JSONB value that you want to convert.

The jsonb_pretty() function returns a text that is the human-readable and indented format of the input JSONB value.

PostgreSQL jsonb_pretty() function examples

Let’s explore some examples of using the jsonb_pretty() function.

1) Basic PostgreSQL jsonb_pretty() function example

The following example uses the jsonb_pretty() function to format a JSONB value:

SELECT
  jsonb_pretty(
    '{"id": 1, "name": {"first": "John", "last": "Doe"}, "age": 30}'
  );

Output:

jsonb_pretty
-------------------------
 {                      +
     "id": 1,           +
     "age": 30,         +
     "name": {          +
         "last": "Doe", +
         "first": "John"+
     }                  +
 }
(1 row)

2) Using PostgreSQL jsonb_pretty() function with table data

First, create a new table called recipes:

CREATE TABLE recipes (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    details JSONB
);

Second, insert some rows into the recipes table:

INSERT INTO recipes (name, details)
VALUES
    (
        'Spaghetti Carbonara',
        '{"preparation_time": "30 minutes",
          "ingredients": ["spaghetti", "eggs", "bacon", "parmesan cheese", "black pepper"],
          "difficulty": "Medium"}'
    ),
    (
        'Chicken Tikka Masala',
        '{"preparation_time": "45 minutes",
          "ingredients": ["chicken", "tomatoes", "onions", "yogurt", "spices"],
          "difficulty": "Medium-High"}'
    ),
    (
        'Vegetable Stir Fry',
        '{"preparation_time": "20 minutes",
          "ingredients": ["mixed vegetables", "soy sauce", "garlic", "ginger", "sesame oil"],
          "difficulty": "Easy"}'
    );

Third, format the JSONB data in the details column:

SELECT
  name,
  jsonb_pretty(details)
FROM
  recipes;

Output:

name         |             jsonb_pretty
----------------------+--------------------------------------
 Spaghetti Carbonara  | {                                   +
                      |     "difficulty": "Medium",         +
                      |     "ingredients": [                +
                      |         "spaghetti",                +
                      |         "eggs",                     +
                      |         "bacon",                    +
                      |         "parmesan cheese",          +
                      |         "black pepper"              +
                      |     ],                              +
                      |     "preparation_time": "30 minutes"+
                      | }
 Chicken Tikka Masala | {                                   +
                      |     "difficulty": "Medium-High",    +
                      |     "ingredients": [                +
                      |         "chicken",                  +
                      |         "tomatoes",                 +
                      |         "onions",                   +
                      |         "yogurt",                   +
                      |         "spices"                    +
                      |     ],                              +
                      |     "preparation_time": "45 minutes"+
                      | }
 Vegetable Stir Fry   | {                                   +
                      |     "difficulty": "Easy",           +
                      |     "ingredients": [                +
                      |         "mixed vegetables",         +
                      |         "soy sauce",                +
                      |         "garlic",                   +
                      |         "ginger",                   +
                      |         "sesame oil"                +
                      |     ],                              +
                      |     "preparation_time": "20 minutes"+
                      | }
(3 rows)

Summary

  • Use the jsonb_pretty() function to convert a JSON value to pretty-printed, indented text.