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

risingwave_expr_impl/scalar/
jsonb_delete.rs
