risingwave_expr_impl/scalar/
jsonb_access.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::fmt::Write;
16
17use risingwave_common::row::Row;
18use risingwave_common::types::JsonbRef;
19use risingwave_expr::function;
20
21/// Extracts JSON object field with the given key.
22///
23/// `jsonb -> text → jsonb`
24///
25/// # Examples
26///
27/// ```slt
28/// query T
29/// select '{"a": {"b":"foo"}}'::jsonb -> 'a';
30/// ----
31/// {"b": "foo"}
32/// ```
33#[function("jsonb_access(jsonb, varchar) -> jsonb")]
34pub fn jsonb_object_field<'a>(v: JsonbRef<'a>, p: &str) -> Option<JsonbRef<'a>> {
35    v.access_object_field(p)
36}
37
38/// Extracts n'th element of JSON array (array elements are indexed from zero,
39/// but negative integers count from the end).
40///
41/// `jsonb -> integer → jsonb`
42///
43/// # Examples
44///
45/// ```slt
46/// query T
47/// select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> 2;
48/// ----
49/// {"c": "baz"}
50///
51/// query T
52/// select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> -3;
53/// ----
54/// {"a": "foo"}
55/// ```
56#[function("jsonb_access(jsonb, int4) -> jsonb")]
57pub fn jsonb_array_element(v: JsonbRef<'_>, p: i32) -> Option<JsonbRef<'_>> {
58    let idx = if p < 0 {
59        let Ok(len) = v.array_len() else {
60            return None;
61        };
62        if ((-p) as usize) > len {
63            return None;
64        } else {
65            len - ((-p) as usize)
66        }
67    } else {
68        p as usize
69    };
70    v.access_array_element(idx)
71}
72
73/// Extracts JSON sub-object at the specified path, where path elements can be either field keys or array indexes.
74///
75/// - `jsonb #> text[] → jsonb`
76/// - `jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb`
77///
78/// # Examples
79///
80/// ```slt
81/// query T
82/// select '{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,1}'::text[];
83/// ----
84/// "bar"
85///
86/// query T
87/// select '{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,null}'::text[];
88/// ----
89/// NULL
90///
91/// query T
92/// select jsonb_extract_path('{"a": {"b": ["foo","bar"]}}', 'a', 'b', '1');
93/// ----
94/// "bar"
95///
96/// query T
97/// select jsonb_extract_path('{"a": {"b": ["foo","bar"]}}', variadic array['a', 'b', '1']);
98/// ----
99/// "bar"
100/// ```
101#[function("jsonb_extract_path(jsonb, variadic varchar[]) -> jsonb")]
102pub fn jsonb_extract_path(v: JsonbRef<'_>, path: impl Row) -> Option<JsonbRef<'_>> {
103    let mut jsonb = v;
104    for key in path.iter() {
105        // return null if any element is null
106        let key = key?.into_utf8();
107        if jsonb.is_array() {
108            // return null if the key is not an integer
109            let idx = key.parse().ok()?;
110            jsonb = jsonb_array_element(jsonb, idx)?;
111        } else if jsonb.is_object() {
112            jsonb = jsonb_object_field(jsonb, key)?;
113        } else {
114            return None;
115        }
116    }
117    Some(jsonb)
118}
119
120/// Extracts JSON object field with the given key, as text.
121///
122/// `jsonb ->> text → text`
123///
124/// # Examples
125///
126/// ```slt
127/// query T
128/// select '{"a":1,"b":2}'::jsonb ->> 'b';
129/// ----
130/// 2
131///
132/// query T
133/// select '{"a":1,"b":null}'::jsonb ->> 'b';
134/// ----
135/// NULL
136/// ```
137#[function("jsonb_access_str(jsonb, varchar) -> varchar")]
138pub fn jsonb_object_field_str(v: JsonbRef<'_>, p: &str, writer: &mut impl Write) -> Option<()> {
139    let jsonb = jsonb_object_field(v, p)?;
140    if jsonb.is_jsonb_null() {
141        return None;
142    }
143    jsonb.force_str(writer).unwrap();
144    Some(())
145}
146
147/// Extracts n'th element of JSON array, as text.
148///
149/// `jsonb ->> integer → text`
150///
151/// # Examples
152///
153/// ```slt
154/// query T
155/// select '[1,2,3]'::jsonb ->> 2;
156/// ----
157/// 3
158///
159/// query T
160/// select '[1,2,null]'::jsonb ->> 2;
161/// ----
162/// NULL
163/// ```
164#[function("jsonb_access_str(jsonb, int4) -> varchar")]
165pub fn jsonb_array_element_str(v: JsonbRef<'_>, p: i32, writer: &mut impl Write) -> Option<()> {
166    let jsonb = jsonb_array_element(v, p)?;
167    if jsonb.is_jsonb_null() {
168        return None;
169    }
170    jsonb.force_str(writer).unwrap();
171    Some(())
172}
173
174/// Extracts JSON sub-object at the specified path as text.
175///
176/// - `jsonb #>> text[] → text`
177/// - `jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text`
178///
179/// # Examples
180///
181/// ```slt
182/// query T
183/// select '{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,1}'::text[];
184/// ----
185/// bar
186///
187/// query T
188/// select '{"a": {"b": ["foo",null]}}'::jsonb #>> '{a,b,1}'::text[];
189/// ----
190/// NULL
191///
192/// query T
193/// select '{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,null}'::text[];
194/// ----
195/// NULL
196///
197/// query T
198/// select jsonb_extract_path_text('{"a": {"b": ["foo","bar"]}}', 'a', 'b', '1');
199/// ----
200/// bar
201///
202/// query T
203/// select jsonb_extract_path_text('{"a": {"b": ["foo","bar"]}}', variadic array['a', 'b', '1']);
204/// ----
205/// bar
206/// ```
207#[function("jsonb_extract_path_text(jsonb, variadic varchar[]) -> varchar")]
208pub fn jsonb_extract_path_text(
209    v: JsonbRef<'_>,
210    path: impl Row,
211    writer: &mut impl Write,
212) -> Option<()> {
213    let jsonb = jsonb_extract_path(v, path)?;
214    if jsonb.is_jsonb_null() {
215        return None;
216    }
217    jsonb.force_str(writer).unwrap();
218    Some(())
219}
risingwave_expr_impl/scalar/jsonb_access.rs

risingwave_expr_impl/scalar/
jsonb_access.rs
