PostgreSQL joining using JSONB with TypeORM-CodePudding

I have this SQL

CREATE TABLE product(id SERIAL PRIMARY KEY, name text, categories JSONB);

INSERT INTO product(name, categories) VALUES
   ('prouct1', '{"ids":[4,5]}'),
   ('prouct2', '{"ids":[5,6]}'),
   ('prouct3', '{"ids":[7]}');

CREATE TABLE category(id bigint, rootid bigint);

INSERT INTO category(id, rootid) VALUES
   (1, null),
   (2, null),
   (3, null),
   (4, 1),
   (5, 2),
   (6, 1),
   (7, 3);

I want to make this query with TypeORM. But I have no idea how to make jsonb_array_elements_text(b.categories->'ids') pc(categoryid) ON TRUE part with the TypeORM.

SELECT p.id, p.name, p.categories
FROM product p 
INNER JOIN jsonb_array_elements_text(b.categories->'ids') pc(categoryid) ON TRUE
INNER JOIN category c ON pc.categoryid = c.categoryid AND c.rootid = 1000;

Alternatively, I was trying another query. But it is too slow when I put jsonb_array_elements_text(categories->'ids'). Why it happen?

SELECT p.id, p.name, p.categories
FROM product p 
INNER JOIN (SELECT id, jsonb_array_elements_text(categories->'ids') categoryid FROM product) pc ON p.id = pc.id
INNER JOIN category c ON pc.categoryid = c.categoryid AND c.rootid = 1000;

CodePudding user response：

In PostgreSQL have additional index type GIN for JSON and JSONB types. For best performance, you must create an index for this JSON field. For example:

CREATE INDEX product_category_json_index ON product USING gin (categories jsonb_path_ops);

And I wrote an alternative query for you:

select main.*, cat.* from 
(
    select p.*, jsonb_array_elements((categories->'ids'))::integer as category_id 
    from product p
) main 
inner join examples.category cat on cat.id = main.category_id;

I want to get more detailed information, for these tables, if you know then please explain to me how many records in both tables (product and category) I want to insert to my local tables sample data (same count) for testing and analyzing