PostgreSQL: Generated column computed from jsonb column

Problem: I am using PostgreSQL 12 new feature Generated Column, but computing column is jsonb type that containing json as "active":true or "active":false. I have tried to create the table using the following PostgreSQL statement.

CREATE TABLE mytable (     data jsonb,     active boolean generated always as ((data ->> 'active')::boolean) stored,     ... );

The problem is that sometimes active is missing in data so default value save null in the "active" column while I want to default value false.

Solution: When the key does not exist the operator ->> returns NULL, so you can just COALESCE that to the desired default value.

Using COALESCE Set default value if the key (json field) doesn't exist

To resolve the issue just replace the code ((data ->> 'active')::boolean) with COALESCE((data ->> 'public')::boolean, FALSE), look at the below query.

CREATE TABLE mytable (     data jsonb,     active boolean generated always as COALESCE((data ->> 'public')::boolean, FALSE) stored,     ... );