1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
/* syntax version 1 */
/* postgres can not */
$json = CAST(@@{
"key": 123
}@@ as Json);
-- Check all supported types for variables
SELECT
-- Numeric types
JSON_QUERY(
$json,
"strict $var1 + $var2 + $var3 + $var4 + $var5 + $var6 + $var7 + $var8 + $var9 + $var10"
PASSING
CAST(1 as Int8) as var1,
CAST(2 as Uint8) as var2,
CAST(3 as Int16) as var3,
CAST(4 as Uint16) as var4,
CAST(5 as Int32) as var5,
CAST(6 as Uint32) as var6,
CAST(7 as Int64) as var7,
CAST(8 as Uint64) as var8,
CAST(9 as Double) as var9,
CAST(10 as Float) as var10
WITH UNCONDITIONAL ARRAY WRAPPER
),
-- Time types
JSON_QUERY(
$json,
"strict $var"
PASSING
CAST(1582044622 as Datetime) as var
WITH UNCONDITIONAL ARRAY WRAPPER
),
JSON_QUERY(
$json,
"strict $var"
PASSING
CAST(1582044622 as Timestamp) as var
WITH UNCONDITIONAL ARRAY WRAPPER
),
JSON_QUERY(
$json,
"strict $var"
PASSING
CAST("2020-02-18" as Date) as var
WITH UNCONDITIONAL ARRAY WRAPPER
),
-- Utf8
JSON_QUERY(
$json,
"strict $var"
PASSING
CAST("привет" as Utf8) as var
WITH UNCONDITIONAL ARRAY WRAPPER
),
-- Bool
JSON_QUERY(
$json,
"strict $var"
PASSING
true as var
WITH UNCONDITIONAL ARRAY WRAPPER
),
-- Json
JSON_QUERY(
$json,
"strict $var"
PASSING
$json as var
),
-- Nulls
JSON_QUERY(
$json,
"strict $var"
PASSING
Nothing(Int64?) as var
WITH UNCONDITIONAL ARRAY WRAPPER
),
JSON_QUERY(
$json,
"strict $var"
PASSING
NULL as var
WITH UNCONDITIONAL ARRAY WRAPPER
);
-- Check various ways to pass variable name
SELECT
JSON_QUERY(
$json, "strict $var1"
PASSING
123 as var1
WITH UNCONDITIONAL ARRAY WRAPPER
),
-- NOTE: VaR1 is not casted to upper-case VAR1 as standard expects
JSON_QUERY(
$json, "strict $VaR1"
PASSING
123 as VaR1
WITH UNCONDITIONAL ARRAY WRAPPER
),
JSON_QUERY(
$json, "strict $var1"
PASSING
123 as "var1"
WITH UNCONDITIONAL ARRAY WRAPPER
),
JSON_QUERY(
$json, "strict $VaR1"
PASSING
123 as "VaR1"
WITH UNCONDITIONAL ARRAY WRAPPER
);
|
