PostgreSQL Generated Column from a JSONB column with nested values

Question

I have following table with a JSONB column:

CREATE TABLE movies (
    contributors    JSONB
);

The data in the column looks like this:

INSERT INTO movies (contributors) VALUES('[
  {"last_name": "Robbins", "first_name": "Tim", "age": 61},
  {"last_name": "Freeman", "first_name": "Morgan", "age": 83}
]');

Now I want to add a generated column of vectors. It should only contain last_name of the JSONB column:

ALTER TABLE movies ADD COLUMN search TSVECTOR
    GENERATED ALWAYS AS (TO_TSVECTOR('simple',
        /* need help here  */
    )) STORED;

Can anyone help me out how to do that? Vectors should look like this 'freeman':2 'robbin':1 Demo on DB Fiddle

This would be way easier with a properly normalized data model — , Jun 08 '20 at 09:21

score 3 · Accepted Answer · answered Jun 08 '20 at 09:33

With the JSON Path expression you can specify the items in the JSON data. With following example only items with the key last_name will be included:

ALTER TABLE movies ADD COLUMN search TSVECTOR
    GENERATED ALWAYS AS (TO_TSVECTOR('simple',
        jsonb_path_query_array(contributors, '$[*].last_name') /* ✅ */
    )) STORED;

Demo on DB Fiddle

Thank you RhodiumToad in the #postgresql IRC channel for providing a solution for this problem ‍♂️

PostgreSQL Generated Column from a JSONB column with nested values

1 Answers1