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 · Accepted Answer · 2017-11-25 22:26:20Z

2

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);

answered Nov 25, 2017 at 22:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Kurma Over a year ago

What indexes should I add for this query?

Gordon Linoff Over a year ago

An optimal index would be document_tags(tag_key, tag_value, document_id).

Collectives™ on Stack Overflow

PostgreSQL - key-value pair normalization

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related