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
|
/* syntax version 1 */
/* postgres can not */
$json = CAST(@@{
"key": 123
}@@ as Json);
-- Check all supported types for variables
SELECT
-- Numeric types
JSON_EXISTS(
$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
),
-- Time types
JSON_EXISTS(
$json,
"strict $var"
PASSING
CAST(1582044622 as Datetime) as var
),
JSON_EXISTS(
$json,
"strict $var"
PASSING
CAST(1582044622 as Timestamp) as var
),
JSON_EXISTS(
$json,
"strict $var"
PASSING
CAST("2020-02-18" as Date) as var
),
-- Utf8
JSON_EXISTS(
$json,
"strict $var"
PASSING
CAST("привет" as Utf8) as var
),
-- Bool
JSON_EXISTS(
$json,
"strict $var"
PASSING
true as var
),
-- Json
JSON_EXISTS(
$json,
"strict $var"
PASSING
$json as var
),
-- Nulls
JSON_EXISTS(
$json,
"strict $var"
PASSING
Nothing(Int64?) as var
),
JSON_EXISTS(
$json,
"strict $var"
PASSING
NULL as var
);
-- Check various ways to pass variable name
SELECT
JSON_EXISTS(
$json, "strict $var1"
PASSING
123 as var1
),
-- NOTE: VaR1 is not casted to upper-case VAR1 as standard expects
JSON_EXISTS(
$json, "strict $VaR1"
PASSING
123 as VaR1
),
JSON_EXISTS(
$json, "strict $var1"
PASSING
123 as "var1"
),
JSON_EXISTS(
$json, "strict $VaR1"
PASSING
123 as "VaR1"
);
|
