我有一個具有jsonb陣列型別的表，并且我正在嘗試在該陣列中專門對遵循某些要求的電子郵件（通過 md5 或 sha1）進行哈希/匿名化。使用下面的虛擬資料，我很難嘗試owners專門針對任何不帶@google.com域或 admin.
{
"blah": blah,
"owners": [
"admin"
],
},
{
"blah": blah,
"owners": [
"[email protected]"
],
},
{
"blah": blah,
"owners": [
"[email protected]", "[email protected]"
],
},
{
"blah": blah,
"owners": [
"[email protected]", "[email protected]"
],
}
理想情況下，最終結果應該是
{
"blah": blah,
"owners": [
"admin"
],
},
{
"blah": blah,
"owners": [
"[email protected]"
],
},
{
"blah": blah,
"owners": [
"[email protected]", "[email protected]"
],
},
{
"blah": blah,
"owners": [
"[email protected]", "[email protected]"
],
}
這里的邏輯可能是遍歷owners列中的每個陣列，但不確定查詢的具體外觀。
uj5u.com熱心網友回復：
您可以使用子查詢json_agg：
select jsonb_set(t.data, '{owners}',
(select jsonb_agg(case when v.value::text = '"admin"' or v.value::text ~ 'google\.com"' then replace(v.value::text, '"', '') else md5(substring(v.value::text from '^"([\w\.\-] )@'))||'@'||md5(substring(v.value::text from '@([\w\.\-] )"$')) end)
from jsonb_array_elements(t.data -> 'owners') v))
from vals t
出于演示的目的，使用的散列是md5.
uj5u.com熱心網友回復：
(a) 您的jsonb資料樣本內部有一些錯誤。對于這個答案，我將改用：
[{
"blah": "blah",
"owners": [
"admin"
]},
{
"blah": "blah",
"owners": [
"[email protected]"
]},
{
"blah": "blah",
"owners": [
"[email protected]", "[email protected]"
]},
{
"blah": "blah",
"owners": [
"[email protected]", "[email protected]"
]}]
(b)jsonb使用 postgres 在資料中進行多次更新并不容易。以下解決方案在跟蹤使用新電子郵件值更新 jsonb 資料結構時使用的路徑資訊時分解 jsonb 資料結構。
(c) 該解決方案基于jsonb_set()標準函式，該函式只能對給定的 jsonb 資料應用一次更新。所以第一步是創建jsonb_set_agg()一個aggregate基于的函式jsonb_set，它能夠在迭代一組記錄時對相同的 jsonb 資料執行多次更新：
CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], z jsonb, c boolean)
RETURNS jsonb LANGUAGE sql AS
$$ SELECT jsonb_set(COALESCE(x,y), p, z, c) ; $$ ;
CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb,p text[], z jsonb, c boolean)
(SFUNC = jsonb_set, STYPE = jsonb) ;
(d) 最后，以下查詢分解 jsonb 資料（FROM子句），過濾必須更新的電子郵件（WHERE子句），然后根據ORDINALITY資訊的路徑，用新的電子郵件值更新 jsonb 資料：
SELECT jsonb_set_agg(j.json_data, array[(b.id - 1) :: text, 'owners', (c.id - 1) :: text], to_jsonb('insert_here_the_new_value' :: text), true)
FROM (VALUES ('[{
"blah": "blah",
"owners": [
"admin"
]},
{
"blah": "blah",
"owners": [
"[email protected]"
]},
{
"blah": "blah",
"owners": [
"[email protected]", "[email protected]"
]},
{
"blah": "blah",
"owners": [
"[email protected]", "[email protected]"
]}]' :: jsonb)) AS j(json_data)
CROSS JOIN LATERAL jsonb_array_elements(j.json_data) WITH ORDINALITY AS b(json_data, id)
CROSS JOIN LATERAL jsonb_array_elements_text(b.json_data->'owners') WITH ORDINALITY AS c(json_data, id)
WHERE c.json_data <> 'admin'
AND NOT c.json_data ~ '@google.com$'
結果是：
[ {"blah": "blah", "owners": ["admin"]}
, {"blah": "blah", "owners": ["[email protected]"]}
, {"blah": "blah", "owners": ["insert_here_the_new_value","insert_here_the_new_value"]}
, {"blah": "blah", "owners": ["insert_here_the_new_value", "[email protected]"]}
]
dbfiddle中的所有詳細資訊。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/411522.html
標籤：