我有一个数据模型,如下所示,仅简化以展示您所面临的问题(SQL Fiddle链接在底部):
人以元表行表示,并具有名称和多个属性,这些属性作为键值对存储在数据表中(键和值分别位于不同的列中)。
期望结果
现在,我想检索所有用户及其所有属性。 属性应以json对象的形式返回到单独的列中。 例如:
name, data
Florian, { "age":23, "color":"blue" }
Markus, { "age":24, "color":"green" }
我的方法
现在我的问题是,我找不到在Postgres中创建键值对的方法。我尝试了以下方法:
SELECT
name,
array_to_json(array_agg(row(d.key, d.value))) AS data
FROM meta AS m
JOIN (
SELECT d.fk_id, d.key, d.value AS value FROM data AS d
) AS d
ON d.fk_id = m.id
GROUP BY m.name;
但它将此作为数据列返回:
[{"f1":"age","f2":24},{"f1":"color","f2":"blue"}]
其他解决方案
我知道有一个名为crosstab的函数可以将数据表转换为以列为键,以行为值的表格。但是这不是动态的。而且我不知道数据表中一个人有多少属性。所以这不是一个选择。
我也可以创建一个类似json的字符串,其中包含两个行值并将它们聚合。但也许有一种更好的解决方案。
不,无法更改数据模型,因为真实的数据模型已被多个方使用。
SQLFiddle
请查看并测试我为此问题创建的fiddle: http://sqlfiddle.com/#!15/bd579/14