我目前正在研究如何在我的資料庫中進行更復雜的資料遷移，以及是否可以在 SQL 中進行（我自己不是很有經驗的 SQL 開發人員）。
假設我將 JSON 存盤在 Postgres 表的文本列之一中，格式大致如下：
{"type":"something","params":[{"value":"00de1be5-f75b-4072-ba30-c67e4fdf2333"}]}
現在，我想將value部件遷移到更復雜的格式：
{"type":"something","params":[{"value":{"id":"00de1be5-f75b-4072-ba30-c67e4fdf2333","path":"/hardcoded/string"}}]}
此外，我還需要推理是否value包含 UUID 模式，如果沒有，請使用略有不同的結構：
{"type":"something-else","params":[{"value":"not-id"}]} ---> {"type":"something-else","params":[{"value":{"value":"not-id","path":""}}]}
我知道我可以定義一個程序并使用REGEX_REPLACE:REGEXP_REPLACE(source, pattern, replacement_string,[, flags])但我不知道如何處理關于內容是否包含 ID 的推理。有人可以建議至少一些方向或提示如何做到這一點嗎？
uj5u.com熱心網友回復：
您可以使用jsonb函式來提取資料并更改它們。最后，您應該擴展資料。
示例資料結構和查詢結果：dbfiddle
select
(t.data::jsonb || jsonb_build_object(
'params',
jsonb_agg(
jsonb_build_object(
'value',
case
when e.value->>'value' ~* '^[0-9A-F]{8}-[0-9A-F]{4}-4[0-9A-F]{3}-[89AB][0-9A-F]{3}-[0-9A-F]{12}$' then
jsonb_build_object('id', e.value->>'value', 'path', '/hardcoded/string')
else
jsonb_build_object('value', 'not-id', 'path', '')
end
)
)
))::text
from
test t
cross join jsonb_array_elements(t.data::jsonb->'params') e
group by t.data
PS：
如果您的表有id或唯一的欄位，您可以更改group by t.data為執行以下操作：
select
(t.data::jsonb || jsonb_build_object(
'params',
jsonb_agg(
jsonb_build_object(
'value',
case
when e.value->>'value' ~* '^[0-9A-F]{8}-[0-9A-F]{4}-4[0-9A-F]{3}-[89AB][0-9A-F]{3}-[0-9A-F]{12}$' then
jsonb_build_object('id', e.value->>'value', 'path', '/hardcoded/string')
else
jsonb_build_object('value', 'not-id', 'path', '')
end
)
)
))::text
from
test t
cross join jsonb_array_elements(t.data::jsonb->'params') e
group by t.id
uj5u.com熱心網友回復：
要value在任何深度替換s，您可以使用遞回CTE來運行value鍵的每個值的替換，使用條件檢查該值是否為 UUID，并相應地生成正確的 JSON 物件：
with recursive cte(v, i, js) as (
select (select array_to_json(array_agg(distinct t.i))
from (select (regexp_matches(js, '"value":("[\w\-] ")', 'g'))[1] i) t), 0, js from (select '{"type":"something","params":[{"value":"00de1be5-f75b-4072-ba30-c67e4fdf2333"}, {"value":"sdfsa"}]}' js) t1
union all
select c.v, c.i 1, regexp_replace(
regexp_replace(c.js, regexp_replace((c.v -> c.i)::text, '[\\"] ', '', 'g'),
case when not ((c.v -> c.i)::text ~ '\w \-\w \-\w \-\w \-\w ') then
json_build_object('value', regexp_replace((c.v -> c.i)::text, '[\\"] ', '', 'g'), 'path', '')::text
else json_build_object('id', regexp_replace((c.v -> c.i)::text, '[\\"] ', '', 'g'), 'path', '/hardcoded/path')::text end, 'g'),
'(")(?=\{)|(?<=\})(")', '', 'g')
from cte c where c.i < json_array_length(c.v)
)
select js from cte order by i desc limit 1
輸出：
{"type":"something","params":[{"value":{"id" : "00de1be5-f75b-4072-ba30-c67e4fdf2333", "path" : "/hardcoded/path"}}, {"value":{"value" : "sdfsa", "path" : ""}}]}
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/349170.html
標籤：sql PostgreSQL