Vu7NR8PL

1EXPLAIN ANALYZE SELECT * FROM items WHERE int_array_cast(metadata->>'types') @> '{52, 53}' 
2     
3Seq Scan on items  (cost=10000000000.00..10000000016.07 rows=1 width=2391) (actual time=0.073..0.117 rows=1 loops=1)
4  Filter: (int_array_cast((metadata ->> 'types'::text)) @> '{10,14}'::integer[])
5  Rows Removed by Filter: 37
6Planning Time: 0.201 ms
7Execution Time: 0.197 ms 
8     
9CREATE TABLE "items" ( 
10    "item_uuid" UUid NOT NULL,
11    "metadata" JSONB,
12    PRIMARY KEY ("item_uuid") 
13); 
14     
15CREATE OR REPLACE FUNCTION int_array_cast(TEXT) RETURNS INT[] 
16AS 
17$$
18  SELECT CAST($1 AS INT[])
19$$
20IMMUTABLE
21LANGUAGE SQL
22RETURNS NULL ON NULL INPUT 
23     
24CREATE INDEX IF NOT EXISTS items_metadata_dok_index ON items USING GIN(int_array_cast(metadata->>'types')) 
25     
261 | {"types":"{1,2}", "name": "item_name1"}
27    2 | {"types":"{10,11}", "name": "item_name2"}
28    3 | {"types":"12", "name": "item_name3"}
29    3 | {"name": "item_name4"}