Skip to content

Commit b4fad46

Browse files
amitlanamitlan
committed
SQL/JSON: Improve some error messages
This improves some error messages emitted by SQL/JSON query functions by mentioning column name when available, such as when they are invoked as part of evaluating JSON_TABLE() columns. To do so, a new field column_name is added to both JsonFuncExpr and JsonExpr that is only 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 parent 40126ac commit b4fad46

File tree

10 files changed

+96
-57
lines changed

10 files changed

+96
-57
lines changed

‎src/backend/executor/execExprInterp.c

+24-19
Original file line numberDiff line numberDiff line change
@@ -4312,7 +4312,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
43124312
case JSON_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-
return jsestate->jump_empty;
4419+
Assert(jsestate->jump_empty >= 0);
4420+
return jsestate->jump_empty;
4421+
}
4422+
}
4423+
else if (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
4424+
{
4425+
jsestate->error.value = BoolGetDatum(true);
4426+
Assert(!throw_error && jsestate->jump_error >= 0);
4427+
return jsestate->jump_error;
44214428
}
4422-
else if (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-
return jsestate->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

+1
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

+4-6
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
else if (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

+33-12
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(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
3902-
bool *error, List *vars)
3902+
bool *error, List *vars,
3903+
const char *column_name)
39033904
{
39043905
JsonbValue *singleton;
39053906
bool wrap;
@@ -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(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
3981+
JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars,
3982+
const char *column_name)
39743983
{
39753984
JsonbValue *res;
39763985
JsonValueList found = {0};
@@ -4006,9 +4015,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
40064015
return NULL;
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
return NULL;
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

+2
Original file line numberDiff line numberDiff line change
@@ -1791,6 +1791,8 @@ typedef struct JsonFuncExpr
17911791
{
17921792
NodeTag type;
17931793
JsonExprOp op; /* 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

+3
Original file line numberDiff line numberDiff line change
@@ -1782,6 +1782,9 @@ typedef struct JsonExpr
17821782

17831783
JsonExprOp op;
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

+4-2
Original file line numberDiff line numberDiff line change
@@ -300,9 +300,11 @@ typedef struct JsonPathVariable
300300
/* SQL/JSON item */
301301
extern bool JsonPathExists(Datum jb, JsonPath *path, bool *error, List *vars);
302302
extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
303-
bool *empty, bool *error, List *vars);
303+
bool *empty, bool *error, List *vars,
304+
const char *column_name);
304305
extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
305-
bool *error, List *vars);
306+
bool *error, List *vars,
307+
const char *column_name);
306308

307309
extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
308310

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

+7-3
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

+15-15
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 return singleton scalar item
342+
ERROR: JSON path expression in JSON_VALUE should return single 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 return singleton scalar item
350+
ERROR: JSON path expression in JSON_VALUE should return single 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 return singleton scalar item
402+
ERROR: JSON path expression in JSON_VALUE should return single 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 return singleton item without wrapper
780-
HINT: Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.
779+
ERROR: JSON path expression in JSON_QUERY should return single item without wrapper
780+
HINT: Use WITH WRAPPER clause to wrap SQL/JSON items 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

+3
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 (foo int));
292292

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

295298
-- Duplicate path names

0 commit comments

Comments
 (0)