Postgres: how to do an index on a jsonb[] column?

Asked Jun 17 '18 at 01:33

Active Jun 17 '18 at 09:18

Viewed 84 times

In Postgres is it possible to create an index on a jsonb[] column?

I have a table that contains a set of jsonb[] array, and each json in table A has a reference to another table B.

I want to have the count of element B in A when I select B.

I do something like :

SELECT 
    id, 
    (SELECT count(*) 
     FROM A, unnest(jsons) json 
     WHERE json->>'id' = B.id) 
FROM B

That works but it is very slow :( !

edited Jun 17 '18 at 09:18

asked Jun 17 '18 at 01:33

s.chafik

Sorry by: Yhak work but very slow I wanted to write That work but very slow – s.chafik Jun 17 '18 at 01:37
1

http://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/ – a_horse_with_no_name Jun 17 '18 at 06:24
did you try to create the index using these instructions https://stackoverflow.com/a/18405706/1265980 ? what error did you get? – Ereli Jun 17 '18 at 07:20
Yes I have this error: data type jsonb[] has no default operator class for access method "gin" – s.chafik Jun 17 '18 at 09:06

0 Answers0