Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb4fad46

Browse files
committed
SQL/JSON: Improve some error messages
This improves some error messages emitted by SQL/JSON query functionsby mentioning column name when available, such as when they areinvoked as part of evaluating JSON_TABLE() columns. To do so, a newfield column_name is added to both JsonFuncExpr and JsonExpr that isonly populated when creating those nodes for transformed JSON_TABLE()columns.While at it, relevant error messages are reworded for clarity.Reported-by: Jian He <jian.universality@gmail.com>Suggested-by: Jian He <jian.universality@gmail.com>Discussion:https://postgr.es/m/CACJufxG_e0QLCgaELrr2ZNz7AxPeGCNKAORe3fHtFCQLsH4J4Q@mail.gmail.com
1 parent40126ac commitb4fad46

File tree

10 files changed

+96
-57
lines changed

10 files changed

+96
-57
lines changed

‎src/backend/executor/execExprInterp.c

Lines changed: 24 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -4312,7 +4312,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
43124312
caseJSON_QUERY_OP:
43134313
*op->resvalue=JsonPathQuery(item,path,jsexpr->wrapper,&empty,
43144314
!throw_error ?&error :NULL,
4315-
jsestate->args);
4315+
jsestate->args,
4316+
jsexpr->column_name);
43164317

43174318
*op->resnull= (DatumGetPointer(*op->resvalue)==NULL);
43184319

@@ -4337,7 +4338,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
43374338
{
43384339
JsonbValue*jbv=JsonPathValue(item,path,&empty,
43394340
!throw_error ?&error :NULL,
4340-
jsestate->args);
4341+
jsestate->args,
4342+
jsexpr->column_name);
43414343

43424344
if (jbv==NULL)
43434345
{
@@ -4407,30 +4409,33 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
44074409
/* Handle ON EMPTY. */
44084410
if (empty)
44094411
{
4412+
*op->resvalue= (Datum)0;
4413+
*op->resnull= true;
44104414
if (jsexpr->on_empty)
44114415
{
4412-
if (jsexpr->on_empty->btype==JSON_BEHAVIOR_ERROR)
4413-
ereport(ERROR,
4414-
errcode(ERRCODE_NO_SQL_JSON_ITEM),
4415-
errmsg("no SQL/JSON item"));
4416-
else
4416+
if (jsexpr->on_empty->btype!=JSON_BEHAVIOR_ERROR)
4417+
{
44174418
jsestate->empty.value=BoolGetDatum(true);
4418-
4419-
Assert(jsestate->jump_empty >=0);
4420-
returnjsestate->jump_empty;
4419+
Assert(jsestate->jump_empty >=0);
4420+
returnjsestate->jump_empty;
4421+
}
4422+
}
4423+
elseif (jsexpr->on_error->btype!=JSON_BEHAVIOR_ERROR)
4424+
{
4425+
jsestate->error.value=BoolGetDatum(true);
4426+
Assert(!throw_error&&jsestate->jump_error >=0);
4427+
returnjsestate->jump_error;
44214428
}
4422-
elseif (jsexpr->on_error->btype==JSON_BEHAVIOR_ERROR)
4429+
4430+
if (jsexpr->column_name)
44234431
ereport(ERROR,
44244432
errcode(ERRCODE_NO_SQL_JSON_ITEM),
4425-
errmsg("no SQL/JSON item"));
4433+
errmsg("no SQL/JSON item found for specified path of column \"%s\"",
4434+
jsexpr->column_name));
44264435
else
4427-
jsestate->error.value=BoolGetDatum(true);
4428-
4429-
*op->resvalue= (Datum)0;
4430-
*op->resnull= true;
4431-
4432-
Assert(!throw_error&&jsestate->jump_error >=0);
4433-
returnjsestate->jump_error;
4436+
ereport(ERROR,
4437+
errcode(ERRCODE_NO_SQL_JSON_ITEM),
4438+
errmsg("no SQL/JSON item found for specified path"));
44344439
}
44354440

44364441
/*

‎src/backend/parser/parse_expr.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4311,6 +4311,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
43114311
jsexpr=makeNode(JsonExpr);
43124312
jsexpr->location=func->location;
43134313
jsexpr->op=func->op;
4314+
jsexpr->column_name=func->column_name;
43144315

43154316
/*
43164317
* jsonpath machinery can only handle jsonb documents, so coerce the input

‎src/backend/parser/parse_jsontable.c

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -402,19 +402,17 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
402402
Node*pathspec;
403403
JsonFuncExpr*jfexpr=makeNode(JsonFuncExpr);
404404

405-
/*
406-
* XXX consider inventing JSON_TABLE_VALUE_OP, etc. and pass the column
407-
* name via JsonExpr so that JsonPathValue(), etc. can provide error
408-
* message tailored to JSON_TABLE(), such as by mentioning the column
409-
* names in the message.
410-
*/
411405
if (jtc->coltype==JTC_REGULAR)
412406
jfexpr->op=JSON_VALUE_OP;
413407
elseif (jtc->coltype==JTC_EXISTS)
414408
jfexpr->op=JSON_EXISTS_OP;
415409
else
416410
jfexpr->op=JSON_QUERY_OP;
417411

412+
/* Pass the column name so any runtime JsonExpr errors can print it. */
413+
Assert(jtc->name!=NULL);
414+
jfexpr->column_name=pstrdup(jtc->name);
415+
418416
jfexpr->context_item=makeJsonValueExpr((Expr*)contextItemExpr,NULL,
419417
makeJsonFormat(JS_FORMAT_DEFAULT,
420418
JS_ENC_DEFAULT,

‎src/backend/utils/adt/jsonpath_exec.c

Lines changed: 33 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -3899,7 +3899,8 @@ JsonPathExists(Datum jb, JsonPath *jp, bool *error, List *vars)
38993899
*/
39003900
Datum
39013901
JsonPathQuery(Datumjb,JsonPath*jp,JsonWrapperwrapper,bool*empty,
3902-
bool*error,List*vars)
3902+
bool*error,List*vars,
3903+
constchar*column_name)
39033904
{
39043905
JsonbValue*singleton;
39053906
boolwrap;
@@ -3950,10 +3951,17 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
39503951
return (Datum)0;
39513952
}
39523953

3953-
ereport(ERROR,
3954-
(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
3955-
errmsg("JSON path expression in JSON_QUERY should return singleton item without wrapper"),
3956-
errhint("Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.")));
3954+
if (column_name)
3955+
ereport(ERROR,
3956+
(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
3957+
errmsg("JSON path expression for column \"%s\" should return single item without wrapper",
3958+
column_name),
3959+
errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
3960+
else
3961+
ereport(ERROR,
3962+
(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
3963+
errmsg("JSON path expression in JSON_QUERY should return single item without wrapper"),
3964+
errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
39573965
}
39583966

39593967
if (singleton)
@@ -3970,7 +3978,8 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
39703978
* *error to true. *empty is set to true if no match is found.
39713979
*/
39723980
JsonbValue*
3973-
JsonPathValue(Datumjb,JsonPath*jp,bool*empty,bool*error,List*vars)
3981+
JsonPathValue(Datumjb,JsonPath*jp,bool*empty,bool*error,List*vars,
3982+
constchar*column_name)
39743983
{
39753984
JsonbValue*res;
39763985
JsonValueListfound= {0};
@@ -4006,9 +4015,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
40064015
returnNULL;
40074016
}
40084017

4009-
ereport(ERROR,
4010-
(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
4011-
errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
4018+
if (column_name)
4019+
ereport(ERROR,
4020+
(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
4021+
errmsg("JSON path expression for column \"%s\" should return single scalar item",
4022+
column_name)));
4023+
else
4024+
ereport(ERROR,
4025+
(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
4026+
errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
40124027
}
40134028

40144029
res=JsonValueListHead(&found);
@@ -4024,9 +4039,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
40244039
returnNULL;
40254040
}
40264041

4027-
ereport(ERROR,
4028-
(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
4029-
errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
4042+
if (column_name)
4043+
ereport(ERROR,
4044+
(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
4045+
errmsg("JSON path expression for column \"%s\" should return single scalar item",
4046+
column_name)));
4047+
else
4048+
ereport(ERROR,
4049+
(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
4050+
errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
40304051
}
40314052

40324053
if (res->type==jbvNull)

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1791,6 +1791,8 @@ typedef struct JsonFuncExpr
17911791
{
17921792
NodeTagtype;
17931793
JsonExprOpop;/* expression type */
1794+
char*column_name;/* JSON_TABLE() column name or NULL if this is
1795+
* not for a JSON_TABLE() */
17941796
JsonValueExpr*context_item;/* context item expression */
17951797
Node*pathspec;/* JSON path specification expression */
17961798
List*passing;/* list of PASSING clause arguments, if any */

‎src/include/nodes/primnodes.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1782,6 +1782,9 @@ typedef struct JsonExpr
17821782

17831783
JsonExprOpop;
17841784

1785+
char*column_name;/* JSON_TABLE() column name or NULL if this is
1786+
* not for a JSON_TABLE() */
1787+
17851788
/* jsonb-valued expression to query */
17861789
Node*formatted_expr;
17871790

‎src/include/utils/jsonpath.h

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -300,9 +300,11 @@ typedef struct JsonPathVariable
300300
/* SQL/JSON item */
301301
externboolJsonPathExists(Datumjb,JsonPath*path,bool*error,List*vars);
302302
externDatumJsonPathQuery(Datumjb,JsonPath*jp,JsonWrapperwrapper,
303-
bool*empty,bool*error,List*vars);
303+
bool*empty,bool*error,List*vars,
304+
constchar*column_name);
304305
externJsonbValue*JsonPathValue(Datumjb,JsonPath*jp,bool*empty,
305-
bool*error,List*vars);
306+
bool*error,List*vars,
307+
constchar*column_name);
306308

307309
externPGDLLIMPORTconstTableFuncRoutineJsonbTableRoutine;
308310

‎src/test/regress/expected/sqljson_jsontable.out

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -492,11 +492,11 @@ FROM
492492
ON true;
493493
ERROR: invalid input syntax for type integer: "err"
494494
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
495-
ERROR: no SQL/JSON item
495+
ERROR: no SQL/JSON item found for specified path of column "a"
496496
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
497497
ERROR: jsonpath member accessor can only be applied to an object
498498
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
499-
ERROR: no SQL/JSON item
499+
ERROR: no SQL/JSON item found for specified path of column "a"
500500
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
501501
a
502502
---
@@ -637,6 +637,10 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
637637
ERROR: only string constants are supported in JSON_TABLE path specification
638638
LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
639639
^
640+
-- JsonPathQuery() error message mentioning column name
641+
SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
642+
ERROR: JSON path expression for column "b" should return single item without wrapper
643+
HINT: Use WITH WRAPPER clause to wrap SQL/JSON items into array.
640644
-- JSON_TABLE: nested paths
641645
-- Duplicate path names
642646
SELECT * FROM JSON_TABLE(
@@ -849,7 +853,7 @@ SELECT sub.* FROM s,
849853
xx int path '$.c',
850854
NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
851855
)) sub;
852-
ERROR: no SQL/JSON item
856+
ERROR: no SQL/JSON item found for specified path of column "z21"
853857
-- Parent columns xx1, xx appear before NESTED ones
854858
SELECT sub.* FROM s,
855859
(VALUES (23)) x(x), generate_series(13, 13) y,

‎src/test/regress/expected/sqljson_queryfuncs.out

Lines changed: 15 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -339,15 +339,15 @@ SELECT JSON_VALUE(jsonb '[]', '$');
339339
(1 row)
340340

341341
SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
342-
ERROR: JSON path expression in JSON_VALUE should returnsingleton scalar item
342+
ERROR: JSON path expression in JSON_VALUE should returnsingle scalar item
343343
SELECT JSON_VALUE(jsonb '{}', '$');
344344
json_value
345345
------------
346346

347347
(1 row)
348348

349349
SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
350-
ERROR: JSON path expression in JSON_VALUE should returnsingleton scalar item
350+
ERROR: JSON path expression in JSON_VALUE should returnsingle scalar item
351351
SELECT JSON_VALUE(jsonb '1', '$.a');
352352
json_value
353353
------------
@@ -363,9 +363,9 @@ SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
363363
(1 row)
364364

365365
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
366-
ERROR: no SQL/JSON item
366+
ERROR: no SQL/JSON item found for specified path
367367
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
368-
ERROR: no SQL/JSON item
368+
ERROR: no SQL/JSON item found for specified path
369369
SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
370370
json_value
371371
------------
@@ -397,9 +397,9 @@ SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR
397397
(1 row)
398398

399399
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
400-
ERROR: no SQL/JSON item
400+
ERROR: no SQL/JSON item found for specified path
401401
SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
402-
ERROR: JSON path expression in JSON_VALUE should returnsingleton scalar item
402+
ERROR: JSON path expression in JSON_VALUE should returnsingle scalar item
403403
SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
404404
json_value
405405
------------
@@ -758,26 +758,26 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
758758
(1 row)
759759

760760
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
761-
ERROR: no SQL/JSON item
761+
ERROR: no SQL/JSON item found for specified path
762762
SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
763763
json_query
764764
------------
765765
"empty"
766766
(1 row)
767767

768768
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
769-
ERROR: no SQL/JSON item
769+
ERROR: no SQL/JSON item found for specified path
770770
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
771-
ERROR: no SQL/JSON item
771+
ERROR: no SQL/JSON item found for specified path
772772
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
773-
ERROR: no SQL/JSON item
773+
ERROR: no SQL/JSON item found for specified path
774774
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
775-
ERROR: no SQL/JSON item
775+
ERROR: no SQL/JSON item found for specified path
776776
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
777-
ERROR: no SQL/JSON item
777+
ERROR: no SQL/JSON item found for specified path
778778
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
779-
ERROR: JSON path expression in JSON_QUERY should returnsingleton item without wrapper
780-
HINT: Use WITH WRAPPER clause to wrap SQL/JSONitem sequence into array.
779+
ERROR: JSON path expression in JSON_QUERY should returnsingle item without wrapper
780+
HINT: Use WITH WRAPPER clause to wrap SQL/JSONitems into array.
781781
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
782782
json_query
783783
------------
@@ -1033,7 +1033,7 @@ SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
10331033
(1 row)
10341034

10351035
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
1036-
ERROR: no SQL/JSON item
1036+
ERROR: no SQL/JSON item found for specified path
10371037
-- Test timestamptz passing and output
10381038
SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
10391039
json_query

‎src/test/regress/sql/sqljson_jsontable.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -290,6 +290,9 @@ FROM JSON_TABLE(
290290
-- Should fail (not supported)
291291
SELECT*FROM JSON_TABLE(jsonb'{"a": 123}','$'||'.'||'a' COLUMNS (fooint));
292292

293+
-- JsonPathQuery() error message mentioning column name
294+
SELECT*FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}','$' COLUMNS (b jsonpath'$.a[*].b' ERRORON ERROR));
295+
293296
-- JSON_TABLE: nested paths
294297

295298
-- Duplicate path names

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp