Using JSON in saved search formula Netsuite

Question

How to get value from JSON in Case Formula Netsuite from a free-form text field

For example, if we have a field with ID custcol_test and it has a Json:

{"a": "testa"}

When we print the formula in a formula text column like below

{custcol_test}

It prints the JSON but we need the value testa from JSON based on the key a

Answer 1

REGEXP_SUBSTR({custcol_test}, '"a":"(.+?)"', 1, 1, NULL, 1)

Replace the "a" with whatever key of which you'd like the value.

Example: If your key was 'test', then you'd put "test" like this:

REGEXP_SUBSTR({custcol_test}, '"test":"(.+?)"', 1, 1, NULL, 1)

This will only work if your field contains valid JSON.

REGEXP_SUBSTR Documentation