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?
Advertisement
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;