SQL JSON - How to modify boolean value present in the Json Data

Question

How can I modify the existing boolean value of a json data in SQL Server 2016 - Json?

Below is the script for inserting json Data.

DECLARE @a nvarchar(max) = N'{"employeeName":"Test","isActive":true}';

insert into TestTable(testId,name,jsonData) Values('1','Test',@a) 

I tried to update using below query, but it returns Invalid column name 'false'.

update TestTable
set jsonData = JSON_MODIFY(jsonData,'$.isActive',false)

Answer 1

You can accomplish this using CAST(0 as BIT)

update TestTable
set jsonData = JSON_MODIFY(jsonData, '$.isActive', CAST(0 as BIT))

If you want to set it to true, it is simply CAST(1 as BIT) instead.

This works because in SQL, a boolean is represented as a BIT, which can only be 0 or 1. In its translation to JSON, it converts a bit of 0 to false, and a bit of 1 to true.