Today I Learned

A Hashrocket project

Pretty Printing JSONB Rows in PostgreSQL

Who needs a document store when you can just use PostgreSQL's JSONB data type? Viewing rows of JSONB output can be challenging though because it defaults to printing them as a single line of text.

> select '{"what": "is this", "nested": {"items 1": "are the best", "items 2": [1, 2, 3]}}'::jsonb;
                                      jsonb
----------------------------------------------------------------------------------
 {"what": "is this", "nested": {"items 1": "are the best", "items 2": [1, 2, 3]}}
(1 row)

Fortunately, Postgres comes with a function for prettying up the format of the output of these rows -- jsonb_pretty

> select jsonb_pretty('{"what": "is this", "nested": {"items 1": "are the best", "items 2": [1, 2, 3]}}'::jsonb);
            jsonb_pretty
------------------------------------
 {                                 +
     "what": "is this",            +
     "nested": {                   +
         "items 1": "are the best",+
         "items 2": [              +
             1,                    +
             2,                    +
             3                     +
         ]                         +
     }                             +
 }
(1 row)

h/t Jack Christensen

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.