postgresql, generated fields from jsonb

1
votes

I'm using a generated column with postgresql 12 with data from a jsonb field (containing a json with "public":true or "public":false

CREATE TABLE people (
    ...,
    data jsonb,
    public boolean generated always as ((data ->> 'public')::boolean) stored,
    ...
)

sometimes the json (coming from different applications) is missing the public key. how i can set a default to FALSE to public column?

2
sqljsonpostgresqlcreate-tablejsonb

2 Answers

1
votes

The ->> operator returns NULL when the key is not found, so you can just COALESCE that to the desired default value:

COALESCE((data ->> 'public')::boolean, FALSE)
0
votes

You could use a case expression along with the ? operator, like:

create table people (
    ...,
    data jsonb,
    public boolean generated always as (
        case 
            when data ? 'public' then (data ->> 'public')::boolean
            else false
        end
    ) stored
);