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 — user330315
– user330315, Commented Jun 8, 2020 at 9:21

ruslansteiger · Accepted Answer · 2020-06-08 09:33:54Z

7

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

answered Jun 8, 2020 at 9:33

ruslansteiger

5721 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Generated Column from a JSONB column with nested values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related