如何從 JSONB 中選擇具有給定鍵的子對象？

如何從 Postgres 中的 json(b) 對像中提取/獲取/選擇“子對象”？

似乎有很多資訊讓我幾乎一路走到了那裡，但並不完全。很多關於轉換為記錄、過濾然後從中建構新對象的東西。TBH，我真的很驚訝這不是內置功能。也許有一種簡單的方法可以通過編寫內置 fns 來實現這一點？我正在尋找的基本上相當於select-keys; 一個函式（比如，jsonb_select_keys），它給出了這個：

SELECT jsonb_select_keys('{"a":42,"b":43,"c":44,"d":97}', '{a,d,e}');

會返回這個：

{"a":42,"d":97}

有點像jsonb_path_query_array，但對於 kv 對而不是值。

您可以使用運算符刪除給定的鍵**-**（與您的想法相反）：

jsonb - text[]→jsonb

從左操作數中刪除所有匹配的鍵或數組元素。

'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]→{}

但我不知道在目前 Postgres 13 中是否有內置函式或運算符可以滿足您的要求。

SELECT值得注意的是，SQL語句正好相反。在那裡你得到一個肯定列表SELECT a,d,e FROM tbl，但不能簡單地得到*“所有列，除了$$ a,d,e $$“就像你可以得到”所有的鑰匙，除了$$ a,d,e $$"*來自具有上述運算符的 JSON 對象。我希望為每個對象提供互補的功能。

也想不出使用 SQL/JSON 路徑語言（Postgres 12+）的簡單方法。

解決方法jsonb

您可以創建一個簡單的函式，例如：

CREATE OR REPLACE FUNCTION f_jsonb_select_keys(_js jsonb, _keys text[])
 RETURNS jsonb
 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT jsonb_object_agg(t.key, t.value)
FROM   jsonb_each(_js) t
WHERE  t.key = ANY (_keys);
$func$;

db<>在這裡擺弄

稱呼：

SELECT f_jsonb_select_keys('{"a":42,"b":43,"c":44,"d":97}', '{a,d,e}');

回報：

{"a":42,"d":97}

對於任一參數，您將獲得 NULL 或空輸入（如小提琴所示）。所以我聲明了函式**STRICT**（aka RETURNS NULL ON NULL INPUT），即使嵌套函式jsonb_object_agg()不是STRICT. 這可能會與函式內聯混淆，但由於無論如何都不能內聯（包含聚合函式），我們也可以。看：

• 沒有 STRICT 修飾符的函式執行得更快？
• 聲明函式可變性 IMMUTABLE 會損害性能嗎？

IMMUTABLE只是一種真實。繼續閱讀。

按鍵順序？

jsonb不保留鍵的順序（鍵在內部以確定的方式排序），因此我們不必擔心聚合中輸入行的順序。除非可能有重複，否則以輸入的最新副本為準。手冊：

因為該json類型儲存輸入文本的精確副本，所以它將保留標記之間的語義無關緊要的空白，以及 JSON 對像中鍵的順序。此外，如果值中的 JSON 對像多次包含相同的鍵，則保留所有鍵/值對。（處理函式將最後一個值視為有效值。）相比之下，jsonb不保留空白，不保留對象鍵的順序，並且不保留重複的對象鍵。如果在輸入中指定了重複鍵，則僅保留最後一個值。

這意味著，如果輸入中可能有重複的鍵，則行的順序很重要。我的函式沒有明確地對行進行排序，因此結果不會嚴格地為IMMUTABLE. 但是輸入直接來自jsonb_each()並且不能有重複的鍵jsonb（不像json）。= ANY輸入鍵數組中可能的重複項與構造無關。所以重複永遠不會發生。IMMUTABLE畢竟。

同樣，這將禁止函式內聯——如果它可以從一開始就被內聯。

相當於json

為了完整性：**對於json而不是jsonb**相同：

第一個變體不嘗試保留給定的鍵順序，它消除了輸入鍵數組中可能的重複項（但輸入中不可能出現重複項json！）：

CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
 RETURNS json
 LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value)
FROM   json_each(_js) t
WHERE  key = ANY (_keys);
$func$;

第二個變體保留給定的鍵順序並保留所有可能的重複項。（如果相同的鍵在輸入中是 2x，在json輸入鍵數組中是 3x，則結果中會得到 6x。）：

CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
 RETURNS json
 LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value ORDER BY ord)
FROM   unnest(_keys) WITH ORDINALITY k(key, ord)
JOIN   json_each(_js) t USING (key);
$func$;

關於WITH ORDINALITY：

• 如何保留未嵌套數組中元素的原始順序？

我製作了這些json函式STABLE，因為json_object_agg()它只是STABLE與 相對jsonb_object_agg()，即IMMUTABLE.

---

您可以使用json_build_object ('a', js->'a', 'd', js->'d', 'e', js->'e')，但這將包括所有鍵，如果未找到，則為 NULL 值。不完全是你的要求。而且您無法區分失去的鍵和具有實際 NULL 值的相同鍵之間的區別。

引用自：https://dba.stackexchange.com/questions/290005