topic Re: Update Values in enrichment_json in Fraud, AML and Security Intelligence

Update Values in enrichment_json

chaudharydeepak — Wed, 03 May 2023 08:54:12 GMT

Dears,

Hope this email finds you well.

Is there any way to change only the aging values in the enrichment_json_doc column of tdc_alert?

For Example:

As given below I want to update the aging values in the tdc_alert table every day, but the rest of the values stored in the enrichment_json_doc column should remain the same.

{"aging": "31 Days", "branch_name": ".", "customer_name": ".", "customer_type": ".", "sender_number": "c_num", "customer_number": ".", "transaction_number": ".", "reason_for_suspension": "."}

Thank you for your attention to this matter.

Deepak

Re: Update Values in enrichment_json

Patrick — Wed, 03 May 2023 09:59:32 GMT

A json is just a text file. You need to either read it into SAS, change the value and then recreate the json OR you just treat it as text file where you read it as string and do a simple text replace using string functions.
If using SAS for directly changing the text file one would need to know if you have line delimiters (LF ?) or if the whole json is on a single line.

Re: Update Values in enrichment_json

chaudharydeepak — Wed, 03 May 2023 11:13:22 GMT

Dear Patrick,

I'm having a data set and in the data, I do have a column as JSON, in which I need to update the record.

If you can share the scripts, it will be a great help if you could share?

Re: Update Values in enrichment_json

Patrick — Wed, 03 May 2023 11:53:38 GMT

@chaudharydeepak wrote:

Dear Patrick,

I'm having a data set and in the data, I do have a column as JSON, in which I need to update the record.

If you can share the scripts, it will be a great help if you could share?

If that's a SAS table then use SAS string functions to replace the value. If you can search for a fixed string and replace it with another fixed string then code as below should do.

data sample;
  json_column='{"aging": "31 Days", "branch_name": ".", "customer_name": ".", "customer_type": ".", "sender_number": "c_num", "customer_number": ".", "transaction_number": ".", "reason_for_suspension": "."}';
  json_column=transtrn(json_column,'"aging": "31 Days"','"aging": "40 Days"');
run;

If you need to search for a text pattern like "aging": "?? Days" and replace it with something variable then getting the string functions right could take a bit more but is still very doable.
You will have to provide representative sample data that cover all your cases and then explain what you have and what you need - like that we need to take the aging value as found in the data and increase it by 1. You would in such a case also need to be clear if there could be no value and if that would mean we've got a dot instead ...and if we then would need to set the value to 1 or leave it "missing". ....stuff like that you need to tell us in detail.