PostgreSQL - key-value pair normalization

Question

I'm trying to design a schema (on Postgres but any other SQL's fine) that supports the following requirements:

Each document (a row in table documents) has a unique string ID (id field) and several other data fields.
Each document can have 0 or more tags (which is string key-value pairs) attached to it, and, the goal is to build a system that lets users to sort or filter documents using those string key-value pairs. E.g. "Show me all documents that have a tag of "key1" with "value1" value AND sort the output using the tag value of "key3".

So DDL should look like this: (simplified)

create table documents
(
  id char(32) not null
    constraint documents_pkey
    primary key,
  data varchar(2000),
  created_at timestamp,
  updated_at timestamp
)

create table document_tags
(
  id serial not null
    constraint document_tags_pkey
    primary key,
  document_id char(32) not null
    constraint document_tags_documents_id_fk
    references documents
    on update cascade on delete cascade,
  tag_key varchar(200) not null,
  tag_value varchar(2000) not null
)

Now my question is how can I build a query that does filtering/sorting using the tag key values? E.g. Returns all documents (possibly with LIMIT/OFFSET) that does have "key1" = "value1" tag and "key2" = "value2" tags, sorted by the value of "key3" tag.

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-11-25T22:26:20

You can use group by and having:

select dt.document_id
from document_tags dt
where dt.tag_key = 'key1' and dt.tag_value = 'value1'
group by dt.document_id
order by max(case when dt.tag_key = 'key2' then dt.tag_value end);

PostgreSQL - key-value pair normalization

1 Answers