Question 114929 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres: Use JSONB column keys as generated stored column

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search