json string to table view in Postgres 14

Question

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?

Answer 1

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"}');