Extract Transaction ID from from column with JSON Array-CodePudding

I have a column with a JSON Array and I need to extract transaction_id if there is one:

{
  "transaction_id": "222222",
  "feature_id": "e57dcd3c-7adf-11eb-9439-0242ac130002",
  "provider_type": "thm",
  "transaction_date": "2020-11-14",
  "account_number": "XXXX",
  "transaction_vendor": "Visa",
  "transaction_type": "DEBIT",
  "alert_type": "Large Card Purchase",
  "threshold_amount": "1250.0",
  "profile_id": "4fbf9b51-c737-434d-ae5c-63a35ac4a75a",
  "alert_id": "d1cd1f80-ed0c-49c3-bf97-e71834f4aaa1",
  "account_name": "BankXX",
  "provider_id": "2",
  "alert_date": "2022-11-05T01:31:17.000Z",
  "create_date": "2022-11-05T01:31:17.000Z"
}

CodePudding user response：

It could be as simple as this

Declare @YourTable Table ([ID] varchar(50),[JsonStr] varchar(max))  Insert Into @YourTable Values 
 (1,'{"transaction_id": "222222","feature_id": "e57dcd3c-7adf-11eb-9439-0242ac130002","provider_type": "thm","transaction_date": "2020-11-14","account_number": "XXXX","transaction_vendor": "Visa","transaction_type": "DEBIT","alert_type": "Large Card Purchase","threshold_amount": "1250.0","profile_id": "4fbf9b51-c737-434d-ae5c-63a35ac4a75a","alert_id": "d1cd1f80-ed0c-49c3-bf97-e71834f4aaa1","account_name": "BankXX","provider_id": "2","alert_date": "2022-11-05T01:31:17.000Z","create_date": "2022-11-05T01:31:17.000Z"}')
 
Select ID 
      ,Trans_ID  = json_value(JsonStr,'$.transaction_id')
 From @YourTable

Results

ID  Trans_ID
1   222222

CodePudding user response：

I have used next:

JSON_VALUE(args, '$.transaction_id')