1

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 :( !

s.chafik
  • 11
  • 2
  • 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/ –  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