Postgres: Use JSONB column keys as generated stored column-CodePudding

I have a table which has a jsonB column named as emailAddress. An example row in that column is like this:

{
  "[email protected]": {
    "tags": {
    "work": true
  }
},
  "[email protected]": {
    "tags": {
      "work": true
    }
  }
}

I want to create a new column named lookup which is generated based on the keys of the emailAddress column. So the value of lookup column of the above example should be something like:

[email protected] [email protected]

This will obviously update if more keys are added to the emailAddress column.

I started with this query:

ALTER TABLE "table_example"
ADD COLUMN lookup TEXT GENERATED ALWAYS AS (jsonb_object_keys("emailAddresses")) STORED;

But the error I get is: ERROR: set-returning functions are not allowed in column generation expressions

I understand that this is not the right way to do it because no where I m specifying the separator I want to use. I tried using multiple functions like array() or array_agg() to get this to a form accepted by generated column, but doesn't seem to work.

Any ideas?

CodePudding user response：

Better build a view instead. Use lateral join

create view the_view as
select t.*, l.keys as lookup
from the_table as t 
cross join lateral
(
  select string_agg(k, ' ') as keys  
  from jsonb_object_keys(mailAddress) as k
) as l;

or a scalar subquery

create view the_view as
select t.*, 
 (
   select string_agg(k, ' ') as keys  
   from jsonb_object_keys(mailAddress) as k
 ) as lookup
from the_table as t;

CodePudding user response：

This will work fine.

create or replace function remove_nested_object(obj jsonb, key_to_remove text)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(key, value- key_to_remove)
    from jsonb_each(obj)
$$;

ALTER TABLE  "table_example"
    ADD COLUMN lookup TEXT GENERATED ALWAYS AS (
      translate(remove_nested_object("emailAddresses", 'tags') #>> '{}',':{}",','')
    ) STORED;