Select part from the values in SQL

I have such problem/question. I am trying to extract data from database, but only part. The real example is:

{ 
   "email":"[e-mail]",
   "addinfo":{ 
      "invoice_id":"1F5FspmpyfQ"
   },
   "cardholder":"blabla",
   "masked_pan":"123456XXXXXX1234"
}

I need to receive only 1F5FspmpyfQ, all between {"invoice_id": " and "},.

Answer

You can use Postgres’ JSON functions:

select the_column::jsonb -> 'addinfo' ->> 'invoice_id' as invoice_id
from the_table;

-> returns a json object with the specified key and ->> returns the key’s value as a text (rather than jsonb)