Here’s the 2nd post for SQL Server 2022 – T-SQL Enhancements. This post will cover new functions added for SQL Server 2022 for JSON and Bit Manipulation.
JSON:
Microsoft – JSON Functions
There are several new functions for working with JSON.
ISJSON = Returns 1 if the supplied string is valid JSON.
TRUE:
SELECT ISJSON('{ "version": 2022}');
The following returns false, since the attribute name isn’t quote delimited.
SELECT ISJSON('{ version: 2022}');
JSON_OBJECT – Takes name/value pairs and returns them as JSON.
SELECT TOP 3 JSON_OBJECT('name': name, 'type': type_desc)
FROM sys.objects;
Result:
{"name":"sysrscols","type":"SYSTEM_TABLE"}
{"name":"sysrowsets","type":"SYSTEM_TABLE"}
{"name":"sysclones","type":"SYSTEM_TABLE"}
JSON_ARRAY – Returns a string of an array of the specified items. I assume you would use this along with JSON_OBJECT if you were trying to build a JSON document with arrays.
SELECT TOP 3 JSON_ARRAY('name', name, 'type', type_desc)
FROM sys.objects;
Result:
["name","sysrscols","type","SYSTEM_TABLE"] ["name","sysrowsets","type","SYSTEM_TABLE"] ["name","sysclones","type","SYSTEM_TABLE"]
JSON_PATH_EXISTS – Return 1 if the specified attribute is in the JSON document.
TRUE:
SELECT JSON_PATH_EXISTS('{"db_engine":"SQL Server",
"version":2022}', '$.version');
FALSE:
SELECT JSON_PATH_EXISTS('{"db_engine":"SQL Server",
"version":2022}', '$.version.year');
Bit Manipulation:
Microsoft – Bit Manipulation
Hacker Earth – Basics of Bit Manipulation
I’ve never run into a instance where I needed to use bit manipulation, but there’s a new series of functions in this area.
These functions will take either numeric or binary data type values.
For my examples, I’ll use the integer 1001, which is 1111101001 in binary.
In this example, LEFT_SHIFT will shift 1001 2 places to the left, and replace those spaces with 0, so we end up with 111110100100, which is 4004 as an integer. So we’re multiplying our value by 2^2(4).
SELECT LEFT_SHIFT(1001, 2);
RIGHT_SHIFT will drop the last two values, so in this case we end up with 250(11111010). So dividing by 2^2(4) and dropping the remainder.
SELECT RIGHT_SHIFT(1001, 2);
BIT_COUNT – counts the number of 1s in your binary value, so for 1001 we would get 7.
SELECT BIT_COUNT(1001);
GET_BIT – Get the value at the designated position in the binary value. The rightmost value is 0, and counts up moving to the left. For 1001, the third value from the right is 0.
SELECT GET_BIT(1001, 2);
SET_BIT – In this example, set the bit at position 3 to a value of 0.
SELECT SET_BIT(1001, 3, 0);
SQL Rob 