Hi!
Amit, ok, I'll start a new thread with this patch after I deal with an
issue on
plan execution, I've found it during testing.
On Mon, Nov 11, 2024 at 3:29 AM Amit Langote
wrote:
> Hi Nikita,
>
> On Sat, Nov 9, 2024 at 5:22 PM Nikita Malakhov wrote:
> >
> > Hi!
> >
> > We'd like to help to
Hi Nikita,
On Sat, Nov 9, 2024 at 5:22 PM Nikita Malakhov wrote:
>
> Hi!
>
> We'd like to help to implement SQL/JSON in v18 and have adapted the
> JSON_TABLE PLAN clause code
> from patch v45-0001-JSON_TABLE.patch.
Nice, thanks.
I think it might be better to start a new thread for your patch a
Hi!
We'd like to help to implement SQL/JSON in v18 and have adapted the
JSON_TABLE PLAN clause code
from patch v45-0001-JSON_TABLE.patch.
Could you please review it? There are some places with questionable
behavior - please check the JSON_TABLE
plan execution section in tests, and I'm not sure ab
Hi Alexander,
On Fri, Jun 28, 2024 at 5:00 PM Alexander Lakhin wrote:
>
> Hi Amit,
>
> 28.06.2024 09:15, Amit Langote wrote:
> > Hi Alexander,
> >
> >
> > Thanks for the report. Yeah, those comments that got added in
> > 7081ac46ace are obsolete.
> >
>
> Thanks for paying attention to that!
>
>
Hi Amit,
28.06.2024 09:15, Amit Langote wrote:
Hi Alexander,
Thanks for the report. Yeah, those comments that got added in
7081ac46ace are obsolete.
Thanks for paying attention to that!
Could you also look at comments for transformJsonObjectAgg() and
transformJsonArrayAgg(), aren't they o
Hi Alexander,
On Wed, Jun 26, 2024 at 8:00 PM Alexander Lakhin wrote:
>
> Hello,
>
> I'm not sure I've chosen the most appropriate thread for reporting the
> issue, but maybe you would like to look at code comments related to
> SQL/JSON constructors:
>
> * Transform JSON_ARRAY() constructor.
>
Hello,
I'm not sure I've chosen the most appropriate thread for reporting the
issue, but maybe you would like to look at code comments related to
SQL/JSON constructors:
* Transform JSON_ARRAY() constructor.
*
* JSON_ARRAY() is transformed into json[b]_build_array[_ext]() call
* depending on
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:
> > > > > 0
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 to
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 tin
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 "errorOn
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 = makeNode(J
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 = (Js
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,
> > > JSON_TABL
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 depars
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 "char"
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 a
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
> >
> > trigge
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 rev
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 the
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 = 'tuplede
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 process
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 local
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 int
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 a
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, 0002
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 jsonb)
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
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
+++ b/src/backend/pa
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 al
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 imple
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 recur
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;
JsonTable
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
src/interfaces/ecpg/test/sql/s
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 fe
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 (no
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 c
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 js
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 unders
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, $5
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 b
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, ex
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" : {
"first":"Johnn
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
> > Ou
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 explai
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 no
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 public.xm
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 de
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 json
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 beh
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, but
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 [ PA
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;
+ jsexpr->returning->t
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 imp
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 q
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, an
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 num
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 a
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, Th
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 conversion,
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
+ * la
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 occu
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: SELECT
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 colu
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 DOE"}
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 the
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 t
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 cl
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 with
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 JsonExpr.item_co
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 mode
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 tree
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() sho
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 FORMAT
1 - 100 of 248 matches
Mail list logo
