英文:
extract json in column
问题
我有一个表格
id | status | outgoing
1 | paid | {"a945248027_14454878":"processing","old.a945248027_14454878":"cancelled"}
2 | pending| {"069e5248cf_45299995":"processing"}
我正在尝试提取出传出列每个下划线后的值，例如从`a945248027_14454878`中，我想要`14454878`由于JSON数据不规范，我似乎无法弄清楚。
英文:
I have a table
id | status | outgoing-------------------------1 | paid | {"a945248027_14454878":"processing","old.a945248027_14454878":"cancelled"}2 | pending| {"069e5248cf_45299995":"processing"}
I am trying to extract the values after each underscore in the outgoing column e.g from a945248027_14454878 I want 14454878
Because the json data is not standardised I can't seem to figure it out.
答案1
得分: 1
使用正则表达式版本的 substring 可以提取下划线后的 JSON 键部分。
select id, status, outgoing,substring(key from '_([^_]+)$') as keyfrom the_table, lateral jsonb_object_keys(outgoing) as j(key);
查看 演示。
英文:
You may extract the json key part after the underscore using regexp version of substring.
select id, status, outgoing,substring(key from '_([^_]+)$') as keyfrom the_table, lateral jsonb_object_keys(outgoing) as j(key);
See demo.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论