Piping psql into jq

The modern hero of shell scripts is jq. Pipe in JSON, find values, store back into variables or create new JSON and work with that.

Unfortunately my data was in PostgreSQL, and the psql output is not JSON. But it would be awesome if it was. Just imagine the power of psql -c 'SELECT somecolumn FROM table;' | jq -r .somecolumn!

A quick look at the problem and then the solutions:

psql ${pg_uri} -c 'CREATE TABLE two_rows (value1 text, value2 text);'
psql ${pg_uri} -c "INSERT INTO two_rows VALUES ('row1-col1','row1-col2');"
psql ${pg_uri} -c "INSERT INTO two_rows VALUES ('row2-col1','row2-col2');"
psql ${pg_uri} -c 'SELECT * FROM two_rows;'

Returns output like:

  value1   |  value2
-----------+-----------
 row1-col1 | row1-col2
 row2-col1 | row2-col2
(2 rows)

We can strip out much of the human-only tables with the psql -t flag:

# psql ${pg_uri} -c 'SELECT * FROM two_rows;' -t
 row1-col1 | row1-col2
 row2-col1 | row2-col2

If we wrap our SELECT query in SELECT row_to_json we get closer:

# psql ${pg_uri} -c 'SELECT row_to_json(t1) FROM (SELECT * FROM two_rows) t1;'
                 row_to_json
---------------------------------------------
 {"value1":"row1-col1","value2":"row1-col2"}
 {"value1":"row2-col1","value2":"row2-col2"}
(2 rows)

Adding back our -t flag gives us one JSON object per row:

# psql ${pg_uri} -c 'SELECT row_to_json(t1) FROM (SELECT * FROM two_rows) t1;' -t
 {"value1":"row1-col1","value2":"row1-col2"}
 {"value1":"row2-col1","value2":"row2-col2"}

Which can then be piped to jq -r .value1:

# psql ${pg_uri} -c 'SELECT row_to_json(t1) FROM (SELECT * FROM two_rows) t1;' -t | jq -r .value1
row1-col1
row2-col1

Which we can then use inside shell scripts:

values=(
  $(psql ${pg_uri} -c 'SELECT row_to_json(t1) FROM (SELECT * FROM two_rows) t1;' -t | jq -r .value1)
)
for val in ${values}; do
  echo "${val}: $(echo $val | wc -c)"
done

Shows the character count for each value return:

row1-col1:       10
row2-col1:       10

System functions

What about piping system functions to jq?

# psql ${pg_uri} -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

Just wrap the SELECT... expression:

# psql ${pg_uri} -c "SELECT row_to_json(t1) FROM (SELECT pg_is_in_recovery()) t1;" -t
 {"pg_is_in_recovery":false}

Oh so close. Now piping it to jq -r .pg_is_in_recovery to get the true or false value:

$ psql ${pg_uri} -c "SELECT row_to_json(t1) FROM (SELECT pg_is_in_recovery()) t1;" -t | jq -r .pg_is_in_recovery
false

A tiny script to see it in action:

in_recovery=$(psql ${pg_uri} -c "SELECT row_to_json(t1) FROM (SELECT pg_is_in_recovery()) t1;" -t | jq -r .pg_is_in_recovery)
if [[ "${in_recovery}" == "false" ]]; then
  echo "Database is ready for action"
fi

Should display the following unless your database is recovering from failover/restart etc:

Database is ready for action