Use jsonb_pretty for all printed JSONB in Postgres

Question

Can .psqlrc be configured so that jsonb_pretty is applied to every JSONB column printed to console?

Expanded display automatic mode is very useful: https://stackoverflow.com/a/16108898/1007926

I want to use both jsonb_pretty and expanded display. Does jsonb_pretty work by inserting newlines into the string? I suppose this would this cause problems if applied to every JSONB select.

jsonb_pretty: https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

score 3 · Accepted Answer · answered Jul 18 '19 at 04:29

3

No, it is not possible. psql has not any support for pretty print of JSON types.

answered Jul 18 '19 at 04:29

Pavel Stehule

42,331
5
91
94

Unless I'm misunderstanding, this is no longer correct; psql versions greater than 9.5 support `jsonb_pretty(field_name)`, as the OP seems to be aware. See [this answer](https://stackoverflow.com/a/64230928/9154668). – Andrew Sep 13 '22 at 15:36

bitifet · Answer 2 · 2022-04-07T09:47:38.180

With just psql I guess @Pavel_Stehule is right (so Not).

With SQLTT you can hack it with the use of hooks.

Example:

// users.sql.js

const sqltt = require("sqltt");
const getUserData = new sqltt({                                               
    hooks: {                                                                
        // Prettier formatting on CLI output:                               
        user_profile: (arg, eng) => eng.match(/(^|_)cli$/) && "jsonb_pretty("+arg+") as "+arg,              
    },                                                                      
    sql: $=>$`                                                              
        select id, name, ${$.literal("user_profile")}                       
        from users                                                          
        where id = ${"userId"} 
    `,                                                                      
});
sqltt.publish(module, {getUserData});

Then you can...

Install sqltt:

user@host:~/sqltttest$ npm install sqltt                                    
(...)

List available queries in file:

user@host:~/sqltttest$ node user.sql.js                                     
Available queries:                                                          
✓ getUserData:  (Undocumented)

Get psql suitable query (with arguments already interpolated):

user@host:~/sqltttest$ node user.sql.js getUserData 'John Smith'            
\set userId '''John Smith'''                                                
        select id, name, jsonb_pretty(user_profile) as user_profile         
        from users                                                          
        where id = :userId

...pipe it directly to psql:

user@host:~/sqltttest$ node user.sql.js getUserData 'John Smith' | psql myDatabase
\set userId '''John Smith'''                                                
        select id, name, jsonb_pretty(user_profile) as user_profile         
        from users                                                          
        where id = :userId

Use it from Node.js project:

const {getUserData, ...otherSQL} = require("./user.sql.js");                
const sql = getUserData.sql();                                              
// select id, name, user_profile                                            
// from users                                                               
// where id = $1

Obtain non-cli sql to use with other languages:

user@host:~/sqltttest$ node user.sql.js --engine=_nocli getUserData         
                                                                            
        select id, name, user_profile                                       
        from users                                                          
        where id = $1

...and, of course, store it in plain sql file:

user@host:~/sqltttest$ node user.sql.js --engine=_nocli getUserData > getUserData.sql

Use jsonb_pretty for all printed JSONB in Postgres

2 Answers2