Extracting mutiple data items from DB with JSON string format column

[mmiah1982]

Hi,

Just need some assistance if someone can point me in the right direction it would be greatly appreciated.

I have the following table structure

contactID attributes
1234 {"channel":"Web","classification":"Mortgage","customername":"Mike","customerphonenumber":"+44123432","description":"Current Accounts","sentiment":"NEUTRAL"}
1298 {"channel":"Email","classification":"Savings","customername":"Joe","customerphonenumber":"+44345324","description":"Personal Account","sentiment":"POSTIVE"}

I can pull back one value from the attributes string, but require to pull back some others can anyone point me in the right direction in what i need to change in querey below which pulls back description only at the moment, if i wanted to also pull back the sentiment can someone help with the syntax pls

Using following

SELECT contactid, json_extract(attributes, '$.description') AS "description"
FROM myTable

Pulls back contactID and description, i'd like to also pull back sentiment value (from attributes column highlighted in red) aswell

Thank you

 

[mikrom]

does this work ?

SELECT 
  contactid, 
  json_extract(attributes, '$.description') AS "description",
  json_extract(attributes, '$.sentiment') AS "sentiment"
FROM myTable

 

[mmiah1982]

Yes perfect! Thanks Mikrom!