如何在 postgresql 中创建一个空的 JSON 对象？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33304983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create an empty JSON object in postgresql?
提问by cansik
Datamodel
数据模型
A person is represented in the database as a meta table row with a name and with multiple attributes which are stored in the data table as key-value pair (key and value are in separate columns).
Simplified data-model
一个人在数据库中表示为元表行，具有名称和多个属性，这些属性作为键值对存储在数据表中（键和值在单独的列中）。
简化的数据模型
Now there is a query to retrieve all users (name) with all their attributes (data). The attributes are returned as JSON object in a separate column. Here is an example:
现在有一个查询来检索所有用户（名称）及其所有属性（数据）。属性在单独的列中作为 JSON 对象返回。下面是一个例子：
name data
Florian { "age":25 }
Markus { "age":25, "color":"blue" }
Thomas {}
The SQL command looks like this:
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;
Problem
问题
Now the problem I am facing is, that users like Thomaswhich do not have any attributes stored in the key-valuetable, are not shown with my select function. This is because it does only a JOINand no LEFT OUTER JOIN.
现在我面临的问题是，像Thomas这样没有任何属性存储在键值表中的用户没有显示在我的选择函数中。这是因为它只执行 aJOIN和 no LEFT OUTER JOIN。
If I would use LEFT OUTER JOINthen I run into the problem, that json_object_aggtry's to aggregate NULLvalues and dies with an error.
如果我会使用，LEFT OUTER JOIN那么我会遇到问题，那就是json_object_agg尝试聚合NULL值并因错误而死亡。
Approaches
方法
1. Return empty list of keys and values
1.返回空的键值列表
So I tried to check if the key-columnof a user is NULLand return an empty array so json_object_aggwould just create an empty JSONobject.
所以我试图检查用户的键列是否是NULL并返回一个空数组，所以json_object_agg只会创建一个空的JSON对象。
But there is not really a function to create an empty array in SQL. The nearest thing I found was this:
但是在 SQL 中并没有真正创建空数组的函数。我发现的最近的事情是这样的：
select '{}'::text[];
In combination with COALESCEthe query looks like this:
结合COALESCE查询如下所示：
json_object_agg(COALESCE(d.key, '{}'::text[]), COALESCE(d.value, '{}'::text[])) AS data
But if I try to use this I get following error:
但是，如果我尝试使用它，则会出现以下错误：
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
So it looks like that at runtime d.keyis a single value and not an array.
所以它在运行时看起来d.key是一个单一的值而不是一个数组。
2. Split up JSON creation and return empty list
2.拆分JSON创建并返回空列表
So I tried to take json_object_aggand replace it with json_objectwhich does not aggregatethe keys for me:
因此，我尝试json_object_agg将其替换为json_object不为我聚合密钥的：
json_object(COALESCE(array_agg(d.key), '{}'::text[]), COALESCE(array_agg(d.value), '{}'::text[])) AS data
But there I get the error that null value not allowed for object key. So COALESCEdoes not check that the array is empty.
但是我得到的错误是null value not allowed for object key. 所以COALESCE不检查数组是否为空。
Qustion
问题
So, is there a function to check if a joined column is empty, and if yes return just a simple JSON object?
那么，是否有一个函数可以检查连接的列是否为空，如果是，则只返回一个简单的 JSON 对象？
Or is there any other solution which would solve my problem?
或者有没有其他解决方案可以解决我的问题？
回答by klin
Use left joinwith coalesce(). As default value use '{}'::json.
left join与 一起使用coalesce()。作为默认值使用'{}'::json.
select name, coalesce(d.data, '{}'::json) as data
from meta m
left join (
select fk_id, json_object_agg(d.key, d.value) as data
from data d
group by 1
) d
on m.id = d.fk_id;
name | data
---------+------------------------------------
Florian | { "age" : "25" }
Marcus | { "age" : "25", "color" : "blue" }
Thomas | {}
(3 rows)
