JSON Status Extract - Metabase SQL query

Question

i have this table in metabase

id	status	outgoing
1	paid	{"a945248027_14454878":"processing"}
2	unpaid	{"old.a945248027_14454878":"cancelled"}

i am trying to extract the value after colon in the "outgoing" json field i.e processing, cancelled"

Answer 1

You can do it using substring from :

select id, status, substring(outgoing::varchar from ':"([a-z]*)' )
from mytable t

Demo here