Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (id, name) AS
SELECT 1, '{1:mark,2:john}' FROM DUAL UNION ALL
SELECT 2, '{1:Max,2:Mo}'    FROM DUAL;
2 rows affected

SELECT id,
       CASE
       WHEN spos > 0 AND epos > 0
       THEN SUBSTR(name, spos + 3, epos - spos - 3)
       END AS name2
FROM   (
  SELECT id,
         name,
         INSTR(TRANSLATE(name, '{}', ',,'), ',2:', 1) AS spos,
         INSTR(
           TRANSLATE(name, '{}', ',,'),
           ',',
           INSTR(TRANSLATE(name, '{}', ',,'), ',2:', 1, 1) + 3
         ) AS epos
  FROM   table_name
)


ID
NAME2


1
john

2
Mo


SELECT CASE
       WHEN spos > 0 AND epos > 0
       THEN SUBSTR(name, spos + 3, epos - spos - 3)
       END AS name
FROM   (
  SELECT id,
         name,
         INSTR(TRANSLATE(name, '{}', ',,'), ','||index_to_match||':', 1) AS spos,
         INSTR(
           TRANSLATE(name, '{}', ',,'),
           ',',
           INSTR(TRANSLATE(name, '{}', ',,'), ','||index_to_match||':', 1, 1) + 3
         ) AS epos
  FROM   table_name
         CROSS JOIN (SELECT 2 AS index_to_match FROM DUAL)
  WHERE  id = 1
)


NAME


john