sqlpostgresqlmetabase

JSON Status Extract - Metabase SQL query

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"

Solution

  • You can do it using substring from :

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

    Demo here