On Mon, May 20, 2024 at 7:51 PM Amit Langote wrote:
>
> Hi Thom,
>>
> > and I think we need to either remove the leading "select" keyword, or
> > uppercase it in the examples.
> >
> > For example (on
> > https://www.postgresql.org/docs/devel/functions-json.html#SQLJSON-QUERY-FUNCTIONS):
> >
> >
Hi Thom,
On Thu, May 16, 2024 at 8:50 AM Thom Brown wrote:
> On Mon, 8 Apr 2024 at 10:09, Amit Langote wrote:
>>
>> On Mon, Apr 8, 2024 at 2:02 PM jian he wrote:
>> > On Mon, Apr 8, 2024 at 11:21 AM jian he
>> > wrote:
>> > >
>> > > On Mon, Apr 8, 2024 at 12:34 AM jian he
>> > > wrote:
>>
On Mon, 8 Apr 2024 at 10:09, Amit Langote wrote:
> On Mon, Apr 8, 2024 at 2:02 PM jian he
> wrote:
> > On Mon, Apr 8, 2024 at 11:21 AM jian he
> wrote:
> > >
> > > On Mon, Apr 8, 2024 at 12:34 AM jian he
> wrote:
> > > >
> > > > On Sun, Apr 7, 2024 at 9:36 PM Amit Langote
> wrote:
> > > > >
On Mon, Apr 8, 2024 at 2:02 PM jian he wrote:
> On Mon, Apr 8, 2024 at 11:21 AM jian he wrote:
> >
> > On Mon, Apr 8, 2024 at 12:34 AM jian he wrote:
> > >
> > > On Sun, Apr 7, 2024 at 9:36 PM Amit Langote
> > > wrote:
> > > > 0002 needs an expanded commit message but I've run out of energy
On Mon, Apr 8, 2024 at 11:21 AM jian he wrote:
>
> On Mon, Apr 8, 2024 at 12:34 AM jian he wrote:
> >
> > On Sun, Apr 7, 2024 at 9:36 PM Amit Langote wrote:
> > > 0002 needs an expanded commit message but I've run out of energy today.
> > >
>
> other than that, it looks good to me.
one more
On Mon, Apr 8, 2024 at 12:34 AM jian he wrote:
>
> On Sun, Apr 7, 2024 at 9:36 PM Amit Langote wrote:
> > 0002 needs an expanded commit message but I've run out of energy today.
> >
>
+/*
+ * Fetch next row from a JsonTablePlan's path evaluation result and from
+ * any child nested path(s).
+ *
On Sun, Apr 7, 2024 at 9:36 PM Amit Langote wrote:
>
>
> 0002 needs an expanded commit message but I've run out of energy today.
>
some cosmetic issues in v51, 0002.
in struct JsonTablePathScan,
/* ERROR/EMPTY ON ERROR behavior */
bool errorOnError;
the comments seem not right.
I think
On Sun, Apr 7, 2024 at 10:21 PM jian he wrote:
> On Sun, Apr 7, 2024 at 12:30 PM jian he wrote:
> >
> > other than that, it looks good to me.
> while looking at it again.
>
> + | NESTED path_opt Sconst
> + COLUMNS '(' json_table_column_definition_list ')'
> + {
> + JsonTableColumn *n =
On Sun, Apr 7, 2024 at 12:30 PM jian he wrote:
>
> other than that, it looks good to me.
while looking at it again.
+ | NESTED path_opt Sconst
+ COLUMNS '(' json_table_column_definition_list ')'
+ {
+ JsonTableColumn *n = makeNode(JsonTableColumn);
+
+ n->coltype = JTC_NESTED;
+ n->pathspec =
hi.
about v50.
+/*
+ * JsonTableSiblingJoin -
+ * Plan to union-join rows of nested paths of the same level
+ */
+typedef struct JsonTableSiblingJoin
+{
+ JsonTablePlan plan;
+
+ JsonTablePlan *lplan;
+ JsonTablePlan *rplan;
+} JsonTableSiblingJoin;
"Plan to union-join rows of nested paths of the
Hi,
On Sat, Apr 6, 2024 at 3:55 PM jian he wrote:
> On Sat, Apr 6, 2024 at 2:03 PM Amit Langote wrote:
> >
> > >
> > > * problem with type "char". the view def output is not the same as
> > > the select * from v1.
> > >
> > > create or replace view v1 as
> > > SELECT col FROM s,
> > >
On Fri, Apr 5, 2024 at 8:35 PM Amit Langote wrote:
>
> On Thu, Apr 4, 2024 at 9:02 PM Amit Langote wrote:
> > I'll post the rebased 0002 tomorrow after addressing your comments.
>
> Here's one. Main changes:
>
> * Fixed a bug in get_table_json_columns() which caused nested columns
> to be
On Sat, Apr 6, 2024 at 2:03 PM Amit Langote wrote:
>
> >
> > * problem with type "char". the view def output is not the same as
> > the select * from v1.
> >
> > create or replace view v1 as
> > SELECT col FROM s,
> > JSON_TABLE(jsonb '{"d": ["hello", "hello1"]}', '$' as c1
> > COLUMNS(col
On Sat, Apr 6, 2024 at 12:31 PM jian he wrote:
> On Fri, Apr 5, 2024 at 8:35 PM Amit Langote wrote:
> > Here's one. Main changes:
> >
> > * Fixed a bug in get_table_json_columns() which caused nested columns
> > to be deparsed incorrectly, something Jian reported upthread.
> > * Simplified the
Hi Michael,
On Fri, Apr 5, 2024 at 3:07 PM Michael Paquier wrote:
> On Fri, Apr 05, 2024 at 09:00:00AM +0300, Alexander Lakhin wrote:
> > Please look at an assertion failure:
> > TRAP: failed Assert("count <= tupdesc->natts"), File: "parse_relation.c",
> > Line: 3048, PID: 1325146
> >
> >
On Fri, Apr 5, 2024 at 8:35 PM Amit Langote wrote:
> Here's one. Main changes:
>
> * Fixed a bug in get_table_json_columns() which caused nested columns
> to be deparsed incorrectly, something Jian reported upthread.
> * Simplified the algorithm in JsonTablePlanNextRow()
>
> I'll post another
On Thu, Apr 4, 2024 at 9:02 PM Amit Langote wrote:
> I'll post the rebased 0002 tomorrow after addressing your comments.
Here's one. Main changes:
* Fixed a bug in get_table_json_columns() which caused nested columns
to be deparsed incorrectly, something Jian reported upthread.
* Simplified
On Fri, Apr 5, 2024 at 5:00 PM Alexander Lakhin wrote:
> 05.04.2024 10:09, Amit Langote wrote:
> > Seems like it might be a pre-existing issue, because I can also
> > reproduce the crash with:
>
> That's strange, because I get the error (on master, 6f132ed69).
> With backtrace_functions =
05.04.2024 10:09, Amit Langote wrote:
Seems like it might be a pre-existing issue, because I can also
reproduce the crash with:
SELECT * FROM COALESCE(row(1)) AS (a int, b int);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while
Hi Alexander,
On Fri, Apr 5, 2024 at 3:00 PM Alexander Lakhin wrote:
>
> Hello Amit,
>
> 04.04.2024 15:02, Amit Langote wrote:
> > Pushed after fixing these and a few other issues. I didn't include
> > the testing function you proposed in your other email. It sounds
> > useful for testing
On Fri, Apr 05, 2024 at 09:00:00AM +0300, Alexander Lakhin wrote:
> Please look at an assertion failure:
> TRAP: failed Assert("count <= tupdesc->natts"), File: "parse_relation.c",
> Line: 3048, PID: 1325146
>
> triggered by the following query:
> SELECT * FROM JSON_TABLE('0', '$' COLUMNS (js
Hello Amit,
04.04.2024 15:02, Amit Langote wrote:
Pushed after fixing these and a few other issues. I didn't include
the testing function you proposed in your other email. It sounds
useful for testing locally but will need some work before we can
include it in the tree.
I'll post the rebased
On Wed, Apr 3, 2024 at 11:48 PM jian he wrote:
> hi.
> +
> + json_table is an SQL/JSON function which
> + queries JSON data
> + and presents the results as a relational view, which can be accessed as a
> + regular SQL table. You can only use
> json_table inside the
> + FROM clause of
On Thu, Apr 4, 2024 at 3:50 PM jian he wrote:
>
> On Thu, Apr 4, 2024 at 2:41 PM jian he wrote:
> >
> > On Wed, Apr 3, 2024 at 8:39 PM Amit Langote wrote:
> > >
> > > Attached updated patches. I have addressed your doc comments on 0001,
> > > but not 0002 yet.
hi
some doc issue about v49,
On Thu, Apr 4, 2024 at 2:41 PM jian he wrote:
>
> On Wed, Apr 3, 2024 at 8:39 PM Amit Langote wrote:
> >
> > Attached updated patches. I have addressed your doc comments on 0001,
> > but not 0002 yet.
> >
>
about v49, 0002.
--tests setup.
drop table if exists s cascade;
create table s(js
On Wed, Apr 3, 2024 at 8:39 PM Amit Langote wrote:
>
> Attached updated patches. I have addressed your doc comments on 0001,
> but not 0002 yet.
>
in v49, 0002.
+\sv jsonb_table_view1
+CREATE OR REPLACE VIEW public.jsonb_table_view1 AS
+ SELECT id,
+a1,
+b1,
+a11,
+a21,
+a22
hi.
+
+ json_table is an SQL/JSON function which
+ queries JSON data
+ and presents the results as a relational view, which can be accessed as a
+ regular SQL table. You can only use
json_table inside the
+ FROM clause of a SELECT,
+ UPDATE, DELETE, or
MERGE
+ statement.
+
the
On Wed, Apr 3, 2024 at 3:15 PM jian he wrote:
>
> On Wed, Apr 3, 2024 at 11:30 AM jian he wrote:
> >
> > On Tue, Apr 2, 2024 at 9:57 PM Amit Langote wrote:
> > >
> > > Please let me know if you have further comments on 0001. I'd like to
> > > get that in before spending more energy on 0002.
>
On Wed, Apr 3, 2024 at 11:30 AM jian he wrote:
>
> On Tue, Apr 2, 2024 at 9:57 PM Amit Langote wrote:
> >
> > Please let me know if you have further comments on 0001. I'd like to
> > get that in before spending more energy on 0002.
> >
-- a/src/backend/parser/parse_target.c
+++
On Tue, Apr 2, 2024 at 9:57 PM Amit Langote wrote:
>
> Please let me know if you have further comments on 0001. I'd like to
> get that in before spending more energy on 0002.
>
hi. some issues with the doc.
i think, some of the "path expression" can be replaced by
"path_expression".
maybe not
On Fri, Mar 22, 2024 at 12:08 AM Amit Langote wrote:
>
> On Wed, Mar 20, 2024 at 9:53 PM Amit Langote wrote:
> > I'll push 0001 tomorrow.
>
> Pushed that one. Here's the remaining JSON_TABLE() patch.
>
I know v45 is very different from v47.
but v45 contains all the remaining features to be
hi.
+/*
+ * Recursively transform child JSON_TABLE plan.
+ *
+ * Default plan is transformed into a cross/union join of its nested columns.
+ * Simple and outer/inner plans are transformed into a JsonTablePlan by
+ * finding and transforming corresponding nested column.
+ * Sibling plans are
On Mon, Apr 1, 2024 at 8:00 AM jian he wrote:
>
> +-- Should fail (JSON arguments are not passed to column paths)
> +SELECT *
> +FROM JSON_TABLE(
> + jsonb '[1,2,3]',
> + '$[*] ? (@ < $x)'
> + PASSING 10 AS x
> + COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)')
> + ) jt;
> +ERROR: could not find
typedef struct JsonTableExecContext
{
int magic;
JsonTablePlanState *rootplanstate;
JsonTablePlanState **colexprplans;
} JsonTableExecContext;
imho, this kind of naming is kind of inconsistent.
"state" and "plan" are mixed together.
maybe
typedef struct JsonTableExecContext
{
int magic;
FAILED: src/interfaces/ecpg/test/sql/sqljson_jsontable.c
/home/jian/postgres/buildtest6/src/interfaces/ecpg/preproc/ecpg
--regression -I../../Desktop/pg_src/src6/postgres/src/interfaces/ecpg/test/sql
-I../../Desktop/pg_src/src6/postgres/src/interfaces/ecpg/include/ -o
Hi Alvaro,
On Fri, Mar 29, 2024 at 2:04 AM Alvaro Herrera wrote:
> On 2024-Mar-28, Amit Langote wrote:
>
> > Here's patch 1 for the time being that implements barebones
> > JSON_TABLE(), that is, without NESTED paths/columns and PLAN clause.
> > I've tried to shape the interfaces so that those
On Fri, Mar 29, 2024 at 11:20 AM jian he wrote:
>
>
> +
> +JSON_TABLE (
> +context_item,
> path_expression AS
> json_path_name
> PASSING { value AS
> varname } , ...
>
> +COLUMNS ( class="parameter">json_table_column ,
> ... )
> + { ERROR | EMPTY
> } ON ERROR
> +)
> top level
On Thu, Mar 28, 2024 at 1:23 PM Amit Langote wrote:
>
> On Wed, Mar 27, 2024 at 1:34 PM Amit Langote wrote:
> > On Wed, Mar 27, 2024 at 12:42 PM jian he
> > wrote:
> > > hi.
> > > I don't fully understand all the code in json_table patch.
> > > maybe we can split it into several patches,
> >
>
On 2024-Mar-28, Amit Langote wrote:
> Here's patch 1 for the time being that implements barebones
> JSON_TABLE(), that is, without NESTED paths/columns and PLAN clause.
> I've tried to shape the interfaces so that those features can be added
> in future commits without significant rewrite of the
On Wed, Mar 27, 2024 at 12:42 PM jian he wrote:
> hi.
> I don't fully understand all the code in json_table patch.
> maybe we can split it into several patches,
I'm working on exactly that atm.
> like:
> * no nested json_table_column.
> * nested json_table_column, with PLAN DEFAULT
> * nested
On Tue, Mar 26, 2024 at 6:16 PM jian he wrote:
>
> On Fri, Mar 22, 2024 at 12:08 AM Amit Langote wrote:
> >
> > On Wed, Mar 20, 2024 at 9:53 PM Amit Langote
> > wrote:
> > > I'll push 0001 tomorrow.
> >
> > Pushed that one. Here's the remaining JSON_TABLE() patch.
> >
hi.
I don't fully
On Fri, Mar 22, 2024 at 12:08 AM Amit Langote wrote:
>
> On Wed, Mar 20, 2024 at 9:53 PM Amit Langote wrote:
> > I'll push 0001 tomorrow.
>
> Pushed that one. Here's the remaining JSON_TABLE() patch.
>
hi. minor issues i found json_table patch.
+ if (!IsA($5, A_Const) ||
+ castNode(A_Const,
At Fri, 22 Mar 2024 11:44:08 +0900, Amit Langote
wrote in
> Thanks for the heads up.
>
> My bad, will push a fix shortly.
No problem. Thank you for the prompt correction.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
Hi Horiguchi-san,
On Fri, Mar 22, 2024 at 9:51 AM Kyotaro Horiguchi
wrote:
> At Wed, 20 Mar 2024 21:53:52 +0900, Amit Langote
> wrote in
> > I'll push 0001 tomorrow.
>
> This patch (v44-0001-Add-SQL-JSON-query-functions.patch) introduced the
> following new erro message:
>
> +
At Wed, 20 Mar 2024 21:53:52 +0900, Amit Langote
wrote in
> I'll push 0001 tomorrow.
This patch (v44-0001-Add-SQL-JSON-query-functions.patch) introduced the
following new erro message:
+errmsg("can only specify
constant, non-aggregate"
+
looking at documentation again.
one very minor question (issue)
+
+The ON EMPTY clause specifies the behavior if the
+path_expression yields no value at all; the
+default when ON EMPTY is not specified is to return
+a null value.
+
I think it should
minor issues I found while looking through it.
other than these issues, looks good!
/*
* Convert the a given JsonbValue to its C string representation
*
* Returns the string as a Datum setting *resnull if the JsonbValue is a
* a jbvNull.
*/
static char *
ExecGetJsonValueItemString(JsonbValue
On Tue, Mar 19, 2024 at 6:46 PM Amit Langote wrote:
>
> I intend to commit 0001+0002 after a bit more polishing.
>
V43 is far more intuitive! thanks!
if (isnull ||
(exprType(expr) == JSONBOID &&
btype == default_behavior))
coerce = true;
else
coerced_expr =
coerce_to_target_type(pstate, expr,
On Mon, Mar 18, 2024 at 3:33 PM Amit Langote
wrote:
> Himanshu,
>
> On Mon, Mar 18, 2024 at 4:57 PM Himanshu Upadhyaya
> wrote:
> > I have tested a nested case but why is the negative number allowed in
> subscript(NESTED '$.phones[-1]'COLUMNS), it should error out if the number
> is negative.
Himanshu,
On Mon, Mar 18, 2024 at 4:57 PM Himanshu Upadhyaya
wrote:
> I have tested a nested case but why is the negative number allowed in
> subscript(NESTED '$.phones[-1]'COLUMNS), it should error out if the number is
> negative.
>
> ‘postgres[170683]=#’SELECT * FROM JSON_TABLE(jsonb '{
>
I have tested a nested case but why is the negative number allowed in
subscript(NESTED '$.phones[-1]'COLUMNS), it should error out if the number
is negative.
‘postgres[170683]=#’SELECT * FROM JSON_TABLE(jsonb '{
‘...>’ "id" : "0.234567897890",
‘...>’ "name" : {
On Wed, Mar 13, 2024 at 5:47 AM Alvaro Herrera wrote:
> About 0002:
>
> I think we should just drop it. Look at the changes it produces in the
> plans for aliases XMLTABLE:
>
> > @@ -1556,7 +1556,7 @@ SELECT f.* FROM xmldata, LATERAL
> > xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COU
> >
On Mon, Mar 11, 2024 at 11:30 AM jian he wrote:
>
> On Sun, Mar 10, 2024 at 10:57 PM jian he wrote:
> >
> > one more issue.
>
> Hi
> one more documentation issue.
> after applied V42, 0001 to 0003,
> there are 11 appearance of `FORMAT JSON` in functions-json.html
> still not a single place
one more question...
SELECT JSON_value(NULL::int, '$' returning int);
ERROR: cannot use non-string types with implicit FORMAT JSON clause
LINE 1: SELECT JSON_value(NULL::int, '$' returning int);
^
SELECT JSON_query(NULL::int, '$' returning int);
ERROR: cannot use
On Tue, Mar 12, 2024 at 5:37 PM Amit Langote
wrote:
>
>
> SELECT JSON_EXISTS(jsonb '{"customer_name": "test", "salary":1000,
> "department_id":1}', '$ ? (@.department_id == $dept_id && @.salary ==
> $sal)' PASSING 1000 AS sal, 1 as dept_id);
> json_exists
> -
> t
> (1 row)
>
> Does
About 0002:
I think we should just drop it. Look at the changes it produces in the
plans for aliases XMLTABLE:
> @@ -1556,7 +1556,7 @@ SELECT f.* FROM xmldata, LATERAL
> xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COU
> Output: f."COUNTRY_NAME", f."REGION_ID"
> -> Seq Scan on
Hi Himanshu,
On Tue, Mar 12, 2024 at 6:42 PM Himanshu Upadhyaya
wrote:
>
> Hi,
>
> wanted to share the below case:
>
> ‘postgres[146443]=#’SELECT JSON_EXISTS(jsonb '{"customer_name": "test",
> "salary":1000, "department_id":1}', '$.* ? (@== $dept_id && @ == $sal)'
> PASSING 1000 AS sal, 1 as
Hi,
wanted to share the below case:
‘postgres[146443]=#’SELECT JSON_EXISTS(jsonb '{"customer_name": "test",
"salary":1000, "department_id":1}', '$.* ? (@== $dept_id && @ == $sal)'
PASSING 1000 AS sal, 1 as dept_id);
json_exists
-
f
(1 row)
isn't it supposed to return "true" as
Thanka Alvaro. It works fine when quotes are used around the column name.
On Mon, Mar 11, 2024 at 9:04 PM Alvaro Herrera
wrote:
> On 2024-Mar-11, Shruthi Gowda wrote:
>
> > *CASE 2:*
> > --
> > SELECT * FROM JSON_TABLE(jsonb '{
> > "id" : 901,
> > "age" : 30,
>
On 2024-Mar-11, Shruthi Gowda wrote:
> *CASE 2:*
> --
> SELECT * FROM JSON_TABLE(jsonb '{
> "id" : 901,
> "age" : 30,
> "*FULL_NAME*" : "KATE DANIEL"}',
> '$'
> COLUMNS(
> FULL_NAME varchar(20),
>
Hi,
I was experimenting with the v42 patches, and I tried testing without
providing the path explicitly. There is one difference between the two test
cases that I have highlighted in blue.
The full_name column is empty in the second test case result. Let me know
if this is an issue or expected
Hi.
more minor issues.
by searching `elog(ERROR, "unrecognized node type: %d"`
I found that generally enum is cast to int, before printing it out.
I also found a related post at [1].
So I add the typecast to int, before printing it out.
most of the refactored code is unlikely to be reachable,
one more issue.
+-- Extension: non-constant JSON path
+SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo'
ON EMPTY);
+SELECT JSON_QUERY(jsonb '{"a": 123}',
On Sun, Mar 10, 2024 at 10:57 PM jian he wrote:
>
> one more issue.
Hi
one more documentation issue.
after applied V42, 0001 to 0003,
there are 11 appearance of `FORMAT JSON` in functions-json.html
still not a single place explained what it is for.
json_query ( context_item, path_expression [
one more issue.
+ case JSON_VALUE_OP:
+ /* Always omit quotes from scalar strings. */
+ jsexpr->omit_quotes = (func->quotes == JS_QUOTES_OMIT);
+
+ /* JSON_VALUE returns text by default. */
+ if (!OidIsValid(jsexpr->returning->typid))
+ {
+ jsexpr->returning->typid = TEXTOID;
+
jian he writes:
> On Tue, Mar 5, 2024 at 12:38 PM Andy Fan wrote:
>>
>>
>> In the commit message of 0001, we have:
>>
>> """
>> Both JSON_VALUE() and JSON_QUERY() functions have options for
>> handling EMPTY and ERROR conditions, which can be used to specify
>> the behavior when no values are
I looked at the documentation again.
one more changes for JSON_QUERY:
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3e58ebd2..0c49b321 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18715,8 +18715,8 @@ ERROR: jsonpath array subscript is out of bounds
On Thu, Mar 7, 2024 at 23:14 Alvaro Herrera wrote:
> On 2024-Mar-07, Tomas Vondra wrote:
>
> > I was experimenting with the v42 patches, and I think the handling of ON
> > EMPTY / ON ERROR clauses may need some improvement.
>
> Well, the 2023 standard says things like
>
> ::=
> JSON_VALUE
>
On 2024-Mar-07, Tomas Vondra wrote:
> I was experimenting with the v42 patches, and I think the handling of ON
> EMPTY / ON ERROR clauses may need some improvement.
Well, the 2023 standard says things like
::=
JSON_VALUE
[ ]
[ ON EMPTY ]
[ ON ERROR ]
which
On Thu, Mar 7, 2024 at 22:46 jian he wrote:
> On Thu, Mar 7, 2024 at 8:06 PM Amit Langote
> wrote:
> >
> >
> > Indeed.
> >
> > This boils down to the difference in the cast expression chosen to
> > convert the source value to int in the two cases.
> >
> > The case where the source value has no
Hi,
I was experimenting with the v42 patches, and I think the handling of ON
EMPTY / ON ERROR clauses may need some improvement. The grammar is
currently defined like this:
| json_behavior ON EMPTY_P json_behavior ON ERROR_P
This means the clauses have to be defined exactly in this order,
On Thu, Mar 7, 2024 at 8:06 PM Amit Langote wrote:
>
>
> Indeed.
>
> This boils down to the difference in the cast expression chosen to
> convert the source value to int in the two cases.
>
> The case where the source value has no quotes, the chosen cast
> expression is a FuncExpr for function
On Thu, Mar 7, 2024 at 8:13 PM Tomas Vondra
wrote:
> On 3/7/24 06:18, Himanshu Upadhyaya wrote:
Thanks Himanshu for the testing.
> > On Wed, Mar 6, 2024 at 9:04 PM Tomas Vondra
> > wrote:
> >>
> >> I'm pretty sure this is the correct & expected behavior. The second
> >> query treats the value
On 3/7/24 06:18, Himanshu Upadhyaya wrote:
> On Wed, Mar 6, 2024 at 9:04 PM Tomas Vondra
> wrote:
>
>>
>>
>> I'm pretty sure this is the correct & expected behavior. The second
>> query treats the value as string (because that's what should happen for
>> values in double quotes).
>>
>> ok,
On Wed, Mar 6, 2024 at 9:04 PM Tomas Vondra
wrote:
>
>
> I'm pretty sure this is the correct & expected behavior. The second
> query treats the value as string (because that's what should happen for
> values in double quotes).
>
> ok, Then why does the below query provide the correct
two cosmetic minor issues.
+/*
+ * JsonCoercion
+ * Information about coercing a SQL/JSON value to the specified
+ * type at runtime
+ *
+ * A node of this type is created if the parser cannot find a cast expression
+ * using coerce_type() or OMIT QUOTES is specified for JSON_QUERY. If the
+ *
On Tue, Mar 5, 2024 at 12:38 PM Andy Fan wrote:
>
>
> In the commit message of 0001, we have:
>
> """
> Both JSON_VALUE() and JSON_QUERY() functions have options for
> handling EMPTY and ERROR conditions, which can be used to specify
> the behavior when no values are matched and when an error
On Wed, Mar 6, 2024 at 9:22 PM jian he wrote:
>
> Another case, I did test yet: more keys in a single json, but the
> value is small.
Another case attached. see the attached SQL file's comments.
a single simple jsonb, with 33 keys, each key's value with fixed length: 256.
total table size:
On 3/6/24 12:58, Himanshu Upadhyaya wrote:
> On Tue, Mar 5, 2024 at 6:52 AM Amit Langote wrote:
>
> Hi,
>
> I am doing some random testing with the latest patch and found one scenario
> that I wanted to share.
> consider a below case.
>
> ‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb
On Wed, Mar 6, 2024 at 12:07 PM Amit Langote wrote:
>
> Hi Tomas,
>
> On Wed, Mar 6, 2024 at 6:30 AM Tomas Vondra
> wrote:
> >
> > Hi,
> >
> > I know very little about sql/json and all the json internals, but I
> > decided to do some black box testing. I built a large JSONB table
> > (single
On Tue, Mar 5, 2024 at 6:52 AM Amit Langote wrote:
Hi,
I am doing some random testing with the latest patch and found one scenario
that I wanted to share.
consider a below case.
‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{
"id" : 12345678901,
"FULL_NAME" : "JOHN
Hi Tomas,
On Wed, Mar 6, 2024 at 6:30 AM Tomas Vondra
wrote:
>
> Hi,
>
> I know very little about sql/json and all the json internals, but I
> decided to do some black box testing. I built a large JSONB table
> (single column, ~7GB of data after loading). And then I did a query
> transforming
Hi,
I know very little about sql/json and all the json internals, but I
decided to do some black box testing. I built a large JSONB table
(single column, ~7GB of data after loading). And then I did a query
transforming the data into tabular form using JSON_TABLE.
The JSON_TABLE query looks like
On Tue, Mar 5, 2024 at 9:22 AM Amit Langote wrote:
>
> Thanks for the heads up. Attaching rebased patches.
>
Walking through the v41-0001-Add-SQL-JSON-query-functions.patch documentation.
I found some minor cosmetic issues.
+
+select json_query(jsonb '{"a": "[1, 2]"}', 'lax $.a'
Hi,
> On Tue, Mar 5, 2024 at 12:03 AM Alvaro Herrera
> wrote:
>> On 2024-Mar-04, Erik Rijkers wrote:
>>
>> > In my hands (applying with patch), the patches, esp. 0001, do not apply.
>> > But I see the cfbot builds without problem so maybe just ignore these
>> > FAILED
>> > lines. Better get
On 2024-Mar-04, Erik Rijkers wrote:
> In my hands (applying with patch), the patches, esp. 0001, do not apply.
> But I see the cfbot builds without problem so maybe just ignore these FAILED
> lines. Better get them merged - so I can test there...
It's because of dbbca2cf299b. It should apply
Op 3/4/24 om 10:40 schreef Amit Langote:
Hi Jian,
Thanks for the reviews and sorry for the late reply. Replying to all
emails in one.
> [v40-0001-Add-SQL-JSON-query-functions.patch]
> [v40-0002-Show-function-name-in-TableFuncScan.patch]
> [v40-0003-JSON_TABLE.patch]
In my hands (applying
On Thu, Jan 25, 2024 at 10:39 PM jian he wrote:
>
> On Thu, Jan 25, 2024 at 7:54 PM Amit Langote wrote:
> >
> > >
> > > The problem with returning comp_domain_with_typmod from json_value()
> > > seems to be that it's using a text-to-record CoerceViaIO expression
> > > picked from
based on this query:
begin;
SET LOCAL TIME ZONE 10.5;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select JSON_QUERY(s, '$.timestamp_tz()')::text,'+10.5'::text,
'timestamp_tz'::text from cte
union all
select JSON_QUERY(s, '$.time()')::text,'+10.5'::text, 'time'::text from cte
union
Hi.
minor issues.
I am wondering do we need add `pg_node_attr(query_jumble_ignore)`
to some of our created structs in src/include/nodes/parsenodes.h in
v39-0001-Add-SQL-JSON-query-functions.patch
diff --git a/src/backend/parser/parse_jsontable.c
b/src/backend/parser/parse_jsontable.c
new file
On Thu, Jan 25, 2024 at 7:54 PM Amit Langote wrote:
>
> >
> > The problem with returning comp_domain_with_typmod from json_value()
> > seems to be that it's using a text-to-record CoerceViaIO expression
> > picked from JsonExpr.item_coercions, which behaves differently than
> > the expression
On Thu, Jan 25, 2024 at 6:09 PM Amit Langote wrote:
> On Wed, Jan 24, 2024 at 10:11 PM Amit Langote wrote:
> > I still need to take a look at your other report regarding typmod but
> > I'm out of energy today.
>
> The attached updated patch should address one of the concerns --
> JSON_QUERY()
On 9.16.4. JSON_TABLE
`
name type FORMAT JSON [ENCODING UTF8] [ PATH json_path_specification ]
Inserts a composite SQL/JSON item into the output row
`
i am not sure "Inserts a composite SQL/JSON item into the output row"
I think it means, for any type's typecategory is TYPCATEGORY_STRING,
if
On Mon, Jan 22, 2024 at 11:46 PM jian he wrote:
>
> On Mon, Jan 22, 2024 at 10:28 PM Amit Langote wrote:
> >
> > > based on v35.
> > > Now I only applied from 0001 to 0007.
> > > For {DEFAULT expression ON EMPTY} | {DEFAULT expression ON ERROR}
> > > restrict DEFAULT expression be either Const
On 2024-Jan-18, Alvaro Herrera wrote:
> > commands/explain.c (Hmm, I think this is a preexisting bug actually)
> >
> > 3893 18 : case T_TableFuncScan:
> > 3894 18 : Assert(rte->rtekind == RTE_TABLEFUNC);
> > 3895 18 : if
On Mon, Jan 22, 2024 at 10:28 PM Amit Langote wrote:
>
> > based on v35.
> > Now I only applied from 0001 to 0007.
> > For {DEFAULT expression ON EMPTY} | {DEFAULT expression ON ERROR}
> > restrict DEFAULT expression be either Const node or FuncExpr node.
> > so these 3 SQL/JSON functions can
On Mon, Nov 27, 2023 at 9:06 PM Alvaro Herrera wrote:
> At this point one thing that IMO we cannot afford to do, is stop feature
> progress work on the name of parser speed. I mean, parser speed is
> important, and we need to be mindful that what we add is reasonable.
> But at some point we'll
I found two main issues regarding cocece SQL/JSON function output to
other data types.
* returning typmod influence the returning result of JSON_VALUE | JSON_QUERY.
* JSON_VALUE | JSON_QUERY handles returning type domains allowing null
and not allowing null inconsistencies.
in
2024-01 Commitfest.
Hi, This patch has a CF status of "Needs Review" [1], but it seems
there were CFbot test failures last time it was run [2]. Please have a
look and post an updated version if necessary.
==
[1] https://commitfest.postgresql.org/46/4377/
[2]
based on v35.
Now I only applied from 0001 to 0007.
For {DEFAULT expression ON EMPTY} | {DEFAULT expression ON ERROR}
restrict DEFAULT expression be either Const node or FuncExpr node.
so these 3 SQL/JSON functions can be used in the btree expression index.
I made some big changes on the doc.
1 - 100 of 241 matches
Mail list logo
