36

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)
Viresh Mathad
  • 576
  • 1
  • 5
  • 19

1 Answers1

76

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.

Zork
  • 969
  • 8
  • 8