在Postgres中获取字符串的一部分

Question

有人可以帮助我需要写什么来找到并打印这个字符串中的ID。

with t(txt) as
(
values
('[["+","created_at","20210713T08:30:16.590+02:00"],["+","default_zahlungsverbindung_id",null],["+","firmen_gruppe_id",null],["+","gueltig_bis",null],["+","hat_mahnsperre",false],["+","hat_zahlsperre",false],["+","heimatort",null],["+","id",188710],["+","ist_aktiv",true]]')
)
select 
substring (txt from ????) as id
from t;

我只想要得到188715作为回报。字符串的长度可以改变，但这部分始终是相同的"id"，188710]。所以我可以说我想要在"id"，直到]之后的所有内容，但我不知道怎么做。

Answer 1

with t(txt) as
(
values
('[["+","created_at","20210713T08:30:16.590+02:00"],["+","default_zahlungsverbindung_id",null],["+","firmen_gruppe_id",null],["+","gueltig_bis",null],["+","hat_mahnsperre",false],["+","hat_zahlsperre",false],["+","heimatort",null],["+","id",188710],["+","ist_aktiv",true]]')
)
select regexp_replace(txt, '.*"id"s*,s*(d*)s*.*]','1') as id from t;```
ps - it looks like proper json - if its already json in DB, use proper json functions https://www.postgresql.org/docs/current/functions-json.html