Parse text of json object key value postgresql

I have the following kind of objects maintained in a table column. I have tried parsing this object to get the value for this key “1075852262” which has this value “Event="13" Description="(EMOVIES r8)"..........“

{
  "1075850368": "177,227,145,146,147,154,173,174,175,176,223",
  "1075849148": "EMPLOYEE is a person that works for the VIDEO STORE",
  "1075851720": "17",
  "1075849138": "",
  "1075850198": "145,146,147,223,154,176,175,174,173,177,227",
  "1075850197": "177,227,145,146,154,147,173,174,175,176,223",
  "1075851263": "",
  "1075850195": "200,225,353",
  "1073742125": "EMPLOYEE is a person that works for the VIDEO STORE",
  "1073742126": "EMPLOYEE",
  "1075850192": "221,225",
  "1075851281": "385,416,453,479,524",
  "1075848978": "1",
  "1075852262": "Event="13" Description="(EMOVIES r8)" SourcePath="" Comment="" CreateTime="2010-10-28 12:55:44" Creator="krima03" HistoryId="{DF11AC23-4DD3-4D95-BE05-98D0CA399A29}" SourceId="" SourceShortId="",Event="2" Description="" SourcePath="" Comment="" CreateTime="2010-03-08 15:26:22" Creator="" HistoryId="{6888FE25-DCC9-4694-8DAD-F49B4051C1B3}" SourceId="" SourceShortId="",Event="11" Description="" SourcePath="" Comment="" CreateTime="2010-08-07 13:54:20" Creator="daspi02" HistoryId="{4B806783-D70E-4A78-928F-E41DE366B970}" SourceId="" SourceShortId=""",
  "1075850728": "0",
  "1075851722": "16"
}

The SQL I have written is:

select o_id, array_to_json(array[to_json(o_propertyblob1::json->'1075852262')])->>0 from tableone; 

The result it gives is like this:

Event="13" Description="(EMOVIES r8)" SourcePath="" Comment="" CreateTime="2010-10-28 12:55:44" Creator="krima03" HistoryId="{FBA00637-9F62-4230-BA49-9A6D61485CD4}" SourceId="" SourceShortId="",Event="2" Description="" SourcePath="" Comment="" CreateTime="2010-03-08 15:49:06" Creator="" HistoryId="{FF9AEF73-9E12-413F-A340-A2011B81BC12}" SourceId="" SourceShortId="",Event="11" Description="" SourcePath="" Comment="" CreateTime="2010-08-07 13:54:20" Creator="daspi02" HistoryId="{FB4C69B6-6E00-45C2-B8CF-DE32CEF89F34}" SourceId="" SourceShortId=""

Now, I want to fetch only Description value out of it i.e. (EMOVIES r8). How can I do this?

Answer

The substring method using regular expressions is probably what you’re looking for. I solved your case using the following

with data as 
(
select 'Event="13" Description="(EMOVIES r8)" SourcePath="" Comment="" CreateTime="2010-10-28 12:55:44" Creator="krima03" HistoryId="{FBA00637-9F62-4230-BA49-9A6D61485CD4}" SourceId="" SourceShortId="",Event="2" Description="" SourcePath="" Comment="" CreateTime="2010-03-08 15:49:06" Creator="" HistoryId="{FF9AEF73-9E12-413F-A340-A2011B81BC12}" SourceId="" SourceShortId="",Event="11" Description="" SourcePath="" Comment="" CreateTime="2010-08-07 13:54:20" Creator="daspi02" HistoryId="{FB4C69B6-6E00-45C2-B8CF-DE32CEF89F34}" SourceId="" SourceShortId=""' colname

)
select substring(colname from '%Description="#"[A-Za-z0-9 ()]+#"%' for '#') from data;