JSON_PATH_EXISTS gives you more power over JSON documents in SQL Server

     Schemas can easily change if you save your data in JSON format. It is very easy to add or remove properties from JSON documents. When the data model changes quickly, you might need to worry about if the property you are looking for exists in the documents. If the path you are looking for does not exist in some documents, you need to handle the exception in some way. JSON_PATH_EXISTS comes to your help in situations like that. It tests whether a specified path exists in the input JSON.

JSON_PATH_EXISTS (value_expression, sql_json_path)

value_expression

This is the source to test. It can be hard-coded JSON or value coming from a table or file.

sql_json_path

This is the path you want to test if it exists in the source.

     If the path exists in the source, the function returns 1. If the path does not exist, it returns 0. If the path or source is null, the function returns NULL. The function does not return any errors. You will get 0, 1, or NULL as output.

I will use the following JSON document as a source for the following examples. This is a JSON document from the public WideWorldImporters database.

     As you can see in this document, the Event schema is different for each event. You may want to display the current event's Status on your page. It looks like you need to hard code events which are Ready for Collection because there is no Status property. We can use JSON_PATH_EXISTS in this case and control the output.

The following example returns Delivered since Events[1] has the Status property.

SELECT CASE 
WHEN JSON_PATH_EXISTS(ReturnedDeliveryData, '$.Events[1].Status') = 0 
THEN 'Ready'
WHEN JSON_PATH_EXISTS(ReturnedDeliveryData, '$.Events[1].Status') = 1 
THEN JSON_VALUE(ReturnedDeliveryData,'$.Events[1].Status')
END as invoicestatus
FROM Sales.Invoices
WHERE InvoiceId  = 1

The following example returns Ready since Events[0] does not have a Status property.

SELECT CASE 
WHEN JSON_PATH_EXISTS(ReturnedDeliveryData, '$.Events[0].Status') = 0 
THEN 'Ready'
WHEN JSON_PATH_EXISTS(ReturnedDeliveryData, '$.Events[0].Status') = 1 
THEN JSON_VALUE(ReturnedDeliveryData,'$.Events[0].Status')
END as invoicestatus
FROM Sales.Invoices
WHERE InvoiceId  = 1

     Thanks to the JSON_PATH_EXISTS function, you will have more control over the JSON data. You can make smart choices at the database level.