Skip to content

Commit d4b4aff

Browse files
shivsoodllali
shivsood
authored and
llali
committed
Merged PR 1724944: [MSJSON] added support for json_objectagg and Returning JSON option for json_object, json_array
# Pull Request Template for ScriptDom ## Description adds support to scriptdom for new functions and options in SQLServer. Specifically support for - Json_objectagg with options NULL ON NULL/ABSENT ON NULL and RETURNING JSON. - Support for RETURNING JSON option for json_object, json_array. Added relevant test for all options. ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature Added Test.
1 parent 9a11bdf commit d4b4aff

8 files changed

Lines changed: 344 additions & 2 deletions

File tree

‎SqlScriptDom/Parser/TSql/Ast.xml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -642,6 +642,7 @@
642642
<Member Name="TrimOptions" Type="Identifier" Summary="TRIM intrinsic can take optional arguments like 'Leading', 'Trailing' or 'Both'."/>
643643
<Member Name="JsonParameters" Type="JsonKeyValue" Collection="true" Summary="The Json parameters to the function."/>
644644
<Member Name="AbsentOrNullOnNull" Type="Identifier" Collection="true" Summary="The Absent or Null on Null will convert or remove sql null to json null"/>
645+
<Member Name="ReturnType" Type="Identifier" Collection="true" Summary="Return type of function. Used by json_arrayagg, json_objectagg, json_array, json_object and json_value"/>
645646
</Class>
646647
<Class Name="CallTarget" Abstract="true" Summary="Represents a target of a function call.">
647648
</Class>

‎SqlScriptDom/Parser/TSql/CodeGenerationSupporter.cs‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -519,6 +519,7 @@ internal static class CodeGenerationSupporter
519519
internal const string Json = "JSON";
520520
internal const string JsonArray = "JSON_ARRAY";
521521
internal const string JsonObject = "JSON_OBJECT";
522+
internal const string JsonObjectAgg = "JSON_OBJECTAGG";
522523
internal const string Keep = "KEEP";
523524
internal const string KeepDefaults = "KEEPDEFAULTS";
524525
internal const string KeepFixed = "KEEPFIXED";
@@ -848,6 +849,7 @@ internal static class CodeGenerationSupporter
848849
internal const string RetentionDays = "RETENTION_DAYS";
849850
internal const string RetentionPeriod = "RETENTION_PERIOD";
850851
internal const string Returns = "RETURNS";
852+
internal const string Returning = "RETURNING";
851853
internal const string RequestMaxCpuTimeSec = "REQUEST_MAX_CPU_TIME_SEC";
852854
internal const string RequestMaxMemoryGrantPercent = "REQUEST_MAX_MEMORY_GRANT_PERCENT";
853855
internal const string RequestMemoryGrantTimeoutSec = "REQUEST_MEMORY_GRANT_TIMEOUT_SEC";

‎SqlScriptDom/Parser/TSql/TSql170.g‎

Lines changed: 69 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32066,6 +32066,36 @@ expressionList [TSqlFragment vParent, IList<ScalarExpression> expressions]
3206632066
|
3206732067
/* empty */
3206832068
)
32069+
(
32070+
jsonReturningClause[vParent]
32071+
|
32072+
/* empty */
32073+
)
32074+
;
32075+
32076+
jsonObjectAggExpressionList [FunctionCall vParent]
32077+
{
32078+
JsonKeyValue vExpression;
32079+
}
32080+
:
32081+
(
32082+
vExpression=jsonKeyValueExpression
32083+
{
32084+
AddAndUpdateTokenInfo(vParent, vParent.JsonParameters, vExpression);
32085+
}
32086+
|
32087+
/* empty */
32088+
)
32089+
(
32090+
jsonNullClauseFunction[vParent]
32091+
|
32092+
/* empty */
32093+
)
32094+
(
32095+
jsonReturningClause[vParent]
32096+
|
32097+
/* empty */
32098+
)
3206932099
;
3207032100

3207132101
jsonNullClauseFunction [FunctionCall vParent]
@@ -32092,7 +32122,23 @@ expressionList [TSqlFragment vParent, IList<ScalarExpression> expressions]
3209232122
}
3209332123
)
3209432124
;
32095-
32125+
32126+
jsonReturningClause [FunctionCall vParent]
32127+
{
32128+
Identifier vJson;
32129+
}
32130+
:
32131+
tReturning:Identifier tJson:Identifier
32132+
{
32133+
Match(tReturning, CodeGenerationSupporter.Returning);
32134+
Match(tJson, CodeGenerationSupporter.Json);
32135+
UpdateTokenInfo(vParent,tJson);
32136+
vJson = FragmentFactory.CreateFragment<Identifier>();
32137+
AddAndUpdateTokenInfo(vParent, vParent.ReturnType, vJson);
32138+
vJson.SetUnquotedIdentifier(tJson.getText());
32139+
}
32140+
;
32141+
3209632142
jsonKeyValueExpression returns [JsonKeyValue vResult = FragmentFactory.CreateFragment<JsonKeyValue>()]
3209732143
{
3209832144
ScalarExpression vKey;
@@ -32388,6 +32434,9 @@ builtInFunctionCall returns [FunctionCall vResult = FragmentFactory.CreateFragme
3238832434
|
3238932435
{(vResult.FunctionName != null && vResult.FunctionName.Value.ToUpper(CultureInfo.InvariantCulture) == CodeGenerationSupporter.JsonObject)}?
3239032436
jsonObjectBuiltInFunctionCall[vResult]
32437+
|
32438+
{(vResult.FunctionName != null && vResult.FunctionName.Value.ToUpper(CultureInfo.InvariantCulture) == CodeGenerationSupporter.JsonObjectAgg)}?
32439+
jsonObjectAggBuiltInFunctionCall[vResult]
3239132440
|
3239232441
{(vResult.FunctionName != null && vResult.FunctionName.Value.ToUpper(CultureInfo.InvariantCulture) == CodeGenerationSupporter.Trim) &&
3239332442
(NextTokenMatches(CodeGenerationSupporter.Leading) | NextTokenMatches(CodeGenerationSupporter.Trailing) | NextTokenMatches(CodeGenerationSupporter.Both))}?
@@ -32421,6 +32470,11 @@ jsonArrayBuiltInFunctionCall [FunctionCall vParent]
3242132470
|
3242232471
/* empty */
3242332472
)
32473+
(
32474+
jsonReturningClause[vParent]
32475+
|
32476+
/* empty */
32477+
)
3242432478
tRParen:RightParenthesis
3242532479
{
3242632480
UpdateTokenInfo(vParent, tRParen);
@@ -32443,6 +32497,20 @@ jsonObjectBuiltInFunctionCall [FunctionCall vParent]
3244332497
}
3244432498
;
3244532499

32500+
jsonObjectAggBuiltInFunctionCall [FunctionCall vParent]
32501+
{
32502+
}
32503+
: (
32504+
jsonObjectAggExpressionList[vParent]
32505+
|
32506+
/* empty */
32507+
)
32508+
tRParen:RightParenthesis
32509+
{
32510+
UpdateTokenInfo(vParent, tRParen);
32511+
}
32512+
;
32513+
3244632514
regularBuiltInFunctionCall [FunctionCall vParent]
3244732515
{
3244832516
ColumnReferenceExpression vColumn;

‎SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.FunctionCall.cs‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,20 @@ public override void ExplicitVisit(FunctionCall node)
5555
if (node.JsonParameters?.Count > 0 && node.AbsentOrNullOnNull?.Count > 0) //If there are values and null on null or absent on null present then generate space in between them
5656
GenerateSpace();
5757
GenerateNullOnNullOrAbsentOnNull(node?.AbsentOrNullOnNull);
58+
if (node.JsonParameters?.Count > 0 && node.ReturnType?.Count > 0) //If there are values and null on null or absent on null present then generate space in between them
59+
GenerateSpace();
60+
GenerateReturnType(node?.ReturnType);
61+
GenerateSymbol(TSqlTokenType.RightParenthesis);
62+
}
63+
else if (node.FunctionName.Value.ToUpper(CultureInfo.InvariantCulture) == CodeGenerationSupporter.JsonObjectAgg)
64+
{
65+
GenerateCommaSeparatedList(node.JsonParameters);
66+
if (node.JsonParameters?.Count > 0 && node.AbsentOrNullOnNull?.Count > 0) //If there are values and null on null or absent on null present then generate space in between them
67+
GenerateSpace();
68+
GenerateNullOnNullOrAbsentOnNull(node?.AbsentOrNullOnNull);
69+
if (node.JsonParameters?.Count > 0 && node.ReturnType?.Count > 0) //If there are values and null on null or absent on null present then generate space in between them
70+
GenerateSpace();
71+
GenerateReturnType(node?.ReturnType);
5872
GenerateSymbol(TSqlTokenType.RightParenthesis);
5973
}
6074
else if (node.FunctionName.Value.ToUpper(CultureInfo.InvariantCulture) == CodeGenerationSupporter.JsonArray)
@@ -63,6 +77,9 @@ public override void ExplicitVisit(FunctionCall node)
6377
if (node.Parameters?.Count > 0 && node?.AbsentOrNullOnNull?.Count > 0) //If there are values and null on null or absent on null present then generate space in between them
6478
GenerateSpace();
6579
GenerateNullOnNullOrAbsentOnNull(node?.AbsentOrNullOnNull);
80+
if (node.ReturnType?.Count > 0) //If there are values and null on null or absent on null present then generate space in between them
81+
GenerateSpace();
82+
GenerateReturnType(node?.ReturnType);
6683
GenerateSymbol(TSqlTokenType.RightParenthesis);
6784
}
6885
else
@@ -117,5 +134,14 @@ private void GenerateNullOnNullOrAbsentOnNull(IList<Identifier> list)
117134
GenerateKeyword(TSqlTokenType.Null);
118135
}
119136
}
137+
private void GenerateReturnType(IList<Identifier> list)
138+
{
139+
if (list?.Count > 0 && list[0].Value?.ToUpper(CultureInfo.InvariantCulture) == CodeGenerationSupporter.Json)
140+
{
141+
GenerateIdentifier("RETURNING");
142+
GenerateSpace();
143+
GenerateSpaceSeparatedList(list);
144+
}
145+
}
120146
}
121147
}

‎Test/SqlDom/Baselines170/JsonFunctionTests170.sql‎

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,101 @@
1+
SELECT id,
2+
json_col
3+
FROM tab1
4+
WHERE ISJSON(json_col) = 1;
5+
6+
SELECT id,
7+
json_col
8+
FROM tab1
9+
WHERE ISJSON(json_col, SCALAR) = 1;
10+
11+
SELECT ISJSON('true', VALUE);
12+
13+
DECLARE @jsonInfo AS NVARCHAR (MAX);
14+
15+
SET @jsonInfo = N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';
16+
17+
SELECT JSON_PATH_EXISTS(@jsonInfo, '$.info.address');
18+
19+
SELECT JSON_OBJECT('name':'value');
20+
21+
SELECT JSON_OBJECT('name':'value', 'type':1 NULL ON NULL);
22+
23+
SELECT JSON_OBJECT(NULL ON NULL);
24+
25+
SELECT JSON_OBJECT('name':'value', 'type':NULL ABSENT ON NULL);
26+
27+
SELECT JSON_OBJECT('name':'value', 'type':JSON_OBJECT('type_id':1, 'name':'a'));
28+
29+
SELECT JSON_OBJECT();
30+
31+
SELECT JSON_OBJECT('name':'value', 'type':1);
32+
33+
SELECT JSON_OBJECT('name':'value', 'type':JSON_ARRAY(1, 2));
34+
35+
DECLARE @id_key AS NVARCHAR (10) = N'id', @id_value AS NVARCHAR (64) = NEWID();
36+
37+
SELECT JSON_OBJECT('user_name':USER_NAME(), @id_key:@id_value, 'sid':(SELECT @@SPID));
38+
39+
SELECT s.session_id,
40+
JSON_OBJECT('security_id':s.security_id, 'login':s.login_name, 'status':s.status) AS info
41+
FROM sys.dm_exec_sessions AS s
42+
WHERE s.is_user_process = 1;
43+
44+
SELECT JSON_OBJECT('name':'b' RETURNING JSON);
45+
46+
SELECT JSON_OBJECT('name':'b' NULL ON NULL RETURNING JSON);
47+
48+
SELECT JSON_OBJECT('name':'b' ABSENT ON NULL RETURNING JSON);
49+
50+
SELECT JSON_ARRAY('a', JSON_OBJECT('name':'value', 'type':1 NULL ON NULL) NULL ON NULL);
51+
52+
SELECT JSON_ARRAY();
53+
54+
SELECT JSON_ARRAY('name');
55+
56+
SELECT JSON_ARRAY('a', 1, 'b', 2);
57+
58+
SELECT JSON_ARRAY('a', 1, NULL, 2 NULL ON NULL);
59+
60+
SELECT JSON_ARRAY('a', 1, NULL, 2 ABSENT ON NULL);
61+
62+
SELECT JSON_ARRAY(NULL ON NULL);
63+
64+
SELECT JSON_ARRAY(ABSENT ON NULL);
65+
66+
DECLARE @id_value AS NVARCHAR (64) = NEWID();
67+
68+
SELECT JSON_ARRAY(1, @id_value, (SELECT @@SPID));
69+
70+
SELECT s.session_id,
71+
JSON_ARRAY(s.host_name, s.program_name, s.client_interface_name)
72+
FROM sys.dm_exec_sessions AS s
73+
WHERE s.is_user_process = 1;
74+
75+
SELECT JSON_ARRAY('a', 1, NULL, 2 RETURNING JSON);
76+
77+
SELECT JSON_ARRAY('a', 1, NULL, 2 NULL ON NULL RETURNING JSON);
78+
79+
SELECT JSON_ARRAY('a', 1, NULL, 2 ABSENT ON NULL RETURNING JSON);
80+
81+
SELECT JSON_OBJECTAGG('name':'value');
82+
83+
SELECT JSON_OBJECTAGG('name':'value' NULL ON NULL);
84+
85+
SELECT JSON_OBJECTAGG(NULL ON NULL);
86+
87+
SELECT JSON_OBJECTAGG('name':NULL ABSENT ON NULL);
88+
89+
SELECT JSON_OBJECTAGG('name':JSON_OBJECT('type_id':1, 'name':'a'));
90+
91+
SELECT JSON_OBJECTAGG();
92+
93+
SELECT JSON_OBJECTAGG('name':1);
94+
95+
SELECT JSON_OBJECTAGG('name':JSON_ARRAY(1, 2));
96+
SELECT JSON_OBJECTAGG('name':'b' NULL ON NULL RETURNING JSON);
97+
98+
SELECT JSON_OBJECTAGG('name':'b' ABSENT ON NULL RETURNING JSON);
99+
100+
SELECT JSON_OBJECTAGG('name':'b' RETURNING JSON);
101+

‎Test/SqlDom/Only170SyntaxTests.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,8 @@ public partial class SqlDomTests
1515
new ParserTest170("AlterDatabaseManualCutoverTests170.sql", nErrors80: 4, nErrors90: 4, nErrors100: 4, nErrors110: 4, nErrors120: 4, nErrors130: 4, nErrors140: 4, nErrors150: 4, nErrors160: 4),
1616
new ParserTest170("CreateColumnStoreIndexTests170.sql", nErrors80: 3, nErrors90: 3, nErrors100: 3, nErrors110: 3, nErrors120: 3, nErrors130: 0, nErrors140: 0, nErrors150: 0, nErrors160: 0),
1717
new ParserTest170("RegexpTests170.sql", nErrors80: 0, nErrors90: 0, nErrors100: 0, nErrors110: 0, nErrors120: 0, nErrors130: 0, nErrors140: 0, nErrors150: 0, nErrors160: 0),
18-
new ParserTest170("AiGenerateChunksTests170.sql", nErrors80: 21, nErrors90: 18, nErrors100: 17, nErrors110: 17, nErrors120: 17, nErrors130: 17, nErrors140: 17, nErrors150: 17, nErrors160: 17)
18+
new ParserTest170("AiGenerateChunksTests170.sql", nErrors80: 21, nErrors90: 18, nErrors100: 17, nErrors110: 17, nErrors120: 17, nErrors130: 17, nErrors140: 17, nErrors150: 17, nErrors160: 17),
19+
new ParserTest170("JsonFunctionTests170.sql", nErrors80: 9, nErrors90: 8, nErrors100: 30, nErrors110: 30, nErrors120: 30, nErrors130: 30, nErrors140: 30, nErrors150: 30, nErrors160: 30),
1920
};
2021

2122
private static readonly ParserTest[] SqlAzure170_TestInfos =

‎Test/SqlDom/ParserErrorsTests.cs‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -503,6 +503,51 @@ public void JsonArraySyntaxNegativeTest()
503503
new ParserErrorInfo(48, "SQL46010", "ON"));
504504
}
505505

506+
/// <summary>
507+
/// Negative tests for JSON_OBJECTAGG syntax in functions
508+
/// </summary>
509+
[TestMethod]
510+
[Priority(0)]
511+
[SqlStudioTestCategory(Category.UnitTest)]
512+
public void JsonObjectAGGSyntaxNegativeTest()
513+
{
514+
// Incorrect key value parameter number
515+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name':'value', 'type':1)",
516+
new ParserErrorInfo(36, "SQL46010", ","));
517+
518+
// No closing quotation mark
519+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name':'value)",
520+
new ParserErrorInfo(29, "SQL46030", "'value)"));
521+
522+
// Incorrect placing of colon
523+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name':'value''type':1)",
524+
new ParserErrorInfo(42, "SQL46010", ":"));
525+
526+
// cannot use expression without colon
527+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name')",
528+
new ParserErrorInfo(22, "SQL46010", "'name'"));
529+
530+
// cannot use expression without colon
531+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name' ABSENT ON NULL)",
532+
new ParserErrorInfo(29, "SQL46010", "ABSENT"));
533+
534+
// Cannot use empty value
535+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name':)",
536+
new ParserErrorInfo(29, "SQL46010", ")"));
537+
538+
// Cannot use incomplete absent on null clause cases
539+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name':NULL ABSENT ON)",
540+
new ParserErrorInfo(43, "SQL46010", ")"));
541+
542+
// Cannot use Incomplete RETURNING clause
543+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name':NULL RETURNING ON)",
544+
new ParserErrorInfo(46, "SQL46010", ")"));
545+
546+
// Cannot use anything other than JSON in RETURNING clause
547+
ParserTestUtils.ErrorTest170("SELECT JSON_OBJECTAGG('name':NULL RETURNING INT)",
548+
new ParserErrorInfo(44, "SQL46005", "JSON", "INT"));
549+
}
550+
506551
/// <summary>
507552
/// Negative tests for Data Masking Alter Column syntax.
508553
/// </summary>

0 commit comments

Comments
 (0)