SQL Reference - JSON

JSON_EXISTS(json_document, json_path, json_exists_error_handling)

json_document: This parameter is required. It is the JSON document in which the search will be processed. It should be in either a VARCHAR2, BLOB, or CLOB data type. In the case of a NULL value, the function returns FALSE.
json_path: This parameter is required. It is the JSON path expression which is used to search within the json_document. It should contain one or more JSON keys, through which it is possible to traverse the JSON document.
json_exists_error_handling: This parameter is optional. It specifies how the function behaves when an error occurs. Its possible values are ‘strict’ or ‘lax’. If it is set to ‘strict’, the function will raise an error when an invalid JSON document or JSON path is used. If it is set to ‘lax’, the function will return a FALSE when it encounters an erroneous section within the JSON document or JSON path. If this parameter is not specified, it defaults to ‘strict’.

Example

SELECT JSON_EXISTS(
  '{"employees":["John", "Anna", "Peter"]}' FORMAT JSON,
  '$.employees[0]'
) AS "JSON_EXISTS_Test"
FROM dual;

Output

JSON_EXISTS_Test
----------------
1

Explanation

In the provided example, JSON_EXISTS function is being used to check if the JSON object contains “John” at first position in the “employees” array. As “John” is present at the first position in the “employees” array, the function returns 1, indicating true.

JSON_EXISTS( expression , path )

expression: This parameter accepts an expression which is analyzed, and it must be of a JSON type. Essentially, it signifies the JSON data on which the function operates. The data can be a column, a scalar variable holding JSON, or a direct string of JSON data.
path: This is a quoted-string that specifies the path of the property to search for in the JSON input. It must be written in the standard SQL/JSON path language format. The path expression must be appropriate for the expression on which JSON_EXISTS is operating; otherwise, a format error is raised.

Example

DECLARE @json NVARCHAR(4000)
SET @json = N'{"name":"John","skills":["SQL","RUBY","PYTHON"]}'
SELECT JSON_EXISTS(@json,'$.skills')

Output

Explanation

The JSON_EXISTS function in SQL Server checks if a specified path is present in the provided JSON object. In the given example, it checks for the existence of the “skills” key in JSON object. It returns 1 if the path exists, otherwise 0.