jsonpostgresqlnsregularexpressionpostgresql-14

json string to table view in Postgres 14

Working with data as a json string

{"tc_0": "Namefield1:ValueField", "tc_2": "Namefield2:", "tc_1": "Namefield3:", "tc_3": "Namefield4:ValueField:ValueField"}

I need to convert them into entries like this

select *
from 
(
    values('Namefield1','ValueField'),('Namefield3',null),('Namefield2',null),('Namefield4','ValueField:ValueField')
) as t(name, value)

How can I do this?

Solution

  • Use split_part() after jsonb_each_text():

    select split_part(value, ':', 1) as first,
       substr(value, length(split_part(value, ':', 1)) + 2) as second
  from jsonb_each_text('{"tc_0": "Namefield1:ValueField", "tc_2": "Namefield2:", "tc_1": "Namefield3:", "tc_3": "Namefield4:ValueField:ValueField"}');