CODEWITHSUNDEEP
postgresqlpostgresql-9.4tsvector
KF Lin
KF Lin

Reputation: 1323

Expand multiple rows result of `jsonb_array_elements` to tsvector inside a PL/pgSQL procedure

overflowers,

I have a JSON array items like this (PostgreSQL 9.4):

[{name: "foo"},
 {name: "bar"},
 {name: "baz"}]

What I want is to concatenate all item's name into a tsvector-typed column so that I can create index on that column.

And if I run:

SELECT to_tsvector(array_to_string(array(SELECT jsonb_array_elements(items)->>'name' FROM store), ','))

I can get an expected result: 'foo': 1 'bar': 2 'baz': 3

But I got stuck at something like this:

CREATE OR REPLACE FUNCTION update_tsv()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv = to_tsvector(jsonb_array_elements(NEW.items)->>'name');
    RETURN NEW;
END;
$$ language 'plpgsql';

The to_tsvector complains about multiple rows.

A sqlfiddle is attached.

Any help will be extremely appreciated!

Upvotes: 0

Views: 888

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23837

Would you try like this:

CREATE OR REPLACE FUNCTION update_tsv()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv = to_tsvector(array_to_string(array( select json_array_elements(NEW.items)->>'name' ),' '));
    RETURN NEW;
END;
$$ language 'plpgsql';

Upvotes: 3

Related Questions