提取列中的 JSON 数据。

huangapple go评论99阅读模式
英文:

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 key
from 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 key
from the_table, lateral jsonb_object_keys(outgoing) as j(key);

See demo.

huangapple
  • 本文由 发表于 2023年2月8日 19:51:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/75385419.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定