I'm working with PostgreSQL 9.5, and I want to create an index for multiple fields of a JSON object. I'm showing two examples, the first one is working and I'm stuck on the second one.
Example 1 - working
For the following code:
CREATE TABLE product(id SERIAL, description JSONB);
INSERT INTO product(id, description) VALUES(1, '{"category":"shoes","name":"red women shoes","brand":"nike"}');
INSERT INTO product(id, description) VALUES(2, '{"category":"shoes","name":"women heels shoes","brand":"red valentino"}');
CREATE INDEX "product_description_idx" ON product USING GIN(to_tsvector('english', (description->>'name') || ' ' || (description->>'category')));
SELECT * from product WHERE to_tsvector('english', (description->>'name') || ' ' || (description->>'category')) @@ to_tsquery('shoes & red');
I got as output only the product id = 1, with is correct.
Example 2 - here is where I'm stuck
For the same table, but this time the description is an array of objects:
CREATE TABLE product(id SERIAL, description JSONB);
INSERT INTO product(id, description) VALUES(1, '[{"category":"shoes","name":"red women shoes","brand":"nike"}, {"category":"hat","name":"white hat","brand":"nike"}]');
INSERT INTO product(id, description) VALUES(2, '[{"category":"shoes","name":"women heels shoes","brand":"red valentino"}, {"category":"dress","name":"maxi dress","brand":"red valentino"}]');
How can I create an index, like in Example 1, using the name and description of all objects of the JSON array?
Note: I just want to create the index with these two fields, name and description, because if the field brand is part of the index as well, the product id = 2 will be returned as a result and that should not happen.
I've searched for similar questions, like Index for finding an element in a JSON array and How do I query using fields inside the new PostgreSQL JSON datatype?, but I couldn't figure out a solution.
Thanks, Sérgio
