合并查询中的串联 JSON(B) 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30101603/
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
Merging Concatenating JSON(B) columns in query
提问by Robin
Using Postgres 9.4, I am looking for a way to merge two (or more) jsonor jsonbcolumns in a query. Consider the following table as an example:
使用 Postgres 9.4，我正在寻找一种方法来合并查询中的两个（或更多）json或jsonb列。以下表为例：
id | json1 | json2
----------------------------------------
1 | {'a':'b'} | {'c':'d'}
2 | {'a1':'b2'} | {'f':{'g' : 'h'}}
Is it possible to have the query return the following:
是否可以让查询返回以下内容：
id | json
----------------------------------------
1 | {'a':'b', 'c':'d'}
2 | {'a1':'b2', 'f':{'g' : 'h'}}
Unfortunately, I can't define a function as described here. Is this possible with a "traditional" query?
不幸的是，我无法按照此处所述定义函数。这可以通过“传统”查询实现吗？
采纳答案by Clément Prévost
Here is the complete list of build-in functions that can be used to create json objects in PostgreSQL. http://www.postgresql.org/docs/9.4/static/functions-json.html
这是可用于在 PostgreSQL 中创建 json 对象的内置函数的完整列表。http://www.postgresql.org/docs/9.4/static/functions-json.html
row_to_jsonandjson_objectdoest not allow you to define your own keys, so it can't be used herejson_build_objectexpect you to know by advance how many keys and values our object will have, that's the case in your example, but should not be the case in the real worldjson_objectlooks like a good tool to tackle this problem but it forces us to cast our values to text so we can't use this one either
row_to_json并且json_object不允许您定义自己的密钥，因此不能在这里使用json_build_object希望您提前知道我们的对象将有多少键和值，在您的示例中就是这种情况，但在现实世界中不应该是这种情况json_object看起来是解决这个问题的好工具，但它迫使我们将值转换为文本，所以我们也不能使用这个
Well... ok, wo we can't use any classic functions.
嗯……好吧，我们不能使用任何经典函数。
Let's take a look at some aggregate functions and hope for the best... http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
让我们来看看一些聚合函数并希望最好... http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
json_object_aggIs the only aggregate function that build objects, that's our only chance to tackle this problem. The trick here is to find the correct way to feed the json_object_aggfunction.
json_object_agg是唯一一个构建对象的聚合函数，这是我们解决这个问题的唯一机会。这里的技巧是找到正确的方法来提供json_object_agg函数。
Here is my test table and data
这是我的测试表和数据
CREATE TABLE test (
id SERIAL PRIMARY KEY,
json1 JSONB,
json2 JSONB
);
INSERT INTO test (json1, json2) VALUES
('{"a":"b", "c":"d"}', '{"e":"f"}'),
('{"a1":"b2"}', '{"f":{"g" : "h"}}');
And after some trials and errors with json_objecthere is a query you can use to merge json1 and json2 in PostgreSQL 9.4
经过json_object这里的一些试验和错误之后，您可以使用一个查询来合并 PostgreSQL 9.4 中的 json1 和 json2
WITH all_json_key_value AS (
SELECT id, t1.key, t1.value FROM test, jsonb_each(json1) as t1
UNION
SELECT id, t1.key, t1.value FROM test, jsonb_each(json2) as t1
)
SELECT id, json_object_agg(key, value)
FROM all_json_key_value
GROUP BY id
EDIT: for PostgreSQL 9.5+, look at Zubin's answer below
编辑：对于 PostgreSQL 9.5+，看看下面祖宾的回答
回答by Zubin
In Postgres 9.5+ you can merge JSONB like this:
在 Postgres 9.5+ 中，您可以像这样合并 JSONB：
select json1 || json2;
Or, if it's JSON, coerce to JSONB if necessary:
或者，如果是 JSON，则在必要时强制转换为 JSONB：
select json1::jsonb || json2::jsonb;
Or:
或者：
select COALESCE(json1::jsonb||json2::jsonb, json1::jsonb, json2::jsonb);
(Otherwise, any null value in json1or json2returns an empty row)
（否则，任何空值json1或json2返回空行）
For example:
例如：
select data || '{"foo":"bar"}'::jsonb from photos limit 1;
?column?
----------------------------------------------------------------------
{"foo": "bar", "preview_url": "https://unsplash.it/500/720/123"}
Kudos to @MattZukowski for pointing this out in a comment.
感谢@MattZukowski 在评论中指出这一点。
回答by caiohamamura
Also you can tranform json into text, concatenate, replace and convert back to json. Using the same data from Clément you can do:
您也可以将 json 转换为文本、连接、替换和转换回 json。使用 Clément 的相同数据，您可以：
SELECT replace(
(json1::text || json2::text),
'}{',
', ')::json
FROM test
You could also concatenate all json1 into single json with:
您还可以使用以下命令将所有 json1 连接成单个 json：
SELECT regexp_replace(
array_agg((json1))::text,
'}"(,)"{|\| |^{"|"}$',
'',
'g'
)::json
FROM test
回答by Joost D?bken
However this question is answered already some time ago; the fact that when json1and json2contain the same key; the key appears twice in the document, does not seem to be best practice.
然而，这个问题已经在前一段时间得到了回答；当json1和json2包含相同密钥的事实；密钥在文档中出现两次，似乎不是最佳实践。
Therefore u can use this jsonb_mergefunction with PostgreSQL 9.5:
因此你可以jsonb_merge在 PostgreSQL 9.5 中使用这个函数：
CREATE OR REPLACE FUNCTION jsonb_merge(jsonb1 JSONB, jsonb2 JSONB)
RETURNS JSONB AS $$
DECLARE
result JSONB;
v RECORD;
BEGIN
result = (
SELECT json_object_agg(KEY,value)
FROM
(SELECT jsonb_object_keys(jsonb1) AS KEY,
1::int AS jsb,
jsonb1 -> jsonb_object_keys(jsonb1) AS value
UNION SELECT jsonb_object_keys(jsonb2) AS KEY,
2::int AS jsb,
jsonb2 -> jsonb_object_keys(jsonb2) AS value ) AS t1
);
RETURN result;
END;
$$ LANGUAGE plpgsql;
The following query returns the concatenated jsonb columns, where the keys in json2are dominant over the keys in json1:
以下查询返回连接的 jsonb 列，其中 injson2的键优于 in 的键json1：
select id, jsonb_merge(json1, json2) from test
回答by Arthur Nascimento
FYI, if someone's using jsonb in >= 9.5 and they only care about top-level elements being merged without duplicate keys, then it's as easy as using the || operator:
仅供参考，如果有人在 >= 9.5 中使用 jsonb 并且他们只关心在没有重复键的情况下合并顶级元素，那么就像使用 || 一样简单 操作员：
select '{"a1": "b2"}'::jsonb || '{"f":{"g" : "h"}}'::jsonb;
?column?
-----------------------------
{"a1": "b2", "f": {"g": "h"}}
(1 row)
回答by Sandeep Sinha
This function would merge nested json objects
此函数将合并嵌套的 json 对象
create or replace function jsonb_merge(CurrentData jsonb,newData jsonb)
returns jsonb
language sql
immutable
as $jsonb_merge_func$
select case jsonb_typeof(CurrentData)
when 'object' then case jsonb_typeof(newData)
when 'object' then (
select jsonb_object_agg(k, case
when e2.v is null then e1.v
when e1.v is null then e2.v
when e1.v = e2.v then e1.v
else jsonb_merge(e1.v, e2.v)
end)
from jsonb_each(CurrentData) e1(k, v)
full join jsonb_each(newData) e2(k, v) using (k)
)
else newData
end
when 'array' then CurrentData || newData
else newData
end
$jsonb_merge_func$;
回答by igilfanov
CREATE OR REPLACE FUNCTION jsonb_merge(pCurrentData jsonb, pMergeData jsonb, pExcludeKeys text[])
RETURNS jsonb IMMUTABLE LANGUAGE sql
AS $$
SELECT json_object_agg(key,value)::jsonb
FROM (
WITH to_merge AS (
SELECT * FROM jsonb_each(pMergeData)
)
SELECT *
FROM jsonb_each(pCurrentData)
WHERE key NOT IN (SELECT key FROM to_merge)
AND ( pExcludeKeys ISNULL OR key <> ALL(pExcludeKeys))
UNION ALL
SELECT * FROM to_merge
) t;
$$;
SELECT jsonb_merge('{"a": 1, "b": 9, "c": 3, "e":5}'::jsonb, '{"b": 2, "d": 4}'::jsonb, '{"c","e"}'::text[]) as jsonb
SELECT jsonb_merge('{"a": 1, "b": 9, "c": 3, "e":5}'::jsonb, '{"b": 2, "d": 4}': :jsonb, '{"c","e"}'::text[]) 作为 jsonb
回答by Piyush Sharma
Try this, if anyone having an issue for merging two JSON object
试试这个，如果有人在合并两个 JSON 对象时遇到问题
select table.attributes::jsonb || json_build_object('foo',1,'bar',2)::jsonb FROM table where table.x='y';
回答by user1767316
works well as an alternative to || when recursive deep merge is required (found here) :
可以很好地替代 || 当需要递归深度合并时（在这里找到）：
create or replace function jsonb_merge_recurse(orig jsonb, delta jsonb)
returns jsonb language sql as $$
select
jsonb_object_agg(
coalesce(keyOrig, keyDelta),
case
when valOrig isnull then valDelta
when valDelta isnull then valOrig
when (jsonb_typeof(valOrig) <> 'object' or jsonb_typeof(valDelta) <> 'object') then valDelta
else jsonb_merge_recurse(valOrig, valDelta)
end
)
from jsonb_each(orig) e1(keyOrig, valOrig)
full join jsonb_each(delta) e2(keyDelta, valDelta) on keyOrig = keyDelta
$$;
