CODEWITHSUNDEEP
sqljsondb2
hadoo
hadoo

Reputation: 213

Parse JSON in DB2

I am attempting to parse a JSON field in DB2. I am able to utilize JSON_VALUE and JSON_TABLE in Oracle without issue, but I am getting an error in DB2.

If I run this simple test in Oracle from DUAL it returns the currenttimestamp and 100. When I try it in DB2 it returns the currenttimestamp, but an error for the JSON_VALUE. I have defaulted it to say 'Not Found', without that it is a null/blank value.

SELECT CURRENT TIMESTAMP AS CURRENT_TIMESTAMP,
       JSON_VALUE('{a:100}', '$.a' DEFAULT 'Not found' ON ERROR) AS JSON_PARSE
FROM SYSIBM.SYSDUMMY1

Upvotes: 1

Views: 2855

Answers (1)

data_henrik
data_henrik

Reputation: 17156

It seems the error is caused by invalid JSON as input to JSON_VALUE. This works for me (with quotes around "a"):

SELECT CURRENT TIMESTAMP AS CURRENT_TIMESTAMP,
       JSON_VALUE('{"a":100}', '$.a' DEFAULT 'Not found' ON ERROR) AS JSON_PARSE
FROM SYSIBM.SYSDUMMY1

Is Oracle accepting invalid JSON objects?

Upvotes: 2

Related Questions