Postgres Array, DOC Tags

doc_tags.sql

table doc_tags_array (
    doc_id int not null references documents(doc_id),
    tags text[] not null default '{}'
)
unique index doc_tags_id_doc_id on (doc_id)
index doc_tags_id_tags using gin (tags)

-- One Tag + Offset

select doc_id 
  from doc_tags_array
  where tags @> ARRAY['math']
  order by doc_id limit 25 offset 0; 

-- Multiple Tags

select doc_id
    from doc_tags_array
    where tags @> array['math','physics']
    order by doc_id limit 25;
    
-- Tag Cloud


select count(*) as tag_count, tag
from doc_tags_array
    join tags on doc_tags_array.tags @> array[tags.tag::text]
group by tag
order by tag_count desc limit 100;