I am trying to search message codes within 1 table and think it would be easier if I create a new column that specifies each code within the column with the corresponding formula.
So I am trying to create a new column within the table called MessageCode which specifies each number after "MessageCode": but I am not sure how to do this. In the end I want a table that basically gives back Id, Title, Enabled, Message Code and Actions.
If you need further information please let me know.Also I am using SSMS.
ID Title Enabled Mark Colour Condition Action
1914 ABW: 012 1 -256 {"Operator":1,"Children":[{"Operator":1,"LeftOperand":} [{"ActionType":1,"Results":[{"ExcludeApplicationGroups":[5],"IncludeEZY":false,"StartDate":"2022-07-08T00:00:00","MessageCode":9109,"Ordinal":32,"Level":2,"Status":2,"LocalisedMessages":[{"Message":"<p>Passengers are advised to hold travel insurance that covers Covid-19.</p>"},{"LanguageId":50,"Message":"<p>Passagiers wordt aangeraden een reisverzekering af te sluiten die het Covid-19 dekt.</p>"},{"LanguageId":75,"Message":"<p><strong> </strong>Il est conseillé aux passagers d'avoir une assurance voyage qui couvre le Covid-19.</p>"},{"LanguageId":156,"Message":"<p><strong> </strong>Se aconseja a los pasajeros que tengan un seguro de viaje que cubra Covid-19.</p>"},{"LanguageId":140,"Message":"<p><strong> </strong>Os passageiros são aconselhados a possuir um seguro de viagem que cubra o Covid-19.</p>"},{"LanguageId":141,"Message":"<p><strong> </strong>Aconselha-se os passageiros a possuírem um seguro de viagem que cubra o Covid-19.</p>"}]}]}]
You can try to use the below query to read the value from Action column
SELECT ID, Title, Enabled, MarkColour, Condition,
JSON_VALUE(Action, '$[0].Results[0].MessageCode') AS MessageCode
FROM <YourTableName>