Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

drop table if exists my_table;
create table my_table(id text, date date);
insert into my_table values
('5356:type=sub&quality=tier3&country=de', '2022-07-01'),
('351:country=fr&type=follow', '2022-07-01');

DROP TABLE



CREATE TABLE



INSERT 0 2


select 
    split_part(id, ':', 1) as id,
    date,
    jsonb_object_agg(split_part(param, '=', 1), split_part(param, '=', 2)) as params
from my_table
cross join unnest (string_to_array(split_part(id, ':', 2), '&')) as param
group by id, date;


id
date
params


351
2022-07-01
{"type": "follow", "country": "fr"}

5356
2022-07-01
{"type": "sub", "country": "de", "quality": "tier3"}



SELECT 2


select
    id,
    date,
    params->>'type' as type,
    params->>'country' as country,
    params->>'quality' as quality
from (
    select 
        split_part(id, ':', 1) as id,
        date,
        jsonb_object_agg(split_part(param, '=', 1), split_part(param, '=', 2)) as params
    from my_table
    cross join unnest (string_to_array(split_part(id, ':', 2), '&')) as param
    group by id, date
    ) s;


id
date
type
country
quality


351
2022-07-01
follow
fr
null

5356
2022-07-01
sub
de
tier3



SELECT 2

id	date	params
351	2022-07-01	{"type": "follow", "country": "fr"}
5356	2022-07-01	{"type": "sub", "country": "de", "quality": "tier3"}