JSON Functions

JSON_APPEND

JSON_APPEND(json_doc, path, val[, path, val] ...)

Description: Appends a value to path in json_doc and returns the modified JSON. JSON_APPEND is an alias of JSON_ARRAY_APPEND. Its behavior is the same as that of JSON_ARRAY_APPEND.

• The value of path must be an array or object. If an array is specified, a new value is added to the end of the array. If the key of an object is specified, a new value is added to the end of the object value. If an object is a separate value, the object is converted into an array and a new value is added to the end.
• If json_doc or path is null, the function will return null.
• If the specified path cannot be found in json_doc, no modifications will be applied.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Parameters: See Table 1.

Return type: JSON.

Example:

m_db=# SELECT json_append('[1, [2, 3]]', '$[1]', 4, '$[0]', false, '$[0]', null, '$[0]', 1);
 json_append
----------------------------------------------------------------
 [[1, false, null, 1], [2, 3, 4]]
(1 row)

JSON_ARRAY

JSON_ARRAY([val[, val] ...])

Description: Constructs an array from a variable parameter list and returns a JSON array. If the function does not have any parameters, an empty JSON array is returned.

Return type: JSON.

Example:

-- If no input parameter is entered, an empty JSON array is returned.
m_db=# SELECT json_array();
 json_array
------------
 []
(1 row)

m_db=# SELECT json_array(TRUE, FALSE, NULL, 114, 'text');
 json_array
-------------------------------------------------------------------
 [true, false, null, 114, "text"]
(1 row)

JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

Description: Adds a value to path in json_doc and returns the modified JSON.

• The value of path must be an array or object. If an array is specified, a new value is added to the end of the array. If the key of an object is specified, a new value is added to the end of the object value. If an object is a separate value, the object is converted into an array and a new value is added to the end.
• If json_doc or path is null, the function will return null.
• If the specified path cannot be found in json_doc, no modifications will be applied.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Parameters: See Table 1.

Return type: JSON.

Example:

-- Appends values to both objects and arrays.
m_db=# SELECT json_array_append('[1, [2, 3]]', '$[1]', 4, '$[0]',3);
 json_array_append
----------------------
 [[1, 3], [2, 3, 4]] 
(1 row)

-- Appends a value to the value of a dictionary object.
m_db=# SELECT json_array_append('{"name":"Tom"}','$.name', 2);
 json_array_append
------------------------------------------------------------------
 {"name": ["Tom", 2]}
(1 row)

-- Appends a value to the dictionary object itself.
m_db=# SELECT json_array_append('{"name":"Tom"}','$', 2);
 json_array_append
------------------------------------------------------------------
 [{"name": "Tom"}, 2]
(1 row)

JSON_ARRAY_INSERT

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

Description: Inserts a value into the specified location in the array indicated by path in json_doc and returns the modified JSON.

• If the specified path is not a JSON array, an error is reported.
• If a value has existed in the specified path, a new value is inserted into the path and the existing value is moved backward.
• If the specified path cannot be found in json_doc, no modifications will be applied.
• If json_doc or path is null, the function will return null.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Parameters: See Table 1.

Return type: JSON.

Example:

-- Example of one json_path-value pair
m_db=# SELECT json_array_insert('[1, [2, 3]]', '$[1]', 4);
 json_array_insert
-------------------
 [1, 4, [2, 3]]
(1 row)

-- Example of multiple json_path-value pairs
m_db=# SELECT json_array_insert('[1, [2, 3]]', '$[1]', 4, '$[0]', false, '$[0]', null, '$[0]', 1);
 json_array_insert
------------------------------------------------------------------
 [1, null, false, 1, 4, [2, 3]]
(1 row)

-- The specified path is a JSON array.
m_db=# select json_array_insert('{"a":[1]}','$.a[1]',2);
 json_array_insert 
-------------------
 {"a": [1, 2]}
(1 row)

-- The specified path does not exist.
m_db=# select json_array_insert('{"a":1}','$[1]',2);
 json_array_insert 
-------------------
 {"a": 1}
(1 row)

JSON_CONTAINS

JSON_CONTAINS(target_json_doc, candidate_json_doc[, path])

Description: Checks whether target_json_doc contains candidate_json_doc. If path is specified, the system checks whether target_json_doc contains candidate_json_doc after matching the sub-JSON data in target_json_doc using path.

• If target_json_doc is in an incorrect format, the function will report an error. If target_json_doc is null, the function will return null.
• If candidate_json_doc is in an incorrect format, the function will report an error. If candidate_json_doc is null, the function will return null.
• If path is in an incorrect format, the function will report an error. If path is null, the function will return null.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: BIGINT.

Example:

m_db=# SELECT json_contains('[1, [2, 3], 4]', '[1, 3]');
 json_contains 
---------------
             1
(1 row)

m_db=# SELECT json_contains('{"a": 1, "b": {"c": 3, "d": 4}}', '{"d": 4}', '$.b');
 json_contains 
---------------
             1
(1 row)

m_db=# SELECT json_contains('{"a": 1, "b": {"c": 3, "d": 4}}', '{"e": 4}', '$.b');
 json_contains 
---------------
             0
(1 row)

JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, one_or_all, path [, path [, path ...]])

Description: Queries whether sub-JSON data can be matched at the position specified by path in json_doc.

• When one_or_all is set to one, 1 is returned as long as a value in path matches the sub-JSON data.
• If one_or_all is set to all, 1 is returned only when all values of path match sub-JSON data. Otherwise, 0 is returned.
• If json_doc is in an incorrect format, the function will report an error. If json_doc is null, the function will return null.
• If the one_or_all format is incorrect, an error is reported. If the value of one_or_all is null, null is returned.
• When one_or_all is set to one:
  • If the previous paths are in the correct format and at least one of them is found in json_doc, the function will not check subsequent paths for null values or errors; instead, it will directly return 1.
  • If the previous paths are in the correct format and none of them are found in json_doc, the function will return null if it detects a null value in subsequent paths first, or report an error if it detects a format error first.
• When one_or_all is set to all:
  • If the previous paths are in the correct format and at least one of them cannot be found in json_doc, the function will not check subsequent paths for null values or errors; instead, it will directly return 0.
  • If the previous paths are in the correct format and all of them are found in json_doc, the function will return null if it detects a null value in subsequent paths first, or report an error if it detects a format error first.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: BIGINT.

Example:

m_db=# SELECT json_contains_path('{"a": {"b": 2, "c": 3}, "d": null}', 'one', '$.d', '$.e');
 json_contains_path 
--------------------
                  1
(1 row)


m_db=# SELECT json_contains_path('{"a": {"b": 2, "c": 3}, "d": null}', 'all', '$.d', '$.e');
 json_contains_path 
--------------------
                  0
(1 row)

JSON_DEPTH

JSON_DEPTH(json_doc)

Description: Returns the maximum depth of a JSON. If json_doc is null, the function will return null. If json_doc contains invalid or empty character strings, or if the JSON depth exceeds 100, the function will report an error.

Return type: BIGINT.

Example:

m_db=# SELECT JSON_DEPTH('[]');
 json_depth 
------------
          1
(1 row)

m_db=# SELECT JSON_DEPTH('{"s":1}');
 json_depth 
------------
          2
(1 row)

m_db=# SELECT JSON_DEPTH('{"s":1, "x":2,"y":3}');
 json_depth 
------------
          2
(1 row)

m_db=# SELECT JSON_DEPTH('{"s":1, "x":2,"y":[1]}');
 json_depth 
------------
          3
(1 row)

JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path...])

Description: Extracts sub-JSON data based on path in json_doc. There can be multiple paths. This function concatenates the extracted sub-JSON data into a JSON array and returns the array. If only one sub-JSON data is matched, the sub-JSON data is directly returned.

• If json_doc is in an incorrect format, the function will report an error. If json_doc is null, the function will return null.
• If path is in an incorrect format, the function will report an error. If path is null, the function will return null.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: JSON.

Example:

m_db=# SELECT json_extract('{"a": {"b": 2, "c": 3}, "d": 4}', '$.a');
   json_extract   
------------------
 {"b": 2, "c": 3}
(1 row)

m_db=# SELECT json_extract('{"a": {"b": 2, "c": 3}, "d": 4}', '$.a', '$.a');
             json_extract             
--------------------------------------
 [{"b": 2, "c": 3}, {"b": 2, "c": 3}]
(1 row)

JSON_INSERT

JSON_INSERT(json_doc, path, val[, path, val] ...)

Description: Inserts a value into path in json_doc and returns the modified JSON. JSON_INSERT can only insert data into a path that does not exist. If a value exists in the specified path, the value is not modified.

• If json_doc or path is null, the function will return null.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Parameters: See Table 1.

Return type: JSON.

Example:

-- If a value has existed in the input path, the insertion does not take effect.
m_db=# SELECT json_insert('{"x": 1, "y": [1, 2]}', '$.x', '2');
 json_insert
-------------------
 {"x": 1, "y": [1, 2]}
(1 row)

-- If there are multiple directories, insert values in sequence.
m_db=# SELECT json_insert('[1, [2, 3], {"a": [4, 5]}]', '$[10]', 10,'$[5]', 5);
 json_insert
------------------------------------------------------------------
 [1, [2, 3], {"a": [4, 5]}, 10, 5] 
(1 row)

JSON_KEYS

JSON_KEYS(json_doc[, path])

Description: Returns the key of the top-level object in json_doc as a JSON array. If path exists, the key of the top-level object in json_doc, which matches path, is returned as a JSON array.

• If json_doc or path is null, or if the specified path cannot be found in json_doc, the function will return null for JSON_KEYS.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: JSON.

Example:

m_db=# SELECT json_keys('{"a": {"b": 2, "c": 3}, "d": 4}');
 json_keys  
------------
 ["a", "d"]
(1 row)

m_db=# SELECT json_keys('{"a": {"b": 2, "c": 3}, "d": 4}', '$.a');
 json_keys  
------------
 ["b", "c"]
(1 row)

JSON_LENGTH

JSON_LENGTH(json_doc[,path])

Description: Returns the length of a specified path in JSON. If no path is specified, the length of JSON is returned.

• If json_doc or path is null, or if the specified path cannot be found in json_doc, the function will return null.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: BIGINT.

Example:

-- Embedded arrays or objects are not involved in length calculation. For example, the length of {"x": [1, 2]} is 1.
m_db=# SELECT json_length('{"name":"Tom", "age":24, "like":["football", "basketball"]}');
 json_length
-------------
          3
(1 row)

-- The length of an object is the number of object members. For example, the length of {"x":1} is 1.
m_db=# SELECT json_length('{"a":["abc","bcd"],"b":"abc"}','$.a');
 json_length
-------------
          2
(1 row)

JSON_MERGE

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

Description: The input parameter contains multiple json_doc. When the number of json_doc is greater than or equal to 2, the JSON object is constructed from the variable parameter list. This function is used to combine all input json_doc files and return the combination result.

• If any input parameter is null, the function will return null.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.
• JSON_MERGE is the alias of JSON_MERGE_PRESERVE. Its behavior is the same as that of JSON_MERGE_PRESERVE. The message "'JSON_MERGE' is deprecated and will be removed in a future release." is displayed each time when the API is called. Please use the "JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead." warning information.

Return type: JSON.

Example:

-- The two arrays are combined into one array, and all elements in the array are retained.
m_db=# SELECT json_merge('[1, 2]','[2]');
 json_merge 
---------------------
 [1, 2, 2]
(1 row)

-- A pure value is wrapped into an array and merged as an array. The pure value must be enclosed in single quotation marks as an input parameter. If the pure value indicates a character, it must be enclosed in double quotation marks.
m_db=# SELECT json_merge('[3, 2]','1');
 json_merge      
------------------------------
 [3, 2, 1]
(1 row)

-- Two objects are combined into one object, and all keys and values are retained. Objects are re-sorted when they are combined. When an object and an array are merged, the object is wrapped into an array and merged as an array.
m_db=# SELECT json_merge('{"b":"2"}','{"a":"1"}','[1,2]');
 json_merge      
------------------------------
 [{"a": "1", "b": "2"}, 1, 2]
(1 row)

JSON_MERGE_PATCH

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

Description: Performs RFC 7396-compliant merge on two or more json_doc files and returns the merge result. Members with duplicate keys are not retained.

• If any json_doc parameter is invalid, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: JSON.

Example:

m_db=# SELECT json_merge_patch('{"b":"2"}','{"a":"1"}');
   json_merge_patch   
----------------------
 {"a": "1", "b": "2"}
(1 row)

m_db=# SELECT json_merge_patch('{"b":"2"}','{"a":"1"}','[1,2]');
 json_merge_patch 
------------------
 [1, 2]
(1 row)

JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

Description: The input parameter is multiple json_doc files. When the number of json_doc files is greater than or equal to 2, the JSON object is constructed from the variable parameter list. This function combines all input JSON files and returns the combination result.

• If any input parameter is null, the function will return null.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: JSON.

Example:

JSON_OBJECT

JSON_OBJECT([key, val[, key1, val1] ...])

Description: Constructs a JSON object from a variable parameter list. The number of input parameters must be an even number. key and val form a key-value pair. If the value of key is null or the number of input parameters is an odd number, an error is reported.

Return type: JSON.

Example:

m_db=# SELECT json_object('d',2,'c','name','b',true,'a',2,'a',NULL,'d',1);
 json_object
------------------------------------------
 {"a": 2, "b": true, "c": "name", "d": 2}
(1 row)

m_db=# SELECT json_object();
 json_object 
-------------
 {}
(1 row)

JSON_QUOTE

JSON_QUOTE(str)

Description: Use double quotation marks to quote the input parameter str and output it as a JSON string value.

Return type: TEXT.

Example:

m_db=# SELECT json_quote('123');
 json_quote 
------------
 "123"
(1 row)

m_db=# SELECT json_quote('"1"');
 json_quote 
------------
 "\"1\""
(1 row)

m_db=# SELECT json_quote('"NULL"');
 json_quote 
------------
 "\"NULL\""
(1 row)

JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] ...)

Description: Deletes a value from path in json_doc and returns the modified JSON. If the specified path cannot be found in json_doc, no modifications will be applied. If there are multiple paths in the input, the function will delete subsequent paths based on the updated json_doc after the previous paths have been deleted.

• If json_doc or path is null, the function will return null.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains non-existing paths, null values, and format errors at the same time, the exceptions are processed in the order of their occurrence.
• If the depth of json_doc exceeds 100, the function will report an error.

Parameters: json_doc and path. For details, see Table 1.

Return type: JSON.

Example:

-- Multiple paths can be accepted and executed from left to right. When multiple paths are executed, the json_remove operation is added based on the deleted JSON.
m_db=# SELECT json_remove('[0, 1, 2, [3, 4]]', '$[0]', '$[0]','$[0]');
 json_remove
----------------------------------------------------------------
 [[3, 4]]
(1 row)

-- Delete the path that does not exist and return.
m_db=# SELECT json_remove('{"A":1, "B":2}', '$.C');
 json_remove
----------------------------------------------------------------
 {"A": 1, "B": 2}
(1 row)

JSON_REPLACE

JSON_REPLACE(json_doc, path, val[, path, val] ...)

Description: Replaces the value in path in json_doc with a new value and returns the modified JSON.

• If the specified path cannot be found in json_doc, no modifications will be applied, and json_doc will be returned as is.
• If json_doc or path is null, the function will return null.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Parameters: See Table 1.

Return type: JSON.

Example:

m_db=# SELECT json_replace('{"x": 1}', '$.x', true);
 json_replace
----------------------------------------------------------------
 {"x": true}
(1 row)

-- If value is a character string, json_replace() writes value to JSON as a character string.
m_db=# SELECT json_replace('{"x": 1}', '$.x', 'true');
 json_replace
----------------------------------------------------------------
 {"x": "true"}
(1 row)

-- You can modify the same path for multiple times. Perform the modification from left to right. The final result is subject to the last modification.
m_db=# SELECT json_replace('{"x": 1}', '$.x', true, '$.x', 123, '$.x', 'asd', '$.x', null);
 json_replace
----------------------------------------------------------------
 {"x": null}
(1 row)

-- If the specified path does not exist in json_doc, the JSON is returned without modification.
m_db=# SELECT json_replace('[1]','$.a',2);
 json_replace 
--------------
 [1]
(1 row)

JSON_SEARCH

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path]...])

Description: Returns the position where the specified string search_str appears in json_doc.

• If one_or_all is set to one, only the position where search_str appears for the first time is returned.
• If one_or_all is set to all, all positions of search_str are returned.
• escape_char is a single character used to escape the wildcard in search_str. If the value is null, the escape character is '\' by default.
• path indicates the position of search_str in the path to be returned. There can be multiple paths.
• Check escape_char. If the escape_char format is incorrect, an error is reported. Then check json_doc. If the json_doc format is incorrect, an error is reported. If the value of json_doc is null, null is returned. If the one_or_all format is incorrect, an error is reported. If the value of one_or_all is null, null is returned. If the path format is incorrect, an error is reported. If the value of path is null, null is returned. If a null value and a format error scenario are both in the variable parameter list, the exception is processed based on the exception sequence. If the null value is in the front of the variable parameter list, null is returned. Otherwise, an error is reported.
• If the depth of json_doc exceeds 100, the function will report an error.

Return type: JSON.

Example:

m_db=# SELECT json_search('{"a": "444%", "d": [[[44, "444%", "4444"]]], "h": {"hh": {"hhh": "4444%"}}}', 'all', '44%4\%', null, '$.a', '$.h');
      json_search      
-----------------------
 ["$.a", "$.h.hh.hhh"]
(1 row)

m_db=# SELECT json_search('{"a": "444%", "d": [[[44, "444%", "4444"]]], "h": {"hh": {"hhh": "4444%"}}}', 'one', '44%4\%', null, '$.a', '$.h');
 json_search 
-------------
 "$.a"
(1 row)

JSON_SET

JSON_SET(json_doc, path, val[, path, val] ...)

Description: Inserts a value into path in json_doc, or replaces the value in the specified path with a new value, and returns the modified JSON. If a value exists in the specified path, the corresponding value is replaced with a new value. If the specified path does not exist, data is inserted into the corresponding path.

• If json_doc or path is null, the function will return null.
• If json_doc is in an incorrect format, path is an invalid path expression, or path includes * or **, the function will report an error.
• If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is detected first, the function will return null. Otherwise, the function will report an error.
• If the depth of json_doc exceeds 100, the function will report an error.

Parameters: See Table 1.

Return type: JSON.

Example:

m_db=# SELECT json_set('{"s":3}','$.s','d');
 json_set
----------------------------------------------------------------
 {"s": "d"}
(1 row)

-- When multiple path-value pairs are entered, json_set() performs the set operation in sequence. For example, if the set operation is performed twice on the same path that does not exist, the value is added when the set operation is performed for the first time and is changed when the set operation is performed for the second time.
m_db=# SELECT json_set('{"s":3}','$.a','d','$.a','1');
 json_set
----------------------------------------------------------------
 {"a": "1", "s": 3}
(1 row)

JSON_TYPE

JSON_TYPE(json_doc)

Description: Returns the type of a given JSON value. The input parameter must be enclosed in single quotation marks (''). Input parameters of the numeric type are not accepted. If the input parameters are of the character type, enclose them in double quotation marks (""). If the depth of json_doc exceeds 100, the function will report an error.

Return type: TEXT.

Example:

-- JSON object type
m_db=# SELECT json_type('{"name":"tom"}');
 json_type
------------
 OBJECT
(1 row)

-- Numeric type
m_db=# SELECT json_type('123');
 json_type
------------
 INTEGER
(1 row)

-- String type
m_db=# SELECT json_type('"123"');
 json_type
------------
 STRING
(1 row)

JSON_UNQUOTE

JSON_UNQUOTE(json_val)

Description: Unquotes a JSON value with double quotation marks and returns the result as a character string.

Return type: TEXT.

Example:

m_db=# SELECT json_unquote('"NULL"');
 json_unquote 
--------------
 NULL
(1 row)

m_db=# SELECT json_unquote('"abc"');
 json_unquote 
--------------
 abc
(1 row)

m_db=# SELECT json_unquote('"');
 json_unquote 
--------------
 "
(1 row)

JSON_VALID

JSON_VALID(str)

Description: Checks whether a specified input parameter is a valid JSON. If the specified parameter is a valid JSON, 1 is returned. If the specified parameter is not a valid JSON, 0 is returned. If the input parameter is null, null is returned.

Return type: BIGINT.

Example:

m_db=# SELECT json_valid('{"name":"tom"}');
 json_valid
------------
          1
(1 row)

m_db=# SELECT json_valid('[}');
 json_valid 
------------
          0
(1 row)

m_db=# SELECT json_valid(1);
 json_valid 
------------
          0
(1 row)