sqljsonpostgresqlmetabase

JSON Extract JSON in Metabase SQL


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


Solution

  • 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);