英文:
JSON Extract JSON in Metabase SQL
问题
| id | status | outgoing |
|---|---|---|
| 1 | paid | {"a945248027_14454878":"processing"} |
| 2 | unpaid | {"old.a945248027_14454878":"cancelled"} |
我尝试提取下划线后的值，即14454878
我尝试使用Metabase中的此查询提取键：
select id, outgoing,
substring(key from '_([^_]+)$') as key
from table,
cross join lateral jsonb_object_keys(outgoing) as j(key);
但我一直遇到错误：
错误：函数jsonb_object_keys(json)不存在
提示：没有与给定名称和参数类型匹配的函数。您可能需要添加显式类型转换。
位置：129
英文:
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
答案1
得分: 0
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 jsonb_object_keys(outgoing) as j(key);
英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论