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
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;
Thank you RhodiumToad in the #postgresql IRC channel for providing a solution for this problem 🙇♂️