PostgreSQL的 - 键值对标准化

Question

我试图设计一个架构（在Postgres的，但任何其他SQL的罚款），支持以下要求：

• 每个文档（表documents行）有唯一的字符串ID（id字段）和其他几个数据字段。
• 每个文档可以附加0个或更多标签（即字符串键值对），目标是构建一个系统，让用户使用这些字符串键值对对文档进行排序或过滤。例如。 “告诉我，有一个标签的所有文档‘键1’与‘值1’值，并使用的标记值排序输出‘KEY3’

因此DDL应该是这样的：（简体）

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 
) 

现在我的问题是如何建立一个查询，使用标签键值进行过滤/排序？例如返回所有具有“key1”=“value1”标签和“标签”的文档（可能带有LIMIT/OFFSET） key2“=”value2“标签，按”key3“标签的值排序

Answer 1

您可以使用group by和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);