Hi all, Has anyone succeeded in parsing JSON string via SuiteQL? Sample query: ---

SELECT JSON_QUERY( '{a:1, b:2, c:3}', '$.a' ) AS value FROM DUAL

--- Expected result:

1

It's not a supported function in SuiteQL: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_158513731864.html#SuiteQL-Supported-and-Unsupported-Functions

Thanks, Shai. It was example of wanted outcome. However, the question remains - is it possible anyhow to achieve parsing a JSON via SuiteQL?

It's not recommended, but for trivial cases in a consistent format, you might be able to use a regex.

SELECT REGEXP_SUBSTR('{"key1": "abc", "key2": "def", "key3": "ghi"}', '"key1"\s*:\s*([^,}]*)', 1, 1, '', 1)

=>

"abc"

Otherwise SuiteQL isn't the tool for that

I'm trying to imagine a use case where I'd EVER want to do something like this? care to share?

@Anthony OConnor : Good question. I came to a client who has absurdly complex dependencies. One of them is a Custom Record with JSON of related records. They can't search it nor join it, obviously. --- I too wonder what made the original dev design it this way.