Handling empty JSONB errors ​

Breaking behavior changes were made between 7.0 and 7.1. Among these changes is that the JSONB created from an empty string is no longer valid

SELECT ''::JSONB;
-- ERROR:  invalid input syntax for type jsonb:
-- LINE 1: SELECT ''::JSONB;

This is a potential problem for upgrades from versions that support this behavior to ones that do not.

There is a preupgrade check for this when upgrading from 7.0. You can hit an error regarding empty jsonbs during the preupgrade check. You can also hit the error after the upgrade tp 7.1 if the preupgrade check was not run before the upgrade.

In the sections below, we describe how to handle each of these scenarios. We demonstrate this on a table created on a 7.0 version of yellowbrick like this

CREATE TABLE t AS (SELECT 0::INT AS i, ''::JSONB AS jb);

Empty JSONB error during pre-upgrade ​

If the pre-upgrade check for empty JSONB values fails, you will see a message like this in the preupgrader logs:

WARNING: yellowbrick_test_utf8: public.t contains 1 rows with empty JSONBs
REMEDY: To view the empty JSONBs, run this statement in the database 'yellowbrick_test_utf8':
 SELECT rowunique, jb FROM public.t WHERE jb IS NOT NULL AND json_typeof(jb) IS NULL;

 See https://docs.yellowbrick.com/latest/ybd_sqlref/jsonb_handle_empty.html for more information

As shown in the remedy message, you can use the condition jb IS NOT NULL AND json_typeof(jb) IS NULL to detect empty JSONB values, and inspect affected rows or to update the values to something else, for example by running

BEGIN;

UPDATE t SET jb = NULL WHERE jb IS NOT NULL AND json_typeof(jb) IS NULL;

COMMIT;

Empty JSONB in stats error during pre-upgrade ​

Another check that can fail is the check for empty JSONB values in pg_statistic:

WARNING: yellowbrick_test_utf8: The following tables contain empty JSONB values in their stats
    public.t
 See https://docs.yellowbrick.com/latest/ybd_sqlref/jsonb_handle_empty.html for more information

This means that pg_statistic, a catalog table responsible for storing statistical data about table columns, such as the most common values for a particular column, contains rows with empty JSONB values.

To solve this problem, you first need to follow the steps from the previous section to ensure that you have removed all empty JSONB values from the affected tables.

When those values are deleted, the pg_statistic table may still contain empty JSONB values if no analyze operations took place. To ensure these values are gone, you should explicitly run ANALYZE on the tables that are listed in the warning message.

ANALYZE t;

Empty JSONB error after upgrade ​

If pre-upgrade was not run before upgrading, you can get into a situation where you have empty JSONB values in your tables that are no longer considered valid. Many operations on these values become invalid after an upgrade, for example:

SELECT * FROM t;
-- ERROR:  empty jsonb is no longer supported, see https://docs.yellowbrick.com/latest/ybd_sqlref/jsonb_handle_empty.html

The condition to check for empty jsonb values is yb_is_empty_jsonb(jb). Other columns in the affected rows of the table can be viewed by running:

SELECT i FROM t WHERE yb_is_empty_jsonb(jb);
--  0

To replace the empty JSONB values with some other value that is valid, use an update statement:

BEGIN;

UPDATE t SET jb = NULL WHERE yb_is_empty_jsonb(jb);

COMMIT;