Postgres: Use JSONB column keys as generated stored column

Question

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

{
  "john.doe@best.com": {
    "tags": {
    "work": true
  }
},
  "nate.calman@best.com": {
    "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:

john.doe@best.com nate.calman@best.com

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?

Change your data model, have a table of e-mail addresses and use foreign keys. — Laurenz Albe, Aug 29 '22 at 06:14
Why do you want to add that column? What problem are you trying to solve with that? You are adding another layer of de-normalization to an already de-normalized model. — , Aug 29 '22 at 06:15
I want adding the `lookup` column to add a gin index on it and make it easy on search. This is just an example, I have other columns also that i will put in the lookup column. — Baqir Khan, Aug 29 '22 at 06:24

Stefanov.sm · Answer 1 · 2022-08-29T08:21:12.447

0

Better build a view instead. Use lateral join

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

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;

edited Aug 29 '22 at 08:21

answered Aug 29 '22 at 05:51

Stefanov.sm

11,215
2
21
21

1

A `left join` is probably better than the `cross join` - otherwise rows without any emails won't show up in the view. – Aug 29 '22 at 06:14

score 0 · Answer 2 · answered Aug 29 '22 at 06:35

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;

Postgres: Use JSONB column keys as generated stored column

2 Answers2