Parse text of json object key value postgresql

Question

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?

Ftisiot · Accepted Answer · 2021-05-05 11:30:59Z

1

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;

answered May 5, 2021 at 11:30

Ftisiot

1,8981 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse text of json object key value postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related