sea_query/extension/postgres/func/json_table/
mod.rs

1use crate::*;
2use std::borrow::Cow;
3
4mod builder;
5pub use builder::Builder;
6mod column;
7pub use column::ColumnBuilder;
8mod exists_column;
9pub use exists_column::ExistsColumnBuilder;
10mod nested;
11pub use nested::NestedPathBuilder;
12pub(super) mod types;
13pub use types::*;
14
15impl PgFunc {
16    /// Create a `JSON_TABLE` function builder. Postgres only.
17    ///
18    /// # Examples
19    ///
20    /// Basic usage with simple columns:
21    /// ```
22    /// use sea_query::extension::postgres::*;
23    /// use sea_query::{tests_cfg::*, *};
24    ///
25    /// let query = Query::select()
26    ///     .from_function(
27    ///         PgFunc::json_table(
28    ///             Expr::val(r#"[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]"#),
29    ///             "$[*]"
30    ///         )
31    ///         .ordinality_column("row_number")
32    ///         .column("name", "text").path("$.name").build_column()
33    ///         .column("age", "int").path("$.age").build_column()
34    ///         .build(),
35    ///         "people"
36    ///     )
37    ///     .column("*")
38    ///     .to_owned();
39    ///
40    /// assert_eq!(
41    ///     query.to_string(PostgresQueryBuilder),
42    ///     r#"SELECT "*" FROM JSON_TABLE('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]', '$[*]' COLUMNS (row_number FOR ORDINALITY, name "text" PATH '$.name', age "int" PATH '$.age')) AS "people""#
43    /// );
44    /// ```
45    ///
46    /// With PASSING parameters and error handling:
47    /// ```
48    /// use sea_query::extension::postgres::*;
49    /// use sea_query::{tests_cfg::*, *};
50    ///
51    /// let query = Query::select()
52    ///     .from_function(
53    ///         PgFunc::json_table(
54    ///             Expr::val(r#"{"users": [{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]}"#),
55    ///             "$.users[*] ? (@.id > $min_id)"
56    ///         )
57    ///         .passing(0, "min_id")
58    ///         .column("user_id", "int").path("$.id").null_on_error().build_column()
59    ///         .column("user_name", "text").path("$.name").default_on_empty(Expr::val("Unknown")).build_column()
60    ///         .error_on_error()
61    ///         .build(),
62    ///         "filtered_users"
63    ///     )
64    ///     .column("*")
65    ///     .to_owned();
66    ///
67    /// assert_eq!(
68    ///     query.to_string(PostgresQueryBuilder),
69    ///     r#"SELECT "*" FROM JSON_TABLE('{"users": [{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]}', '$.users[*] ? (@.id > $min_id)' PASSING 0 AS min_id COLUMNS (user_id "int" PATH '$.id' NULL ON ERROR, user_name "text" PATH '$.name' DEFAULT 'Unknown' ON EMPTY) ERROR ON ERROR) AS "filtered_users""#
70    /// );
71    /// ```
72    ///
73    /// With NESTED PATH:
74    /// ```
75    /// use sea_query::extension::postgres::*;
76    /// use sea_query::{tests_cfg::*, *};
77    ///
78    /// let query = Query::select()
79    ///     .from_function(
80    ///         PgFunc::json_table(
81    ///             Expr::val(r#"{"users": [{"name": "John", "phones": ["123", "456"]}, {"name": "Jane", "phones": ["789"]}]}"#),
82    ///             "$.users[*]"
83    ///         )
84    ///         .column("user_name", "text").path("$.name").build_column()
85    ///         .nested("$.phones[*]")
86    ///             .column("phone", "text").path("$").build_column()
87    ///             .build_nested()
88    ///         .build(),
89    ///         "user_phones"
90    ///     )
91    ///     .column("*")
92    ///     .to_owned();
93    ///
94    /// assert_eq!(
95    ///     query.to_string(PostgresQueryBuilder),
96    ///     r#"SELECT "*" FROM JSON_TABLE('{"users": [{"name": "John", "phones": ["123", "456"]}, {"name": "Jane", "phones": ["789"]}]}', '$.users[*]' COLUMNS (user_name "text" PATH '$.name', NESTED '$.phones[*]' COLUMNS (phone "text" PATH '$'))) AS "user_phones""#
97    /// );
98    /// ```
99    pub fn json_table<T, P>(context_item: T, path_expression: P) -> Builder
100    where
101        T: Into<Expr>,
102        P: Into<Cow<'static, str>>,
103    {
104        Builder {
105            context_item: context_item.into(),
106            path_expression: path_expression.into(),
107            as_json_path_name: None,
108            passing: Vec::new(),
109            columns: Vec::new(),
110            on_error: None,
111        }
112    }
113}
sea_query/extension/postgres/func/json_table/mod.rs

sea_query/extension/postgres/func/json_table/
mod.rs
