IS_VALID_JSON_ARRAY function
Note
JSON_PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.
Instead of using IS_VALID_JSON_ARRAY, we recommend that you parse your JSON strings using the JSON_PARSE function to get a SUPER value. Then, use the IS_ARRAY function function to confirm that the array is properly formed.
The IS_VALID_JSON_ARRAY function validates a JSON array. The function returns Boolean
true if the array is properly formed JSON or
false if the array is malformed. To validate a JSON
string, use IS_VALID_JSON function
For more information, see JSON functions.
Syntax
IS_VALID_JSON_ARRAY('json_array')
Arguments
- json_array
-
A string or expression that evaluates to a JSON array.
Return type
BOOLEAN
Examples
To create a table and insert JSON strings for testing, use the following example.
CREATE TABLE test_json_arrays(id int IDENTITY(0,1), json_arrays VARCHAR); -- Insert valid JSON array strings -- INSERT INTO test_json_arrays(json_arrays) VALUES('[]'), ('["a","b"]'), ('["a",["b",1,["c",2,3,null]]]'); -- Insert invalid JSON array strings -- INSERT INTO test_json_arrays(json_arrays) VALUES('{"a":1}'), ('a'), ('[1,2,]');
To validate the strings in the preceding example, use the following example.
SELECT json_arrays, IS_VALID_JSON_ARRAY(json_arrays) FROM test_json_arrays ORDER BY id;+------------------------------+---------------------+ | json_arrays | is_valid_json_array | +------------------------------+---------------------+ | [] | true | | ["a","b"] | true | | ["a",["b",1,["c",2,3,null]]] | true | | {"a":1} | false | | a | false | | [1,2,] | false | +------------------------------+---------------------+
