i have this table
id | status | outgoing |
---|---|---|
1 | paid | {"a945248027_14454878":"processing"} |
2 | unpaid | {"old.a945248027_14454878":"cancelled"} |
i am trying to extract the value after underscore i.e 14454878
i tried extracting the keys using this query on metabase
select id, outgoing,
substring(key from '_([^_]+)$') as key
from table,
cross join lateral jsonb_object_keys(outgoing) as j(key);
but i keep getting the error
ERROR: function jsonb_object_keys(json) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 129
Please help
The column is defined as json but i used a function that expects jsonb. so i changed Use jsonb_object_keys() to jsonb_object_keys()
select id, outgoing,
substring(key from '_([^_]+)$') as key
from table,
cross join lateral json_object_keys(outgoing) as j(key);