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
Advertisement
Answer
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 ????♂️