Get part of string in Postgres-CodePudding

Can someone help what i need to write to find the and print the ID in this string.

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;

I only want the get 188715 in return. The lenght of the string can change but this part is always the same "id",188710]. So i can say i want everything after "id", until ] but i don't know how.

CodePudding user response：

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

CodePudding user response：

This regex \["\ ","id",(\d )\] in the place of ???? will extract it. BTW the value in the example is 188710, not 188715.

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 '\["\ ","id",(\d )\]') as id
from t;

Edit: As @PrasadU suggests, here is an alternative.

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]]'),
  ('[[" ","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",188711],[" ","ist_aktiv",true]]')
),
ja as 
(
 select jsonb_array_elements(txt::jsonb) arr from t
)
select arr ->> 2 as id from ja where arr ->> 0 = ' ' and arr ->> 1 = 'id';