CODEWITHSUNDEEP
sqlpostgresql
fujidaon
fujidaon

Reputation: 467

Split string into multiple columns

I have a table:

id date
5356:type=sub&quality=tier3&country=de 2022-07-01
351:country=fr&type=follow 2022-07-01

I want to split these ids (to create a view) like:

So I expect this:

id date type quality country
5356 2022-07-01 sub tier3 de
351 2022-07-01 follow NULL fr

I can't use split_part for this, because of different order of parameters inside id.

Upvotes: 1

Views: 1817

Answers (1)

klin
klin

Reputation: 121544

Transforming these strings into jsonb objects is relatively straightforward:

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;

Now you can use the solution described in Flatten aggregated key/value pairs from a JSONB field?

Alternatively, if you know the number and names of the parameters, this query is simpler and works well:

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;

Test it in Db<>fiddle.

In Postgres 14+ you can replace unnest(string_to_array(...)) with string_to_table(...).

Upvotes: 1

Related Questions