risingwave_expr_impl/scalar/
jsonb_delete.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 std::collections::HashSet;
16
17use jsonbb::ValueRef;
18use risingwave_common::types::{JsonbRef, ListRef};
19use risingwave_expr::{ExprError, Result, function};
20
21/// Removes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.
22///
23/// Examples:
24///
25/// ```slt
26/// # remove key from object
27/// query T
28/// SELECT '{"a": "b", "c": "d"}'::jsonb - 'a';
29/// ----
30/// {"c": "d"}
31///
32/// # remove matching value from array
33/// query T
34/// SELECT '["a", "b", "c", "b"]'::jsonb - 'b';
35/// ----
36/// ["a", "c"]
37///
38/// query error cannot delete from scalar
39/// SELECT '1'::jsonb - 'b';
40/// ```
41#[function("subtract(jsonb, varchar) -> jsonb")]
42fn jsonb_remove(v: JsonbRef<'_>, key: &str, writer: &mut jsonbb::Builder) -> Result<()> {
43    match v.into() {
44        ValueRef::Object(obj) => {
45            writer.begin_object();
46            for (k, v) in obj.iter() {
47                if k != key {
48                    writer.add_string(k);
49                    writer.add_value(v);
50                }
51            }
52            writer.end_object();
53            Ok(())
54        }
55        ValueRef::Array(arr) => {
56            writer.begin_array();
57            for value in arr.iter() {
58                if value.as_str() != Some(key) {
59                    writer.add_value(value);
60                }
61            }
62            writer.end_array();
63            Ok(())
64        }
65        _ => Err(ExprError::InvalidParam {
66            name: "jsonb",
67            reason: "cannot delete from scalar".into(),
68        }),
69    }
70}
71
72/// Deletes all matching keys or array elements from the left operand.
73///
74/// Examples:
75///
76/// ```slt
77/// query T
78/// SELECT '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[];
79/// ----
80/// {}
81///
82/// query error cannot delete from scalar
83/// SELECT '1'::jsonb - '{a,c}'::text[];
84/// ```
85#[function("subtract(jsonb, varchar[]) -> jsonb")]
86fn jsonb_remove_keys(
87    v: JsonbRef<'_>,
88    keys: ListRef<'_>,
89    writer: &mut jsonbb::Builder,
90) -> Result<()> {
91    let keys_set: HashSet<&str> = keys.iter().flatten().map(|s| s.into_utf8()).collect();
92
93    match v.into() {
94        ValueRef::Object(obj) => {
95            writer.begin_object();
96            for (k, v) in obj.iter() {
97                if !keys_set.contains(k) {
98                    writer.add_string(k);
99                    writer.add_value(v);
100                }
101            }
102            writer.end_object();
103            Ok(())
104        }
105        ValueRef::Array(arr) => {
106            writer.begin_array();
107            for value in arr.iter() {
108                if let Some(s) = value.as_str()
109                    && keys_set.contains(s)
110                {
111                    continue;
112                }
113                writer.add_value(value);
114            }
115            writer.end_array();
116            Ok(())
117        }
118        _ => Err(ExprError::InvalidParam {
119            name: "jsonb",
120            reason: "cannot delete from scalar".into(),
121        }),
122    }
123}
124
125/// Deletes the array element with the specified index (negative integers count from the end).
126/// Throws an error if JSON value is not an array.
127///
128/// Examples:
129///
130/// ```slt
131/// query T
132/// SELECT '["a", "b"]'::jsonb - 1;
133/// ----
134/// ["a"]
135///
136/// query T
137/// SELECT '["a", "b"]'::jsonb - -1;
138/// ----
139/// ["a"]
140///
141/// query T
142/// SELECT '["a", "b"]'::jsonb - 2;
143/// ----
144/// ["a", "b"]
145///
146/// query T
147/// SELECT '["a", "b"]'::jsonb - -3;
148/// ----
149/// ["a", "b"]
150///
151/// query error cannot delete from scalar
152/// SELECT '1'::jsonb - 1;
153///
154/// query error cannot delete from object using integer index
155/// SELECT '{"a": 1}'::jsonb - 1;
156/// ```
157#[function("subtract(jsonb, int4) -> jsonb")]
158fn jsonb_remove_index(v: JsonbRef<'_>, index: i32, writer: &mut jsonbb::Builder) -> Result<()> {
159    let array = match v.into() {
160        ValueRef::Array(array) => array,
161        ValueRef::Object(_) => {
162            return Err(ExprError::InvalidParam {
163                name: "jsonb",
164                reason: "cannot delete from object using integer index".into(),
165            });
166        }
167        _ => {
168            return Err(ExprError::InvalidParam {
169                name: "jsonb",
170                reason: "cannot delete from scalar".into(),
171            });
172        }
173    };
174    let Some(idx) = normalize_array_index(array.len(), index) else {
175        // out of bounds index returns original value
176        writer.add_value(v.into());
177        return Ok(());
178    };
179
180    writer.begin_array();
181    for (i, value) in array.iter().enumerate() {
182        if i != idx {
183            writer.add_value(value);
184        }
185    }
186    writer.end_array();
187    Ok(())
188}
189
190/// Deletes the field or array element at the specified path, where path elements can be
191/// either field keys or array indexes.
192///
193/// Examples:
194///
195/// ```slt
196/// # Basic test case
197/// query T
198/// SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';
199/// ----
200/// ["a", {}]
201///
202/// # Invalid path
203/// query error path element at position 1 is null
204/// SELECT '["a", {"b":1}]'::jsonb #- array[null];
205///
206/// # Removing non-existent key from an object
207/// query T
208/// SELECT '{"a": 1, "b": 2}'::jsonb #- '{c}';
209/// ----
210/// {"a": 1, "b": 2}
211///
212/// # Removing an existing key from an object
213/// query T
214/// SELECT '{"a": 1, "b": 2}'::jsonb #- '{a}';
215/// ----
216/// {"b": 2}
217///
218/// # Removing an item from an array by positive index
219/// query T
220/// SELECT '["a", "b", "c"]'::jsonb #- '{1}';
221/// ----
222/// ["a", "c"]
223///
224/// # Removing an item from an array by negative index
225/// query T
226/// SELECT '["a", "b", "c"]'::jsonb #- '{-1}';
227/// ----
228/// ["a", "b"]
229///
230/// # Removing a non-existent index from an array
231/// query T
232/// SELECT '["a", "b", "c"]'::jsonb #- '{3}';
233/// ----
234/// ["a", "b", "c"]
235///
236/// # Path element is not an integer for array
237/// query error path element at position 1 is not an integer: "a"
238/// SELECT '["a", "b", "c"]'::jsonb #- '{a}';
239///
240/// # Path to deeply nested value
241/// query T
242/// SELECT '{"a": {"b": {"c": [1, 2, 3]}}}'::jsonb #- '{a,b,c,1}';
243/// ----
244/// {"a": {"b": {"c": [1, 3]}}}
245///
246/// # Path terminates early (before reaching the final depth of the JSON)
247/// query T
248/// SELECT '{"a": {"b": {"c": [1, 2, 3]}}}'::jsonb #- '{a}';
249/// ----
250/// {}
251///
252/// # Removing non-existent path in nested structure
253/// query T
254/// SELECT '{"a": {"b": {"c": [1, 2, 3]}}}'::jsonb #- '{a,x}';
255/// ----
256/// {"a": {"b": {"c": [1, 2, 3]}}}
257///
258/// # Path is longer than the depth of the JSON structure
259/// query T
260/// SELECT '{"a": 1}'::jsonb #- '{a,b}';
261/// ----
262/// {"a": 1}
263///
264/// # Edge case: Removing root
265/// query T
266/// SELECT '{"a": 1}'::jsonb #- '{}';
267/// ----
268/// {"a": 1}
269///
270/// # Edge case: Empty array
271/// query T
272/// SELECT '[]'::jsonb #- '{a}';
273/// ----
274/// []
275///
276/// # Edge case: Empty object
277/// query T
278/// SELECT '{}'::jsonb #- '{null}';
279/// ----
280/// {}
281///
282/// query error cannot delete path in scalar
283/// SELECT '1'::jsonb #- '{}';
284/// ```
285#[function("jsonb_delete_path(jsonb, varchar[]) -> jsonb")]
286fn jsonb_delete_path(
287    v: JsonbRef<'_>,
288    path: ListRef<'_>,
289    writer: &mut jsonbb::Builder,
290) -> Result<()> {
291    if v.is_scalar() {
292        return Err(ExprError::InvalidParam {
293            name: "jsonb",
294            reason: "cannot delete path in scalar".into(),
295        });
296    }
297    if path.is_empty() {
298        writer.add_value(v.into());
299        return Ok(());
300    }
301    jsonbb_remove_path(v.into(), path, 0, writer)?;
302    Ok(())
303}
304
305// Recursively remove `path[i..]` from `jsonb` and write the result to `builder`.
306// Panics if `i` is out of bounds.
307fn jsonbb_remove_path(
308    jsonb: ValueRef<'_>,
309    path: ListRef<'_>,
310    i: usize,
311    builder: &mut jsonbb::Builder,
312) -> Result<()> {
313    match jsonb {
314        ValueRef::Object(obj) => {
315            if obj.is_empty() {
316                builder.add_value(jsonb);
317                return Ok(());
318            }
319            let key = path
320                .get(i)
321                .unwrap()
322                .ok_or_else(|| ExprError::InvalidParam {
323                    name: "path",
324                    reason: format!("path element at position {} is null", i + 1).into(),
325                })?
326                .into_utf8();
327            if !obj.contains_key(key) {
328                builder.add_value(jsonb);
329                return Ok(());
330            }
331            builder.begin_object();
332            for (k, v) in obj.iter() {
333                if k != key {
334                    builder.add_string(k);
335                    builder.add_value(v);
336                    continue;
337                }
338                if i != path.len() - 1 {
339                    builder.add_string(k);
340                    // recursively remove path[i+1..] from v
341                    jsonbb_remove_path(v, path, i + 1, builder)?;
342                }
343            }
344            builder.end_object();
345            Ok(())
346        }
347        ValueRef::Array(array) => {
348            if array.is_empty() {
349                builder.add_value(jsonb);
350                return Ok(());
351            }
352            let key = path
353                .get(i)
354                .unwrap()
355                .ok_or_else(|| ExprError::InvalidParam {
356                    name: "path",
357                    reason: format!("path element at position {} is null", i + 1).into(),
358                })?
359                .into_utf8();
360            let idx = key.parse::<i32>().map_err(|_| ExprError::InvalidParam {
361                name: "path",
362                reason: format!(
363                    "path element at position {} is not an integer: \"{}\"",
364                    i + 1,
365                    key
366                )
367                .into(),
368            })?;
369            let Some(idx) = normalize_array_index(array.len(), idx) else {
370                // out of bounds index returns original value
371                builder.add_value(jsonb);
372                return Ok(());
373            };
374            builder.begin_array();
375            for (j, v) in array.iter().enumerate() {
376                if j != idx {
377                    builder.add_value(v);
378                    continue;
379                }
380                if i != path.len() - 1 {
381                    // recursively remove path[i+1..] from v
382                    jsonbb_remove_path(v, path, i + 1, builder)?;
383                }
384            }
385            builder.end_array();
386            Ok(())
387        }
388        _ => {
389            builder.add_value(jsonb);
390            Ok(())
391        }
392    }
393}
394
395/// Normalizes an array index to `0..len`.
396/// Negative indices count from the end. i.e. `-len..0 => 0..len`.
397/// Returns `None` if index is out of bounds.
398fn normalize_array_index(len: usize, index: i32) -> Option<usize> {
399    if index < -(len as i32) || index >= (len as i32) {
400        return None;
401    }
402    if index >= 0 {
403        Some(index as usize)
404    } else {
405        Some((len as i32 + index) as usize)
406    }
407}
408
409/// Recursively removes all object fields that have null values from the given JSON value.
410/// Null values that are not object fields are untouched.
411///
412/// Examples:
413///
414/// ```slt
415/// query T
416/// SELECT jsonb_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]');
417/// ----
418/// [{"f1": 1}, 2, null, 3]
419/// ```
420#[function("jsonb_strip_nulls(jsonb) -> jsonb")]
421fn jsonb_strip_nulls(v: JsonbRef<'_>, writer: &mut jsonbb::Builder) {
422    jsonbb_strip_nulls(v.into(), writer);
423}
424
425/// Recursively removes all object fields that have null values from the given JSON value.
426fn jsonbb_strip_nulls(jsonb: ValueRef<'_>, builder: &mut jsonbb::Builder) {
427    match jsonb {
428        ValueRef::Object(obj) => {
429            builder.begin_object();
430            for (k, v) in obj.iter() {
431                if let ValueRef::Null = v {
432                    continue;
433                }
434                builder.add_string(k);
435                jsonbb_strip_nulls(v, builder);
436            }
437            builder.end_object();
438        }
439        ValueRef::Array(array) => {
440            builder.begin_array();
441            for v in array.iter() {
442                jsonbb_strip_nulls(v, builder);
443            }
444            builder.end_array();
445        }
446        _ => builder.add_value(jsonb),
447    }
448}
risingwave_expr_impl/scalar/jsonb_delete.rs

risingwave_expr_impl/scalar/
jsonb_delete.rs
