Product Documentation

Appearance

All platforms

CHECK_JSON ​

Validates a JSON string, which is useful for checking the format before inserting it into a JSONB column. The function returns NULL if the string is a well-formed JSON or if it is NULL. If the JSON string is malformed, it returns an error message with a location of the error.

sql
CHECK_JSON(json_string)
json_string
A JSON expression to validate.

Examples ​

sql
select check_json('{"ticker": ');
-- Invalid value. Pos: 11

select check_json('{"ticker": "INTC"}');
-- null

select check_json('{ "key1" : 1, "key2" : 2, "key1" : 3 }');
-- the key 'key1' is not unique

select check_json('{ "key1" : 1, "key2 : 2 }');
-- Missing a closing quotation mark in string. Pos: 25

select check_json('"key1""key2"');
-- The document root must not be followed by other values. Pos: 3

select check_json('{:100}');
-- Missing a name for object member. Pos: 1

select check_json('{"key1" 100}');
-- Missing a colon after a name of object member. Pos: 5

select check_json('{"key1":100"key2":200,"key3" 300}');
-- Missing a comma or '}' after an object member. Pos: 8

select check_json('"\ufffg"');
-- Incorrect hex digit after \u escape in string. Pos: 1

select check_json('{"emoji": "\uDE0A\uD83D"}');
-- The surrogate pair in string is invalid. Pos: 11