数据模型
在数据库中,一个人被表示为一个元表行,该行具有一个名称和多个属性,这些属性作为键值对存储在数据表中(键和值在单独的列中).
现在有一个查询来检索所有用户(name)及其所有属性(data).属性在单独的列中作为JSON对象返回.下面是一个例子:
name data
Florian { "age":25 }
Markus { "age":25, "color":"blue" }
Thomas {}
SQL命令如下所示:
SELECT
name,
json_object_agg(d.key, d.value) AS data,
FROM meta AS m
JOIN (
JOIN 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;
问题
现在我面临的问题是,像Thomas这样没有任何属性存储在key-value表中的用户,没有使用我的 Select 函数显示.这是因为它只做JOIN
而不做LEFT OUTER JOIN
.
如果我使用LEFT OUTER JOIN
,那么我遇到了一个问题,那就是json_object_agg
try 聚合NULL
个值,结果以错误告终.
方法
1.返回键和值的空列表
所以我试着判断用户的key-column是否为NULL
,并返回一个空数组,这样json_object_agg
就会创建一个空的JSON对象.
但实际上,SQL中并没有创建空数组的函数.我找到的最接近的东西是:
select '{}'::text[];
结合COALESCE
,查询如下所示:
json_object_agg(COALESCE(d.key, '{}'::text[]), COALESCE(d.value, '{}'::text[])) AS data
但如果我try 使用这个,我会得到以下错误:
ERROR: COALESCE types text and text[] cannot be matched
LINE 10: json_object_agg(COALESCE(d.key, '{}'::text[]), COALES...
^
Query failed
PostgreSQL said: COALESCE types text and text[] cannot be matched
所以在运行时d.key
看起来是一个单一的值,而不是一个array.
2.拆分JSON创建并返回空列表
所以我试着拿json_object_agg
把它换成json_object
把我的 keys 换掉:
json_object(COALESCE(array_agg(d.key), '{}'::text[]), COALESCE(array_agg(d.value), '{}'::text[])) AS data
但我得到的误差是null value not allowed for object key
.因此COALESCE
不会判断数组是否为空.
问题
那么,是否有一个函数来判断连接列是否为空,如果为空,则只返回一个简单的JSON对象?
或者有没有其他解决方案可以解决我的问题?