Handle JSON_MODIFY function and int inputs properly

[Jakeowen1]

Description

1. Currently Babelfish does not properly handle the JSON_MODIFY function when the new value parameter is JSON_QUERY, SELECT FOR JSON, or JSON MODIFY. This change adds support to correctly handle these cases
2. This change was made to expand the implementation of the JSON_MODIFY function
3. In TSQL, JSON_MODIFY() is explicitly stated to only accept text or nvarchar inputs but in practice it allows certain inputs to be interpreted as non text types. This pull request adds support to properly handle INT values.

Issues Resolved

BABEL-3696 and BABEL-3697

Test Scenarios Covered

• Use case based -

1> DECLARE @stats NVARCHAR(100)='{"click_count": 173}'
2> SELECT JSON_MODIFY(@stats, '$.click_count', CAST(JSON_VALUE(@stats, '$.click_count') AS INT)+1)
3> go
json_modify                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"click_count": 174} 

1> SELECT json_modify('{"Brand":"HP","Product":"Laptop"}', '$.Accessories', JSON_Query('["Keyboard","Mouse","Monitor"]'))
2> GO
json_modify_no_escape                                                                                                                                                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"Brand": "HP", "Product": "Laptop", "Accessories": ["Keyboard", "Mouse", "Monitor"]} 

1> select json_modify('{"a":"b"}', '$.a', json_modify('{"a":"b"}', '$.a', 'c'))
2> go
json_modify_no_escape                                                                                                                                                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"a": {"a": "c"}} 

1> create table t1 (x nvarchar(20))
2> insert into t1 values ('some string')
3> go

(1 rows affected)
1> select json_modify('{"a":"b"}', '$.a', x) from (select * from t1 for json path) a ([x])
2> go
json_modify_no_escape                                                                                                                                                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"a": [{"x": "some string"}]} 

• Boundary conditions -

1> DECLARE @data NVARCHAR(100)='{"click_count": 12345}'
2> SELECT JSON_MODIFY(@data, '$.click_count', 6 + 235)
3> go
json_modify                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"click_count": 241}

1> create table t1 (x nvarchar(20))
2> insert into t1 values ('some string')
3> go

(1 rows affected)

1> select json_modify('{"a":"b"}', '$.a', x) from (select * from t1 for json path, without_array_wrapper) a ([x])
2> go
json_modify                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"a": "{\"x\":\"some string\"}"}  

• Arbitrary inputs -

1> DECLARE @data NVARCHAR(100)='{"click_count": 12345}'
2> SELECT JSON_MODIFY(@data, '$.click_count', CAST(JSON_VALUE(@data,'$.click_count') AS CHAR(2)))
3> go
json_modify                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"click_count": "12"} 

1> select JSON_MODIFY(JSON_MODIFY('{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}', '$.Accessories', JSON_QUERY('["HDMI","USB"]')), '$.Brand', 'Lenovo')
2> go
json_modify                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"Brand": "Lenovo", "Product": "Laptop", "Accessories": ["HDMI", "USB"]}                                                                                                                                                                                        

(1 rows affected)

• Negative test cases -

1> select json_modify('{"a":"b"}', '$.a', json_modify('{"a":"b"}', 'strict $.c', 'c'))
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
property cannot be found on the specified JSON path

1> SELECT JSON_MODIFY('{"click_count": 1900-01-01}', '$.click_count', CAST('1900-02-02' as DATE))
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
invalid input syntax for type json

• Major version upgrade tests - Babel-937 tests the original implementation of json modify in 14_6, 14_7, 14_8, 14_9, 14_10 upgrade tests. Babel-3696 tests the new additions to this function in 15_3 and beyond

• Performance tests - Performance tests were ran by comparing the times of various queries before and after the change. We did not notice a significant difference between the time spent on queries before/after the changes

Check List

• [ ] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 and PostgreSQL licenses, and grant any person obtaining a copy of the contribution permission to relicense all or a portion of my contribution to the PostgreSQL License solely to contribute all or a portion of my contribution to the PostgreSQL open source project.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.