Search code examples
sqlpostgresqljsonbgenerated-columns

PostgreSQL Generated Column from a JSONB column with nested values

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

Solution

  • 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 🙇‍♂️