Re: BUG #11207: empty path will segfault jsonb #>
От | Tom Lane |
---|---|
Тема | Re: BUG #11207: empty path will segfault jsonb #> |
Дата | |
Msg-id | 25391.1408577416@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: BUG #11207: empty path will segfault jsonb #> (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: BUG #11207: empty path will segfault jsonb #>
(Peter Geoghegan <pg@heroku.com>)
Re: BUG #11207: empty path will segfault jsonb #> (Andrew Dunstan <andrew@dunslane.net>) |
Список | pgsql-bugs |
I wrote: > I think returning NULL is the right thing here, really. Aside from being > arguably more convenient for indexing, we will get less push-back if we > make some operators go from throwing errors to returning null than if > we make some other operators go the other way. Attached is a proposed patch (against HEAD and 9.4 git tip) that has these effects: 1. #> now returns the input object if the RHS array is empty; 2. Error cases in -> and #> are all removed in favor of returning NULL. The regression test changes show that we were really pretty inconsistent about whether to throw error or return NULL for cases where the JSON tree structure didn't match the extraction request. I think having a uniform rule is a definite improvement. regards, tom lane diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 13c71af..57ab8ad 100644 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** table2-mapping *** 10152,10161 **** <note> <para> There are parallel variants of these operators for both the ! <type>json</type> and <type>jsonb</type> types. The operators return the same type as their left-hand input (either <type>json</type> or <type>jsonb</type>), except for those specified as returning <type>text</>, which coerce the value to text. </para> </note> <para> --- 10152,10165 ---- <note> <para> There are parallel variants of these operators for both the ! <type>json</type> and <type>jsonb</type> types. ! The field/element extraction operators return the same type as their left-hand input (either <type>json</type> or <type>jsonb</type>), except for those specified as returning <type>text</>, which coerce the value to text. + The field/element extraction operators return NULL, rather than + failing, if the JSON input does not have the right structure to match + the request; for example if no such element exists. </para> </note> <para> diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index a56942a..37dd611 100644 *** a/doc/src/sgml/json.sgml --- b/doc/src/sgml/json.sgml *************** SELECT jdoc->'guid', jdoc->'name' *** 415,423 **** the <literal>"tags"</> key is common, defining an index like this may be worthwhile: <programlisting> - -- Note that the "jsonb -> text" operator can only be called on a JSON - -- object, so as a consequence of creating this index the root of each - -- "jdoc" value must be an object. This is enforced during insertion. CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags')); </programlisting> Now, the <literal>WHERE</> clause <literal>jdoc -> 'tags' ? 'qui'</> --- 415,420 ---- diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 5fabef0..1ba4f9a 100644 *** a/src/backend/utils/adt/jsonfuncs.c --- b/src/backend/utils/adt/jsonfuncs.c *************** static void okeys_array_start(void *stat *** 39,45 **** static void okeys_scalar(void *state, char *token, JsonTokenType tokentype); /* semantic action functions for json_get* functions */ - static void get_object_start(void *state); static void get_object_field_start(void *state, char *fname, bool isnull); static void get_object_field_end(void *state, char *fname, bool isnull); static void get_array_start(void *state); --- 39,44 ---- *************** static void get_array_element_end(void * *** 48,60 **** static void get_scalar(void *state, char *token, JsonTokenType tokentype); /* common worker function for json getter functions */ ! static Datum get_path_all(FunctionCallInfo fcinfo, const char *funcname, ! bool as_text); static text *get_worker(text *json, char *field, int elem_index, char **tpath, int *ipath, int npath, bool normalize_results); ! static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, const char *funcname, ! bool as_text); /* semantic action functions for json_array_length */ static void alen_object_start(void *state); --- 47,57 ---- static void get_scalar(void *state, char *token, JsonTokenType tokentype); /* common worker function for json getter functions */ ! static Datum get_path_all(FunctionCallInfo fcinfo, bool as_text); static text *get_worker(text *json, char *field, int elem_index, char **tpath, int *ipath, int npath, bool normalize_results); ! static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text); /* semantic action functions for json_array_length */ static void alen_object_start(void *state); *************** Datum *** 455,461 **** json_object_field(PG_FUNCTION_ARGS) { text *json = PG_GETARG_TEXT_P(0); ! text *fname = PG_GETARG_TEXT_P(1); char *fnamestr = text_to_cstring(fname); text *result; --- 452,458 ---- json_object_field(PG_FUNCTION_ARGS) { text *json = PG_GETARG_TEXT_P(0); ! text *fname = PG_GETARG_TEXT_PP(1); char *fnamestr = text_to_cstring(fname); text *result; *************** jsonb_object_field(PG_FUNCTION_ARGS) *** 474,494 **** text *key = PG_GETARG_TEXT_PP(1); JsonbValue *v; ! if (JB_ROOT_IS_SCALAR(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on a scalar", ! "jsonb_object_field (jsonb -> text)"))); ! else if (JB_ROOT_IS_ARRAY(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on an array", ! "jsonb_object_field (jsonb -> text)"))); ! ! Assert(JB_ROOT_IS_OBJECT(jb)); v = findJsonbValueFromContainerLen(&jb->root, JB_FOBJECT, ! VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key)); if (v != NULL) PG_RETURN_JSONB(JsonbValueToJsonb(v)); --- 471,482 ---- text *key = PG_GETARG_TEXT_PP(1); JsonbValue *v; ! if (!JB_ROOT_IS_OBJECT(jb)) ! PG_RETURN_NULL(); v = findJsonbValueFromContainerLen(&jb->root, JB_FOBJECT, ! VARDATA_ANY(key), ! VARSIZE_ANY_EXHDR(key)); if (v != NULL) PG_RETURN_JSONB(JsonbValueToJsonb(v)); *************** Datum *** 500,506 **** json_object_field_text(PG_FUNCTION_ARGS) { text *json = PG_GETARG_TEXT_P(0); ! text *fname = PG_GETARG_TEXT_P(1); char *fnamestr = text_to_cstring(fname); text *result; --- 488,494 ---- json_object_field_text(PG_FUNCTION_ARGS) { text *json = PG_GETARG_TEXT_P(0); ! text *fname = PG_GETARG_TEXT_PP(1); char *fnamestr = text_to_cstring(fname); text *result; *************** jsonb_object_field_text(PG_FUNCTION_ARGS *** 519,539 **** text *key = PG_GETARG_TEXT_PP(1); JsonbValue *v; ! if (JB_ROOT_IS_SCALAR(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on a scalar", ! "jsonb_object_field_text (jsonb ->> text)"))); ! else if (JB_ROOT_IS_ARRAY(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on an array", ! "jsonb_object_field_text (jsonb ->> text)"))); ! ! Assert(JB_ROOT_IS_OBJECT(jb)); v = findJsonbValueFromContainerLen(&jb->root, JB_FOBJECT, ! VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key)); if (v != NULL) { --- 507,518 ---- text *key = PG_GETARG_TEXT_PP(1); JsonbValue *v; ! if (!JB_ROOT_IS_OBJECT(jb)) ! PG_RETURN_NULL(); v = findJsonbValueFromContainerLen(&jb->root, JB_FOBJECT, ! VARDATA_ANY(key), ! VARSIZE_ANY_EXHDR(key)); if (v != NULL) { *************** jsonb_array_element(PG_FUNCTION_ARGS) *** 594,611 **** int element = PG_GETARG_INT32(1); JsonbValue *v; ! if (JB_ROOT_IS_SCALAR(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on a scalar", ! "jsonb_array_element (jsonb -> int)"))); ! else if (JB_ROOT_IS_OBJECT(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on an object", ! "jsonb_array_element (jsonb -> int)"))); ! ! Assert(JB_ROOT_IS_ARRAY(jb)); v = getIthJsonbValueFromContainer(&jb->root, element); if (v != NULL) --- 573,580 ---- int element = PG_GETARG_INT32(1); JsonbValue *v; ! if (!JB_ROOT_IS_ARRAY(jb)) ! PG_RETURN_NULL(); v = getIthJsonbValueFromContainer(&jb->root, element); if (v != NULL) *************** jsonb_array_element_text(PG_FUNCTION_ARG *** 636,653 **** int element = PG_GETARG_INT32(1); JsonbValue *v; ! if (JB_ROOT_IS_SCALAR(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on a scalar", ! "jsonb_array_element_text"))); ! else if (JB_ROOT_IS_OBJECT(jb)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on an object", ! "jsonb_array_element_text"))); ! ! Assert(JB_ROOT_IS_ARRAY(jb)); v = getIthJsonbValueFromContainer(&jb->root, element); if (v != NULL) --- 605,612 ---- int element = PG_GETARG_INT32(1); JsonbValue *v; ! if (!JB_ROOT_IS_ARRAY(jb)) ! PG_RETURN_NULL(); v = getIthJsonbValueFromContainer(&jb->root, element); if (v != NULL) *************** jsonb_array_element_text(PG_FUNCTION_ARG *** 690,709 **** Datum json_extract_path(PG_FUNCTION_ARGS) { ! return get_path_all(fcinfo, "json_extract_path", false); } Datum json_extract_path_text(PG_FUNCTION_ARGS) { ! return get_path_all(fcinfo, "json_extract_path_text", true); } /* * common routine for extract_path functions */ static Datum ! get_path_all(FunctionCallInfo fcinfo, const char *funcname, bool as_text) { text *json = PG_GETARG_TEXT_P(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); --- 649,668 ---- Datum json_extract_path(PG_FUNCTION_ARGS) { ! return get_path_all(fcinfo, false); } Datum json_extract_path_text(PG_FUNCTION_ARGS) { ! return get_path_all(fcinfo, true); } /* * common routine for extract_path functions */ static Datum ! get_path_all(FunctionCallInfo fcinfo, bool as_text) { text *json = PG_GETARG_TEXT_P(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); *************** get_path_all(FunctionCallInfo fcinfo, co *** 714,758 **** char **tpath; int *ipath; int i; - long ind; - char *endptr; if (array_contains_nulls(path)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s with null path elements", ! funcname))); deconstruct_array(path, TEXTOID, -1, false, 'i', &pathtext, &pathnulls, &npath); /* ! * If the array is empty, return NULL; this is dubious but it's what 9.3 ! * did. */ if (npath <= 0) ! PG_RETURN_NULL(); tpath = palloc(npath * sizeof(char *)); ipath = palloc(npath * sizeof(int)); for (i = 0; i < npath; i++) { tpath[i] = TextDatumGetCString(pathtext[i]); - if (*tpath[i] == '\0') - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot call %s with empty path elements", - funcname))); /* * we have no idea at this stage what structure the document is so * just convert anything in the path that we can to an integer and set * all the other integers to -1 which will never match. */ ! ind = strtol(tpath[i], &endptr, 10); ! if (*endptr == '\0' && ind <= INT_MAX && ind >= 0) ! ipath[i] = (int) ind; else ipath[i] = -1; } --- 673,723 ---- char **tpath; int *ipath; int i; + /* + * If the array contains any null elements, return NULL, on the grounds + * that you'd have gotten NULL if any RHS value were NULL in a nested + * series of applications of the -> operator. (Note: because we also + * return NULL for error cases such as no-such-field, this is true + * regardless of the contents of the rest of the array.) + */ if (array_contains_nulls(path)) ! PG_RETURN_NULL(); deconstruct_array(path, TEXTOID, -1, false, 'i', &pathtext, &pathnulls, &npath); /* ! * If the array is empty, return the entire LHS object, on the grounds ! * that we should do zero field or element extractions. */ if (npath <= 0) ! PG_RETURN_TEXT_P(json); tpath = palloc(npath * sizeof(char *)); ipath = palloc(npath * sizeof(int)); for (i = 0; i < npath; i++) { + Assert(!pathnulls[i]); tpath[i] = TextDatumGetCString(pathtext[i]); /* * we have no idea at this stage what structure the document is so * just convert anything in the path that we can to an integer and set * all the other integers to -1 which will never match. */ ! if (*tpath[i] != '\0') ! { ! long ind; ! char *endptr; ! ! ind = strtol(tpath[i], &endptr, 10); ! if (*endptr == '\0' && ind <= INT_MAX && ind >= 0) ! ipath[i] = (int) ind; ! else ! ipath[i] = -1; ! } else ipath[i] = -1; } *************** get_worker(text *json, *** 826,832 **** * Not all variants need all the semantic routines. Only set the ones that * are actually needed for maximum efficiency. */ - sem->object_start = get_object_start; sem->array_start = get_array_start; sem->scalar = get_scalar; if (field != NULL || tpath != NULL) --- 791,796 ---- *************** get_worker(text *json, *** 846,863 **** } static void - get_object_start(void *state) - { - GetState *_state = (GetState *) state; - - /* json structure check */ - if (_state->lex->lex_level == 0 && _state->search_type == JSON_SEARCH_ARRAY) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot extract array element from a non-array"))); - } - - static void get_object_field_start(void *state, char *fname, bool isnull) { GetState *_state = (GetState *) state; --- 810,815 ---- *************** get_object_field_start(void *state, char *** 873,894 **** } else if (_state->search_type == JSON_SEARCH_PATH && lex_level <= _state->npath && ! _state->pathok[_state->lex->lex_level - 1] && strcmp(fname, _state->path[lex_level - 1]) == 0) { ! /* path search, path so far is ok, and we have a match */ /* this object overrides any previous matching object */ _state->tresult = NULL; _state->result_start = NULL; - /* if not at end of path just mark path ok */ if (lex_level < _state->npath) _state->pathok[lex_level] = true; ! ! /* end of path, so we want this value */ ! if (lex_level == _state->npath) get_next = true; } if (get_next) --- 825,849 ---- } else if (_state->search_type == JSON_SEARCH_PATH && lex_level <= _state->npath && ! _state->pathok[lex_level - 1] && strcmp(fname, _state->path[lex_level - 1]) == 0) { ! /* path search, path so far is ok, and we have a match */ /* this object overrides any previous matching object */ _state->tresult = NULL; _state->result_start = NULL; if (lex_level < _state->npath) + { + /* if not at end of path just mark path ok */ _state->pathok[lex_level] = true; ! } ! else ! { ! /* end of path, so we want this value */ get_next = true; + } } if (get_next) *************** get_object_field_end(void *state, char * *** 925,939 **** _state->pathok[lex_level - 1] && strcmp(fname, _state->path[lex_level - 1]) == 0) { - /* done with this field so reset pathok */ if (lex_level < _state->npath) _state->pathok[lex_level] = false; ! ! if (lex_level == _state->npath) get_last = true; } ! /* for as_test variants our work is already done */ if (get_last && _state->result_start != NULL) { /* --- 880,898 ---- _state->pathok[lex_level - 1] && strcmp(fname, _state->path[lex_level - 1]) == 0) { if (lex_level < _state->npath) + { + /* done with this field so reset pathok */ _state->pathok[lex_level] = false; ! } ! else ! { ! /* end of path, so we want this value */ get_last = true; + } } ! /* for as_text variants our work is already done */ if (get_last && _state->result_start != NULL) { /* *************** get_array_start(void *state) *** 962,973 **** GetState *_state = (GetState *) state; int lex_level = _state->lex->lex_level; - /* json structure check */ - if (lex_level == 0 && _state->search_type == JSON_SEARCH_OBJECT) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot extract field from a non-object"))); - /* * initialize array count for this nesting level. Note: the lex_level seen * by array_start is one less than that seen by the elements of the array. --- 921,926 ---- *************** get_array_element_start(void *state, boo *** 1006,1020 **** if (++_state->array_level_index[lex_level - 1] == _state->path_level_index[lex_level - 1]) { ! if (lex_level == _state->npath) { ! /* match and at end of path, so get value */ ! get_next = true; } else { ! /* not at end of path just mark path ok */ ! _state->pathok[lex_level] = true; } } } --- 959,973 ---- if (++_state->array_level_index[lex_level - 1] == _state->path_level_index[lex_level - 1]) { ! if (lex_level < _state->npath) { ! /* not at end of path just mark path ok */ ! _state->pathok[lex_level] = true; } else { ! /* match and at end of path, so get value */ ! get_next = true; } } } *************** get_array_element_end(void *state, bool *** 1042,1048 **** int lex_level = _state->lex->lex_level; /* same logic as in get_object_end, modified for arrays */ - if (lex_level == 1 && _state->search_type == JSON_SEARCH_ARRAY && _state->array_index == _state->search_index) { --- 995,1000 ---- *************** get_array_element_end(void *state, bool *** 1054,1066 **** _state->array_level_index[lex_level - 1] == _state->path_level_index[lex_level - 1]) { - /* done with this element so reset pathok */ if (lex_level < _state->npath) _state->pathok[lex_level] = false; ! ! if (lex_level == _state->npath) get_last = true; } if (get_last && _state->result_start != NULL) { int len = _state->lex->prev_token_terminator - _state->result_start; --- 1006,1021 ---- _state->array_level_index[lex_level - 1] == _state->path_level_index[lex_level - 1]) { if (lex_level < _state->npath) + { + /* done with this element so reset pathok */ _state->pathok[lex_level] = false; ! } ! else get_last = true; } + + /* same logic as for objects */ if (get_last && _state->result_start != NULL) { int len = _state->lex->prev_token_terminator - _state->result_start; *************** get_scalar(void *state, char *token, Jso *** 1077,1086 **** { GetState *_state = (GetState *) state; - if (_state->lex->lex_level == 0 && _state->search_type != JSON_SEARCH_PATH) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot extract element from a scalar"))); if (_state->next_scalar) { /* a de-escaped text value is wanted, so supply it */ --- 1032,1037 ---- *************** get_scalar(void *state, char *token, Jso *** 1093,1109 **** Datum jsonb_extract_path(PG_FUNCTION_ARGS) { ! return get_jsonb_path_all(fcinfo, "jsonb_extract_path", false); } Datum jsonb_extract_path_text(PG_FUNCTION_ARGS) { ! return get_jsonb_path_all(fcinfo, "jsonb_extract_path_text", true); } static Datum ! get_jsonb_path_all(FunctionCallInfo fcinfo, const char *funcname, bool as_text) { Jsonb *jb = PG_GETARG_JSONB(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); --- 1044,1060 ---- Datum jsonb_extract_path(PG_FUNCTION_ARGS) { ! return get_jsonb_path_all(fcinfo, false); } Datum jsonb_extract_path_text(PG_FUNCTION_ARGS) { ! return get_jsonb_path_all(fcinfo, true); } static Datum ! get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) { Jsonb *jb = PG_GETARG_JSONB(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); *************** get_jsonb_path_all(FunctionCallInfo fcin *** 1118,1138 **** JsonbValue tv; JsonbContainer *container; if (array_contains_nulls(path)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s with null path elements", ! funcname))); deconstruct_array(path, TEXTOID, -1, false, 'i', &pathtext, &pathnulls, &npath); /* ! * If the array is empty, return NULL; this is dubious but it's what 9.3 ! * did. */ if (npath <= 0) ! PG_RETURN_NULL(); if (JB_ROOT_IS_OBJECT(jb)) have_object = true; --- 1069,1105 ---- JsonbValue tv; JsonbContainer *container; + /* + * If the array contains any null elements, return NULL, on the grounds + * that you'd have gotten NULL if any RHS value were NULL in a nested + * series of applications of the -> operator. (Note: because we also + * return NULL for error cases such as no-such-field, this is true + * regardless of the contents of the rest of the array.) + */ if (array_contains_nulls(path)) ! PG_RETURN_NULL(); deconstruct_array(path, TEXTOID, -1, false, 'i', &pathtext, &pathnulls, &npath); /* ! * If the array is empty, return the entire LHS object, on the grounds ! * that we should do zero field or element extractions. */ if (npath <= 0) ! { ! if (as_text) ! { ! PG_RETURN_TEXT_P(cstring_to_text(JsonbToCString(NULL, ! &jb->root, ! VARSIZE(jb)))); ! } ! else ! { ! /* not text mode - just hand back the jsonb */ ! PG_RETURN_JSONB(jb); ! } ! } if (JB_ROOT_IS_OBJECT(jb)) have_object = true; *************** get_jsonb_path_all(FunctionCallInfo fcin *** 1158,1174 **** char *endptr; lindex = strtol(indextext, &endptr, 10); ! if (*endptr != '\0' || lindex > INT_MAX || lindex < 0) PG_RETURN_NULL(); index = (uint32) lindex; jbvp = getIthJsonbValueFromContainer(container, index); } else { ! if (i == 0) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot extract path from a scalar"))); PG_RETURN_NULL(); } --- 1125,1139 ---- char *endptr; lindex = strtol(indextext, &endptr, 10); ! if (endptr == indextext || *endptr != '\0' || ! lindex > INT_MAX || lindex < 0) PG_RETURN_NULL(); index = (uint32) lindex; jbvp = getIthJsonbValueFromContainer(container, index); } else { ! /* scalar, extraction yields a null */ PG_RETURN_NULL(); } *************** get_jsonb_path_all(FunctionCallInfo fcin *** 1196,1204 **** if (as_text) { if (jbvp->type == jbvString) ! PG_RETURN_TEXT_P(cstring_to_text_with_len(jbvp->val.string.val, jbvp->val.string.len)); ! else if (jbvp->type == jbvNull) PG_RETURN_NULL(); } --- 1161,1171 ---- if (as_text) { + /* special-case outputs for string and null values */ if (jbvp->type == jbvString) ! PG_RETURN_TEXT_P(cstring_to_text_with_len(jbvp->val.string.val, ! jbvp->val.string.len)); ! if (jbvp->type == jbvNull) PG_RETURN_NULL(); } diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index b438e49..501b23b 100644 *** a/src/test/regress/expected/json.out --- b/src/test/regress/expected/json.out *************** INSERT INTO test_json VALUES *** 506,516 **** SELECT test_json -> 'x' FROM test_json WHERE json_type = 'scalar'; ! ERROR: cannot extract element from a scalar SELECT test_json -> 'x' FROM test_json WHERE json_type = 'array'; ! ERROR: cannot extract field from a non-object SELECT test_json -> 'x' FROM test_json WHERE json_type = 'object'; --- 506,524 ---- SELECT test_json -> 'x' FROM test_json WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_json WHERE json_type = 'array'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_json WHERE json_type = 'object'; *************** WHERE json_type = 'object'; *** 538,544 **** SELECT test_json -> 2 FROM test_json WHERE json_type = 'scalar'; ! ERROR: cannot extract element from a scalar SELECT test_json -> 2 FROM test_json WHERE json_type = 'array'; --- 546,556 ---- SELECT test_json -> 2 FROM test_json WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 2 FROM test_json WHERE json_type = 'array'; *************** WHERE json_type = 'array'; *** 550,556 **** SELECT test_json -> 2 FROM test_json WHERE json_type = 'object'; ! ERROR: cannot extract array element from a non-array SELECT test_json->>2 FROM test_json WHERE json_type = 'array'; --- 562,572 ---- SELECT test_json -> 2 FROM test_json WHERE json_type = 'object'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json->>2 FROM test_json WHERE json_type = 'array'; *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 667,673 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1; ! ERROR: cannot extract array element from a non-array select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z'; ?column? ---------- --- 683,693 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 693,703 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z'; ! ERROR: cannot extract field from a non-object select '"foo"'::json -> 1; ! ERROR: cannot extract element from a scalar select '"foo"'::json -> 'z'; ! ERROR: cannot extract element from a scalar select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::text; ?column? ---------- --- 713,735 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::text; ?column? ---------- *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 711,717 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 1; ! ERROR: cannot extract array element from a non-array select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 'z'; ?column? ---------- --- 743,753 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 737,747 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z'; ! ERROR: cannot extract field from a non-object select '"foo"'::json ->> 1; ! ERROR: cannot extract element from a scalar select '"foo"'::json ->> 'z'; ! ERROR: cannot extract element from a scalar -- array length SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); json_array_length --- 773,795 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! -- array length SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); json_array_length *************** select '{"f2":["f3",1],"f4":{"f5":99,"f6 *** 922,930 **** -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::json #> '{}'; ! ?column? ! ---------- ! (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a']; --- 970,978 ---- -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::json #> '{}'; ! ?column? ! --------------------------- ! {"a": {"b":{"c": "foo"}}} (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a']; *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 934,942 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null]; ! ERROR: cannot call json_extract_path with null path elements select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', '']; ! ERROR: cannot call json_extract_path with empty path elements select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b']; ?column? -------------- --- 982,998 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', '']; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b']; ?column? -------------- *************** select '42'::json #> array['0']; *** 1004,1012 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}'; ! ?column? ! ---------- ! (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a']; --- 1060,1068 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}'; ! ?column? ! --------------------------- ! {"a": {"b":{"c": "foo"}}} (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a']; *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 1016,1024 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null]; ! ERROR: cannot call json_extract_path_text with null path elements select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', '']; ! ERROR: cannot call json_extract_path_text with empty path elements select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b']; ?column? -------------- --- 1072,1088 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', '']; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b']; ?column? -------------- diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out index 077fcbd..e61cd55 100644 *** a/src/test/regress/expected/json_1.out --- b/src/test/regress/expected/json_1.out *************** INSERT INTO test_json VALUES *** 506,516 **** SELECT test_json -> 'x' FROM test_json WHERE json_type = 'scalar'; ! ERROR: cannot extract element from a scalar SELECT test_json -> 'x' FROM test_json WHERE json_type = 'array'; ! ERROR: cannot extract field from a non-object SELECT test_json -> 'x' FROM test_json WHERE json_type = 'object'; --- 506,524 ---- SELECT test_json -> 'x' FROM test_json WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_json WHERE json_type = 'array'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_json WHERE json_type = 'object'; *************** WHERE json_type = 'object'; *** 538,544 **** SELECT test_json -> 2 FROM test_json WHERE json_type = 'scalar'; ! ERROR: cannot extract element from a scalar SELECT test_json -> 2 FROM test_json WHERE json_type = 'array'; --- 546,556 ---- SELECT test_json -> 2 FROM test_json WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 2 FROM test_json WHERE json_type = 'array'; *************** WHERE json_type = 'array'; *** 550,556 **** SELECT test_json -> 2 FROM test_json WHERE json_type = 'object'; ! ERROR: cannot extract array element from a non-array SELECT test_json->>2 FROM test_json WHERE json_type = 'array'; --- 562,572 ---- SELECT test_json -> 2 FROM test_json WHERE json_type = 'object'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json->>2 FROM test_json WHERE json_type = 'array'; *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 667,673 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1; ! ERROR: cannot extract array element from a non-array select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z'; ?column? ---------- --- 683,693 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 693,703 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z'; ! ERROR: cannot extract field from a non-object select '"foo"'::json -> 1; ! ERROR: cannot extract element from a scalar select '"foo"'::json -> 'z'; ! ERROR: cannot extract element from a scalar select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::text; ?column? ---------- --- 713,735 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::text; ?column? ---------- *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 711,717 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 1; ! ERROR: cannot extract array element from a non-array select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 'z'; ?column? ---------- --- 743,753 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 737,747 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z'; ! ERROR: cannot extract field from a non-object select '"foo"'::json ->> 1; ! ERROR: cannot extract element from a scalar select '"foo"'::json ->> 'z'; ! ERROR: cannot extract element from a scalar -- array length SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); json_array_length --- 773,795 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::json ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! -- array length SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); json_array_length *************** select '{"f2":["f3",1],"f4":{"f5":99,"f6 *** 922,930 **** -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::json #> '{}'; ! ?column? ! ---------- ! (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a']; --- 970,978 ---- -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::json #> '{}'; ! ?column? ! --------------------------- ! {"a": {"b":{"c": "foo"}}} (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a']; *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 934,942 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null]; ! ERROR: cannot call json_extract_path with null path elements select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', '']; ! ERROR: cannot call json_extract_path with empty path elements select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b']; ?column? -------------- --- 982,998 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', '']; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b']; ?column? -------------- *************** select '42'::json #> array['0']; *** 1004,1012 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}'; ! ?column? ! ---------- ! (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a']; --- 1060,1068 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}'; ! ?column? ! --------------------------- ! {"a": {"b":{"c": "foo"}}} (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a']; *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 1016,1024 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null]; ! ERROR: cannot call json_extract_path_text with null path elements select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', '']; ! ERROR: cannot call json_extract_path_text with empty path elements select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b']; ?column? -------------- --- 1072,1088 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', '']; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b']; ?column? -------------- diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index ea4d6e1..7131568 100644 *** a/src/test/regress/expected/jsonb.out --- b/src/test/regress/expected/jsonb.out *************** INSERT INTO test_jsonb VALUES *** 311,319 **** ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on a scalar SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on an array SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- --- 311,327 ---- ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- *************** SELECT test_json -> 'field2' FROM test_j *** 327,335 **** (1 row) SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on a scalar SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on an array SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- --- 335,351 ---- (1 row) SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- *************** SELECT test_json ->> 'field2' FROM test_ *** 337,343 **** (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on a scalar SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- --- 353,363 ---- (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- *************** SELECT test_json -> 9 FROM test_jsonb WH *** 351,357 **** (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object'; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on an object SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array'; ?column? ----------- --- 371,381 ---- (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array'; ?column? ----------- *************** SELECT test_json ->> 'field6' FROM test_ *** 383,389 **** (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_array_element_text on a scalar SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- --- 407,417 ---- (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- *************** SELECT test_json ->> 2 FROM test_jsonb W *** 391,397 **** (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object'; ! ERROR: cannot call jsonb_array_element_text on an object SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'scalar'; ERROR: cannot call jsonb_object_keys on a scalar SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'array'; --- 419,429 ---- (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object'; ! ?column? ! ---------- ! ! (1 row) ! SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'scalar'; ERROR: cannot call jsonb_object_keys on a scalar SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'array'; *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 446,452 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on an object select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z'; ?column? ---------- --- 478,488 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 472,482 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on an array select '"foo"'::jsonb -> 1; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on a scalar select '"foo"'::jsonb -> 'z'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on a scalar select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text; ?column? ---------- --- 508,530 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text; ?column? ---------- *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 490,496 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1; ! ERROR: cannot call jsonb_array_element_text on an object select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z'; ?column? ---------- --- 538,548 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 516,526 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on an array select '"foo"'::jsonb ->> 1; ! ERROR: cannot call jsonb_array_element_text on a scalar select '"foo"'::jsonb ->> 'z'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on a scalar -- equality and inequality SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb; ?column? --- 568,590 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! -- equality and inequality SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb; ?column? *************** SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6 *** 1269,1277 **** -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}'; ! ?column? ! ---------- ! (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a']; --- 1333,1341 ---- -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}'; ! ?column? ! ---------------------------- ! {"a": {"b": {"c": "foo"}}} (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a']; *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 1281,1287 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', null]; ! ERROR: cannot call jsonb_extract_path with null path elements select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', '']; ?column? ---------- --- 1345,1355 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', '']; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 1337,1353 **** (1 row) select '"foo"'::jsonb #> array['z']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #> array['f2']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #> array['0']; - ERROR: cannot extract path from a scalar - select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}'; ?column? ---------- (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a']; ?column? --------------------- --- 1405,1433 ---- (1 row) select '"foo"'::jsonb #> array['z']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #> array['f2']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #> array['0']; ?column? ---------- (1 row) + select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}'; + ?column? + ---------------------------- + {"a": {"b": {"c": "foo"}}} + (1 row) + select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a']; ?column? --------------------- *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 1355,1361 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', null]; ! ERROR: cannot call jsonb_extract_path_text with null path elements select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', '']; ?column? ---------- --- 1435,1445 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', '']; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 1411,1421 **** (1 row) select '"foo"'::jsonb #>> array['z']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #>> array['f2']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #>> array['0']; ! ERROR: cannot extract path from a scalar -- array_elements SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'); jsonb_array_elements --- 1495,1517 ---- (1 row) select '"foo"'::jsonb #>> array['z']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #>> array['f2']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #>> array['0']; ! ?column? ! ---------- ! ! (1 row) ! -- array_elements SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'); jsonb_array_elements *************** SELECT '{"n":null,"a":1,"b":[1,2],"c":{" *** 2105,2111 **** (1 row) SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 0; --expecting error ! ERROR: cannot call jsonb_array_element (jsonb -> int) on an object SELECT '["a","b","c",[1,2],null]'::jsonb -> 0; ?column? ---------- --- 2201,2211 ---- (1 row) SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 0; --expecting error ! ?column? ! ---------- ! ! (1 row) ! SELECT '["a","b","c",[1,2],null]'::jsonb -> 0; ?column? ---------- diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out index 4c2d5ae..613dd8d 100644 *** a/src/test/regress/expected/jsonb_1.out --- b/src/test/regress/expected/jsonb_1.out *************** INSERT INTO test_jsonb VALUES *** 311,319 **** ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on a scalar SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on an array SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- --- 311,327 ---- ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- *************** SELECT test_json -> 'field2' FROM test_j *** 327,335 **** (1 row) SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on a scalar SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on an array SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- --- 335,351 ---- (1 row) SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object'; ?column? ---------- *************** SELECT test_json ->> 'field2' FROM test_ *** 337,343 **** (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on a scalar SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- --- 353,363 ---- (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- *************** SELECT test_json -> 9 FROM test_jsonb WH *** 351,357 **** (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object'; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on an object SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array'; ?column? ----------- --- 371,381 ---- (1 row) SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array'; ?column? ----------- *************** SELECT test_json ->> 'field6' FROM test_ *** 383,389 **** (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ERROR: cannot call jsonb_array_element_text on a scalar SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- --- 407,417 ---- (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar'; ! ?column? ! ---------- ! ! (1 row) ! SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array'; ?column? ---------- *************** SELECT test_json ->> 2 FROM test_jsonb W *** 391,397 **** (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object'; ! ERROR: cannot call jsonb_array_element_text on an object SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'scalar'; ERROR: cannot call jsonb_object_keys on a scalar SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'array'; --- 419,429 ---- (1 row) SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object'; ! ?column? ! ---------- ! ! (1 row) ! SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'scalar'; ERROR: cannot call jsonb_object_keys on a scalar SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'array'; *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 446,452 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on an object select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z'; ?column? ---------- --- 478,488 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 472,482 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on an array select '"foo"'::jsonb -> 1; ! ERROR: cannot call jsonb_array_element (jsonb -> int) on a scalar select '"foo"'::jsonb -> 'z'; ! ERROR: cannot call jsonb_object_field (jsonb -> text) on a scalar select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text; ?column? ---------- --- 508,530 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb -> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb -> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text; ?column? ---------- *************** select '{"a": [{"b": "c"}, {"b": "cc"}]} *** 490,496 **** (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1; ! ERROR: cannot call jsonb_array_element_text on an object select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z'; ?column? ---------- --- 538,548 ---- (1 row) select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z'; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 516,526 **** (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on an array select '"foo"'::jsonb ->> 1; ! ERROR: cannot call jsonb_array_element_text on a scalar select '"foo"'::jsonb ->> 'z'; ! ERROR: cannot call jsonb_object_field_text (jsonb ->> text) on a scalar -- equality and inequality SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb; ?column? --- 568,590 ---- (1 row) select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb ->> 1; ! ?column? ! ---------- ! ! (1 row) ! select '"foo"'::jsonb ->> 'z'; ! ?column? ! ---------- ! ! (1 row) ! -- equality and inequality SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb; ?column? *************** SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6 *** 1269,1277 **** -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}'; ! ?column? ! ---------- ! (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a']; --- 1333,1341 ---- -- corner cases for same select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}'; ! ?column? ! ---------------------------- ! {"a": {"b": {"c": "foo"}}} (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a']; *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 1281,1287 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', null]; ! ERROR: cannot call jsonb_extract_path with null path elements select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', '']; ?column? ---------- --- 1345,1355 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', '']; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 1337,1353 **** (1 row) select '"foo"'::jsonb #> array['z']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #> array['f2']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #> array['0']; - ERROR: cannot extract path from a scalar - select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}'; ?column? ---------- (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a']; ?column? --------------------- --- 1405,1433 ---- (1 row) select '"foo"'::jsonb #> array['z']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #> array['f2']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #> array['0']; ?column? ---------- (1 row) + select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}'; + ?column? + ---------------------------- + {"a": {"b": {"c": "foo"}}} + (1 row) + select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a']; ?column? --------------------- *************** select '{"a": {"b":{"c": "foo"}}}'::json *** 1355,1361 **** (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', null]; ! ERROR: cannot call jsonb_extract_path_text with null path elements select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', '']; ?column? ---------- --- 1435,1445 ---- (1 row) select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', null]; ! ?column? ! ---------- ! ! (1 row) ! select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', '']; ?column? ---------- *************** select '[{"b": "c"}, {"b": "cc"}]'::json *** 1411,1421 **** (1 row) select '"foo"'::jsonb #>> array['z']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #>> array['f2']; ! ERROR: cannot extract path from a scalar select '42'::jsonb #>> array['0']; ! ERROR: cannot extract path from a scalar -- array_elements SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'); jsonb_array_elements --- 1495,1517 ---- (1 row) select '"foo"'::jsonb #>> array['z']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #>> array['f2']; ! ?column? ! ---------- ! ! (1 row) ! select '42'::jsonb #>> array['0']; ! ?column? ! ---------- ! ! (1 row) ! -- array_elements SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'); jsonb_array_elements *************** SELECT '{"n":null,"a":1,"b":[1,2],"c":{" *** 2105,2111 **** (1 row) SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 0; --expecting error ! ERROR: cannot call jsonb_array_element (jsonb -> int) on an object SELECT '["a","b","c",[1,2],null]'::jsonb -> 0; ?column? ---------- --- 2201,2211 ---- (1 row) SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 0; --expecting error ! ?column? ! ---------- ! ! (1 row) ! SELECT '["a","b","c",[1,2],null]'::jsonb -> 0; ?column? ----------
В списке pgsql-bugs по дате отправления: