risingwave_expr_impl/scalar/
jsonb_contains.rs

1// Copyright 2025 RisingWave Labs
2//
3// Licensed under the Apache License, Version 2.0 (the "License");
4// you may not use this file except in compliance with the License.
5// You may obtain a copy of the License at
6//
7//     http://www.apache.org/licenses/LICENSE-2.0
8//
9// Unless required by applicable law or agreed to in writing, software
10// distributed under the License is distributed on an "AS IS" BASIS,
11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12// See the License for the specific language governing permissions and
13// limitations under the License.
14
15use jsonbb::ValueRef;
16use risingwave_common::types::{JsonbRef, ListRef};
17use risingwave_expr::function;
18
19/// Does the first JSON value contain the second?
20///
21/// Examples:
22///
23/// ```slt
24/// # Simple scalar/primitive values contain only the identical value:
25/// query B
26/// SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
27/// ----
28/// t
29///
30/// # The array on the right side is contained within the one on the left:
31/// query B
32/// SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
33/// ----
34/// t
35///
36/// # Order of array elements is not significant, so this is also true:
37/// query B
38/// SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
39/// ----
40/// t
41///
42/// # Duplicate array elements don't matter either:
43/// query B
44/// SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
45/// ----
46/// t
47///
48/// # The object with a single pair on the right side is contained
49/// # within the object on the left side:
50/// query B
51/// SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;
52/// ----
53/// t
54///
55/// # The array on the right side is not considered contained within the
56/// # array on the left, even though a similar array is nested within it:
57/// query B
58/// SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
59/// ----
60/// f
61///
62/// # But with a layer of nesting, it is contained:
63/// query B
64/// SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
65/// ----
66/// t
67///
68/// # Similarly, containment is not reported here:
69/// query B
70/// SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;
71/// ----
72/// f
73///
74/// # A top-level key and an empty object is contained:
75/// query B
76/// SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
77/// ----
78/// t
79///
80/// # This array contains the primitive string value:
81/// query B
82/// SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
83/// ----
84/// t
85///
86/// # This exception is not reciprocal -- non-containment is reported here:
87/// query B
88/// SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;
89/// ----
90/// f
91///
92/// # Object is not primitive:
93/// query B
94/// SELECT '[1, {"a":2}]'::jsonb @> '{"a":2}';
95/// ----
96/// f
97///
98/// # Array can be nested:
99/// query B
100/// SELECT '[1, [3, 4]]'::jsonb @> '[[3]]';
101/// ----
102/// t
103///
104/// # Recursion shall not include the special rule of array containing primitive:
105/// query B
106/// SELECT '{"a": [3, 4]}'::jsonb @> '{"a": 3}';
107/// ----
108/// f
109/// ```
110#[function("jsonb_contains(jsonb, jsonb) -> boolean")]
111fn jsonb_contains(left: JsonbRef<'_>, right: JsonbRef<'_>) -> bool {
112    jsonbb_contains(left.into(), right.into(), true)
113}
114
115/// Performs `jsonb_contains` on `jsonbb::ValueRef`.
116/// `root` indicates whether the current recursion is at the root level.
117fn jsonbb_contains(left: ValueRef<'_>, right: ValueRef<'_>, root: bool) -> bool {
118    match (left, right) {
119        // Both left and right are objects.
120        (ValueRef::Object(left_obj), ValueRef::Object(right_obj)) => {
121            // Every key-value pair in right should be present in left.
122            right_obj.iter().all(|(key, value)| {
123                left_obj
124                    .get(key)
125                    .is_some_and(|left_val| jsonbb_contains(left_val, value, false))
126            })
127        }
128
129        // Both left and right are arrays.
130        (ValueRef::Array(left_arr), ValueRef::Array(right_arr)) => {
131            // For every value in right, there should be an equivalent in left.
132            right_arr.iter().all(|right_val| {
133                left_arr
134                    .iter()
135                    .any(|left_val| jsonbb_contains(left_val, right_val, false))
136            })
137        }
138
139        // Left is an array and right is an object.
140        (ValueRef::Array(_), ValueRef::Object(_)) => false,
141
142        // Left is an array and right is a primitive value. only at root level.
143        (ValueRef::Array(left_arr), right_val) if root => {
144            // The right should be present in left.
145            left_arr.iter().any(|left_val| left_val == right_val)
146        }
147
148        // Both left and right are primitive values.
149        (left_val, right_val) => left_val == right_val,
150    }
151}
152
153/// Is the first JSON value contained in the second?
154///
155/// Examples:
156///
157/// ```slt
158/// query B
159/// select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;
160/// ----
161/// t
162/// ```
163#[function("jsonb_contained(jsonb, jsonb) -> boolean")]
164fn jsonb_contained(left: JsonbRef<'_>, right: JsonbRef<'_>) -> bool {
165    jsonb_contains(right, left)
166}
167
168/// Does the text string exist as a top-level key or array element within the JSON value?
169///
170/// Examples:
171///
172/// ```slt
173/// # String exists as array element:
174/// query B
175/// SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
176/// ----
177/// t
178///
179/// # String exists as object key:
180/// query B
181/// SELECT '{"foo": "bar"}'::jsonb ? 'foo';
182/// ----
183/// t
184///
185/// # Object values are not considered:
186/// query B
187/// SELECT '{"foo": "bar"}'::jsonb ? 'bar';
188/// ----
189/// f
190///
191/// # As with containment, existence must match at the top level:
192/// query B
193/// SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar';
194/// ----
195/// f
196///
197/// # A string is considered to exist if it matches a primitive JSON string:
198/// query B
199/// SELECT '"foo"'::jsonb ? 'foo';
200/// ----
201/// t
202/// ```
203#[function("jsonb_exists(jsonb, varchar) -> boolean")]
204fn jsonb_exists(left: JsonbRef<'_>, key: &str) -> bool {
205    match left.into() {
206        ValueRef::Object(object) => object.get(key).is_some(),
207        ValueRef::Array(array) => array.iter().any(|val| val.as_str() == Some(key)),
208        ValueRef::String(str) => str == key,
209        _ => false,
210    }
211}
212
213/// Do any of the strings in the text array exist as top-level keys or array elements?
214///
215/// Examples:
216///
217/// ```slt
218/// query B
219/// select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'];
220/// ----
221/// t
222///
223/// query B
224/// select '["a", "b", "c"]'::jsonb ?| array['b', 'd'];
225/// ----
226/// t
227///
228/// query B
229/// select '"b"'::jsonb ?| array['b', 'd'];
230/// ----
231/// t
232/// ```
233#[function("jsonb_exists_any(jsonb, varchar[]) -> boolean")]
234fn jsonb_exists_any(left: JsonbRef<'_>, keys: ListRef<'_>) -> bool {
235    let mut keys = keys.iter().flatten().map(|val| val.into_utf8());
236    match left.into() {
237        ValueRef::Object(object) => keys.any(|key| object.get(key).is_some()),
238        ValueRef::Array(array) => keys.any(|key| array.iter().any(|val| val.as_str() == Some(key))),
239        ValueRef::String(str) => keys.any(|key| str == key),
240        _ => false,
241    }
242}
243
244/// Do all of the strings in the text array exist as top-level keys or array elements?
245///
246/// Examples:
247///
248/// ```slt
249/// query B
250/// select '{"a":1, "b":2, "c":3}'::jsonb ?& array['a', 'b'];
251/// ----
252/// t
253///
254/// query B
255/// select '["a", "b", "c"]'::jsonb ?& array['a', 'b'];
256/// ----
257/// t
258///
259/// query B
260/// select '"b"'::jsonb ?& array['b'];
261/// ----
262/// t
263/// ```
264#[function("jsonb_exists_all(jsonb, varchar[]) -> boolean")]
265fn jsonb_exists_all(left: JsonbRef<'_>, keys: ListRef<'_>) -> bool {
266    let mut keys = keys.iter().flatten().map(|val| val.into_utf8());
267    match left.into() {
268        ValueRef::Object(object) => keys.all(|key| object.get(key).is_some()),
269        ValueRef::Array(array) => keys.all(|key| array.iter().any(|val| val.as_str() == Some(key))),
270        ValueRef::String(str) => keys.all(|key| str == key),
271        _ => false,
272    }
273}
risingwave_expr_impl/scalar/jsonb_contains.rs

risingwave_expr_impl/scalar/
jsonb_contains.rs
