我正在處理這個有趣的資料，它在其中一列中有這種自定義的字典格式。
| ID | 引數 |
|---|---|
| 1 | x_id x_value; y_id y_value; z_id z_value; |
| 2 | y_id y_value2; z_id z_value2; |
并希望將其變成這種格式...
| ID | x_id | y_id | z_id |
|---|---|---|---|
| 1 | x_value | y_value | z_value |
| 2 | 空值 | y_value2 | z_value2 |
我希望它是完全動態的，但如果它降低復雜性/提高性能，我可以提前知道所有可能的列名。我也可以保證這種模式是一致的。沒有額外的字典嵌套等。
不是 sql master，這是我想出的幼稚實作，但似乎很慢。有沒有更高效的方法來做到這一點？
select
string_agg(x_id, ',') as x_id,
string_agg(y_id, ',') as y_id,
string_agg(z_id, ',') as z_id
from (
select
t.id,
case when 'x_id' LIKE kvs.key then kvs.value else null end as x_id,
case when 'y_id' LIKE kvs.key then kvs.value else null end as y_id,
case when 'z_id' LIKE kvs.key then kvs.value else null end as z_id
from my_table as t
join (
SELECT
id,
split_part(trim(both ' ' FROM unnest(string_to_array(parameters, ';'))), ' ', 1) "key",
split_part(trim(both ' ' FROM unnest(string_to_array(parameters, ';'))), ' ', 2) "value"
FROM my_table
) as kvs
on t.id = kvs.id
) as params
group by params.id
uj5u.com熱心網友回復：
我會將其轉換為 JSON 值，然后您可以很容易地訪問每個鍵：
select id,
parameters ->> 'x_id' as x_id,
parameters ->> 'y_id' as y_id,
parameters ->> 'z_id' as z_id
from (
select t.id,
jsonb_object_agg(split_part(p.parm, ' ', 1), split_part(p.parm, ' ', 2)) as parameters
from the_table t
left join unnest(regexp_split_to_array(trim(';' from parameters), '\s*;\s*')) as p(parm) on true
group by id
) x
order by id;
trim(';' from p_input)必須洗掉尾隨;，否則將regexp_split_to_array()回傳一個空陣列元素。
您可以將其放入一個函式中以使事情變得更容易：
create or replace function convert_to_json(p_input text)
returns jsonb
as
$$
select jsonb_object_agg(elements[1], elements[2]) as parameters
from (
select string_to_array(p.parm, ' ') as elements
from unnest(regexp_split_to_array(trim(';' from p_input), '\s*;\s*')) as p(parm)
) t1;
$$
language sql
immutable;
這變得有點簡單：
select id,
convert_to_json(parameters) ->> 'x_id' as x_id,
convert_to_json(parameters) ->> 'y_id' as y_id,
convert_to_json(parameters) ->> 'z_id' as z_id
from the_table
order by id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/421796.html
標籤：