postgresql jsonb update List of string value based on existing values searched in other table

Question

I have two tables like these in postgres db :

 TABLE tag (
  id number,
  name nvarchar
);

 TABLE article (
  id number,
  tags jsonb  // List<String>   list of **name**s of tags  from tag table => should be converted 
                 to list of **Id**s from tag table
);

now I want to update the article table to keep the List of tags Ids instead of names from the tag table. how can I write a update query which for all records of article table updates tags jsonb columns and sets to the ids of tags based on old name values(in current jsonb tags Column)?

Answer 1

this query worked for me :

UPDATE article artc
       SET tags= array_to_json(ARRAY(
select id from tag where tag.name in (select tag #>> '{}' from article , jsonb_array_elements(tags) tag  where id =artc.id)));