I wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> So will
>> val #>> '{}'
>> now return a dequoted bare scalar string? I think that's where the OP
>> actually came into this.
> If you think the latter should be dequoted, we can probably make it so.
Well, that turned out to be significantly more painful than I expected.
I ended up mostly rewriting get_worker() to make it less of a mess...
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..3d72c24 100644
*** a/src/backend/utils/adt/jsonfuncs.c
--- b/src/backend/utils/adt/jsonfuncs.c
*************** static void okeys_scalar(void *state, ch
*** 40,60 ****
/* 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);
static void get_array_element_start(void *state, bool isnull);
static void get_array_element_end(void *state, bool isnull);
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);
--- 40,59 ----
/* semantic action functions for json_get* functions */
static void get_object_start(void *state);
+ static void get_object_end(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);
+ static void get_array_end(void *state);
static void get_array_element_start(void *state, bool isnull);
static void get_array_element_end(void *state, bool isnull);
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 **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);
*************** static JsonbValue *findJsonbValueFromCon
*** 116,129 ****
char *key,
uint32 keylen);
- /* search type classification for json_get* functions */
- typedef enum
- {
- JSON_SEARCH_OBJECT = 1,
- JSON_SEARCH_ARRAY,
- JSON_SEARCH_PATH
- } JsonSearch;
-
/* state for json_object_keys */
typedef struct OkeysState
{
--- 115,120 ----
*************** typedef struct OkeysState
*** 138,158 ****
typedef struct GetState
{
JsonLexContext *lex;
- JsonSearch search_type;
- int search_index;
- int array_index;
- char *search_term;
- char *result_start;
text *tresult;
! bool result_is_null;
bool normalize_results;
bool next_scalar;
! char **path;
! int npath;
! char **current_path;
! bool *pathok;
! int *array_level_index;
! int *path_level_index;
} GetState;
/* state for json_array_length */
--- 129,143 ----
typedef struct GetState
{
JsonLexContext *lex;
text *tresult;
! char *result_start;
bool normalize_results;
bool next_scalar;
! int npath; /* length of each path-related array */
! char **path_names; /* field name(s) being sought */
! int *path_indexes; /* array index(es) being sought */
! bool *pathok; /* is path matched to current depth? */
! int *array_cur_index; /* current element index at each path level */
} GetState;
/* state for json_array_length */
*************** Datum
*** 455,465 ****
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;
! result = get_worker(json, fnamestr, -1, NULL, NULL, -1, false);
if (result != NULL)
PG_RETURN_TEXT_P(result);
--- 440,450 ----
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;
! result = get_worker(json, &fnamestr, NULL, 1, false);
if (result != NULL)
PG_RETURN_TEXT_P(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));
--- 459,470 ----
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,510 ****
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;
! result = get_worker(json, fnamestr, -1, NULL, NULL, -1, true);
if (result != NULL)
PG_RETURN_TEXT_P(result);
--- 476,486 ----
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;
! result = get_worker(json, &fnamestr, NULL, 1, true);
if (result != NULL)
PG_RETURN_TEXT_P(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)
{
--- 495,506 ----
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)
{
*************** json_array_element(PG_FUNCTION_ARGS)
*** 579,585 ****
int element = PG_GETARG_INT32(1);
text *result;
! result = get_worker(json, NULL, element, NULL, NULL, -1, false);
if (result != NULL)
PG_RETURN_TEXT_P(result);
--- 546,552 ----
int element = PG_GETARG_INT32(1);
text *result;
! result = get_worker(json, NULL, &element, 1, false);
if (result != NULL)
PG_RETURN_TEXT_P(result);
*************** 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)
--- 561,568 ----
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)
*************** json_array_element_text(PG_FUNCTION_ARGS
*** 621,627 ****
int element = PG_GETARG_INT32(1);
text *result;
! result = get_worker(json, NULL, element, NULL, NULL, -1, true);
if (result != NULL)
PG_RETURN_TEXT_P(result);
--- 578,584 ----
int element = PG_GETARG_INT32(1);
text *result;
! result = get_worker(json, NULL, &element, 1, true);
if (result != NULL)
PG_RETURN_TEXT_P(result);
*************** 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)
--- 593,600 ----
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);
--- 637,656 ----
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,768 ****
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;
}
! result = get_worker(json, NULL, -1, tpath, ipath, npath, as_text);
if (result != NULL)
PG_RETURN_TEXT_P(result);
else
- /* null is NULL, regardless */
PG_RETURN_NULL();
}
--- 661,714 ----
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);
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;
!
! errno = 0;
! ind = strtol(tpath[i], &endptr, 10);
! if (*endptr == '\0' && errno == 0 && ind <= INT_MAX && ind >= 0)
! ipath[i] = (int) ind;
! else
! ipath[i] = -1;
! }
else
ipath[i] = -1;
}
! result = get_worker(json, tpath, ipath, npath, as_text);
if (result != NULL)
PG_RETURN_TEXT_P(result);
else
PG_RETURN_NULL();
}
*************** get_path_all(FunctionCallInfo fcinfo, co
*** 770,824 ****
* get_worker
*
* common worker for all the json getter functions
*/
static text *
get_worker(text *json,
- char *field,
- int elem_index,
char **tpath,
int *ipath,
int npath,
bool normalize_results)
{
- GetState *state;
JsonLexContext *lex = makeJsonLexContext(json, true);
! JsonSemAction *sem;
!
! /* only allowed to use one of these */
! Assert(elem_index < 0 || (tpath == NULL && ipath == NULL && field == NULL));
! Assert(tpath == NULL || field == NULL);
! state = palloc0(sizeof(GetState));
! sem = palloc0(sizeof(JsonSemAction));
state->lex = lex;
/* is it "_as_text" variant? */
state->normalize_results = normalize_results;
! if (field != NULL)
! {
! /* single text argument */
! state->search_type = JSON_SEARCH_OBJECT;
! state->search_term = field;
! }
! else if (tpath != NULL)
! {
! /* path array argument */
! state->search_type = JSON_SEARCH_PATH;
! state->path = tpath;
! state->npath = npath;
! state->current_path = palloc(sizeof(char *) * npath);
! state->pathok = palloc0(sizeof(bool) * npath);
state->pathok[0] = true;
- state->array_level_index = palloc(sizeof(int) * npath);
- state->path_level_index = ipath;
- }
- else
- {
- /* single integer argument */
- state->search_type = JSON_SEARCH_ARRAY;
- state->search_index = elem_index;
- state->array_index = -1;
- }
sem->semstate = (void *) state;
--- 716,757 ----
* get_worker
*
* common worker for all the json getter functions
+ *
+ * json: JSON object (in text form)
+ * tpath[]: field name(s) to extract
+ * ipath[]: array index(es) (zero-based) to extract
+ * npath: length of tpath[] and/or ipath[]
+ * normalize_results: true to de-escape string and null scalars
+ *
+ * tpath can be NULL, or any one tpath[] entry can be NULL, if an object
+ * field is not to be matched at that nesting level. Similarly, ipath can
+ * be NULL, or any one ipath[] entry can be -1, if an array element is not
+ * to be matched at that nesting level.
*/
static text *
get_worker(text *json,
char **tpath,
int *ipath,
int npath,
bool normalize_results)
{
JsonLexContext *lex = makeJsonLexContext(json, true);
! JsonSemAction *sem = palloc0(sizeof(JsonSemAction));
! GetState *state = palloc0(sizeof(GetState));
! Assert(npath >= 0);
state->lex = lex;
/* is it "_as_text" variant? */
state->normalize_results = normalize_results;
! state->npath = npath;
! state->path_names = tpath;
! state->path_indexes = ipath;
! state->pathok = palloc0(sizeof(bool) * npath);
! state->array_cur_index = palloc(sizeof(int) * npath);
!
! if (npath > 0)
state->pathok[0] = true;
sem->semstate = (void *) state;
*************** get_worker(text *json,
*** 826,841 ****
* 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)
{
sem->object_field_start = get_object_field_start;
sem->object_field_end = get_object_field_end;
}
! if (field == NULL)
{
sem->array_element_start = get_array_element_start;
sem->array_element_end = get_array_element_end;
}
--- 759,779 ----
* Not all variants need all the semantic routines. Only set the ones that
* are actually needed for maximum efficiency.
*/
sem->scalar = get_scalar;
! if (npath == 0)
! {
! sem->object_start = get_object_start;
! sem->object_end = get_object_end;
! sem->array_end = get_array_end;
! }
! if (tpath != NULL)
{
sem->object_field_start = get_object_field_start;
sem->object_field_end = get_object_field_end;
}
! if (ipath != NULL)
{
+ sem->array_start = get_array_start;
sem->array_element_start = get_array_element_start;
sem->array_element_end = get_array_element_end;
}
*************** static void
*** 849,898 ****
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;
- bool get_next = false;
int lex_level = _state->lex->lex_level;
! if (lex_level == 1 && _state->search_type == JSON_SEARCH_OBJECT &&
! strcmp(fname, _state->search_term) == 0)
{
! _state->tresult = NULL;
! _state->result_start = NULL;
! get_next = true;
}
! 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)
{
if (_state->normalize_results &&
_state->lex->token_type == JSON_TOKEN_STRING)
{
--- 787,852 ----
get_object_start(void *state)
{
GetState *_state = (GetState *) state;
+ int lex_level = _state->lex->lex_level;
! if (lex_level == 0 && _state->npath == 0)
! {
! /*
! * Special case: we should match the entire object. We only need this
! * at outermost level because at nested levels the match will have
! * been started by the outer field or array element callback.
! */
! _state->result_start = _state->lex->token_start;
! }
}
static void
! get_object_end(void *state)
{
GetState *_state = (GetState *) state;
int lex_level = _state->lex->lex_level;
! if (lex_level == 0 && _state->npath == 0)
{
! /* Special case: return the entire object */
! char *start = _state->result_start;
! int len = _state->lex->prev_token_terminator - start;
!
! _state->tresult = cstring_to_text_with_len(start, len);
}
! }
! static void
! get_object_field_start(void *state, char *fname, bool isnull)
! {
! GetState *_state = (GetState *) state;
! bool get_next = false;
! int lex_level = _state->lex->lex_level;
! if (lex_level <= _state->npath &&
! _state->pathok[lex_level - 1] &&
! _state->path_names != NULL &&
! _state->path_names[lex_level - 1] != NULL &&
! strcmp(fname, _state->path_names[lex_level - 1]) == 0)
! {
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)
{
+ /* this object overrides any previous matching object */
+ _state->tresult = NULL;
+ _state->result_start = NULL;
+
if (_state->normalize_results &&
_state->lex->token_type == JSON_TOKEN_STRING)
{
*************** get_object_field_end(void *state, char *
*** 914,939 ****
bool get_last = false;
int lex_level = _state->lex->lex_level;
! /* same tests as in get_object_field_start, mutatis mutandis */
! if (lex_level == 1 && _state->search_type == JSON_SEARCH_OBJECT &&
! strcmp(fname, _state->search_term) == 0)
! {
! get_last = true;
! }
! 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)
{
- /* 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)
{
/*
--- 868,893 ----
bool get_last = false;
int lex_level = _state->lex->lex_level;
! /* same tests as in get_object_field_start */
! if (lex_level <= _state->npath &&
! _state->pathok[lex_level - 1] &&
! _state->path_names != NULL &&
! _state->path_names[lex_level - 1] != NULL &&
! strcmp(fname, _state->path_names[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 scalar case, our work is already done */
if (get_last && _state->result_start != NULL)
{
/*
*************** get_object_field_end(void *state, char *
*** 941,959 ****
* start up to the end of the previous token (the lexer is by now
* ahead of us on whatever came after what we're interested in).
*/
- int len = _state->lex->prev_token_terminator - _state->result_start;
-
if (isnull && _state->normalize_results)
_state->tresult = (text *) NULL;
else
! _state->tresult = cstring_to_text_with_len(_state->result_start, len);
! }
! /*
! * don't need to reset _state->result_start b/c we're only returning one
! * datum, the conditions should not occur more than once, and this lets us
! * check cheaply that they don't (see object_field_start() )
! */
}
static void
--- 895,913 ----
* start up to the end of the previous token (the lexer is by now
* ahead of us on whatever came after what we're interested in).
*/
if (isnull && _state->normalize_results)
_state->tresult = (text *) NULL;
else
! {
! char *start = _state->result_start;
! int len = _state->lex->prev_token_terminator - start;
! _state->tresult = cstring_to_text_with_len(start, len);
! }
!
! /* this should be unnecessary but let's do it for cleanliness: */
! _state->result_start = NULL;
! }
}
static void
*************** get_array_start(void *state)
*** 962,980 ****
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.
! */
! if (_state->search_type == JSON_SEARCH_PATH &&
! lex_level < _state->npath)
! _state->array_level_index[lex_level] = -1;
}
static void
--- 916,951 ----
GetState *_state = (GetState *) state;
int lex_level = _state->lex->lex_level;
! if (lex_level < _state->npath)
! {
! /* Initialize counting of elements in this array */
! _state->array_cur_index[lex_level] = -1;
! }
! else if (lex_level == 0 && _state->npath == 0)
! {
! /*
! * Special case: we should match the entire array. We only need this
! * at outermost level because at nested levels the match will have
! * been started by the outer field or array element callback.
! */
! _state->result_start = _state->lex->token_start;
! }
! }
! static void
! get_array_end(void *state)
! {
! GetState *_state = (GetState *) state;
! int lex_level = _state->lex->lex_level;
!
! if (lex_level == 0 && _state->npath == 0)
! {
! /* Special case: return the entire array */
! char *start = _state->result_start;
! int len = _state->lex->prev_token_terminator - start;
!
! _state->tresult = cstring_to_text_with_len(start, len);
! }
}
static void
*************** get_array_element_start(void *state, boo
*** 984,1027 ****
bool get_next = false;
int lex_level = _state->lex->lex_level;
! if (lex_level == 1 && _state->search_type == JSON_SEARCH_ARRAY)
! {
! /* single integer search */
! _state->array_index++;
! if (_state->array_index == _state->search_index)
! get_next = true;
! }
! else if (_state->search_type == JSON_SEARCH_PATH &&
! lex_level <= _state->npath &&
! _state->pathok[lex_level - 1])
{
! /*
! * path search, path so far is ok
! *
! * increment the array counter. no point doing this if we already know
! * the path is bad.
! *
! * then check if we have a match.
! */
! 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;
! }
}
}
/* same logic as for objects */
if (get_next)
{
if (_state->normalize_results &&
_state->lex->token_type == JSON_TOKEN_STRING)
{
--- 955,987 ----
bool get_next = false;
int lex_level = _state->lex->lex_level;
! /* Update array element counter */
! if (lex_level <= _state->npath)
! _state->array_cur_index[lex_level - 1]++;
!
! if (lex_level <= _state->npath &&
! _state->pathok[lex_level - 1] &&
! _state->path_indexes != NULL &&
! _state->array_cur_index[lex_level - 1] == _state->path_indexes[lex_level - 1])
{
! 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;
}
}
/* same logic as for objects */
if (get_next)
{
+ _state->tresult = NULL;
+ _state->result_start = NULL;
+
if (_state->normalize_results &&
_state->lex->token_type == JSON_TOKEN_STRING)
{
*************** get_array_element_end(void *state, bool
*** 1041,1074 ****
bool get_last = false;
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)
! {
! get_last = true;
! }
! else if (_state->search_type == JSON_SEARCH_PATH &&
! lex_level <= _state->npath &&
! _state->pathok[lex_level - 1] &&
! _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;
-
if (isnull && _state->normalize_results)
_state->tresult = (text *) NULL;
else
! _state->tresult = cstring_to_text_with_len(_state->result_start, len);
}
}
--- 1001,1038 ----
bool get_last = false;
int lex_level = _state->lex->lex_level;
! /* same tests as in get_array_element_start */
! if (lex_level <= _state->npath &&
! _state->pathok[lex_level - 1] &&
! _state->path_indexes != NULL &&
! _state->array_cur_index[lex_level - 1] == _state->path_indexes[lex_level - 1])
{
if (lex_level < _state->npath)
+ {
+ /* done with this element so reset pathok */
_state->pathok[lex_level] = false;
! }
! else
! {
! /* end of path, so we want this value */
get_last = true;
+ }
}
+
+ /* same logic as for objects */
if (get_last && _state->result_start != NULL)
{
if (isnull && _state->normalize_results)
_state->tresult = (text *) NULL;
else
! {
! char *start = _state->result_start;
! int len = _state->lex->prev_token_terminator - start;
!
! _state->tresult = cstring_to_text_with_len(start, len);
! }
!
! _state->result_start = NULL;
}
}
*************** static void
*** 1076,1086 ****
get_scalar(void *state, char *token, JsonTokenType tokentype)
{
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 */
--- 1040,1073 ----
get_scalar(void *state, char *token, JsonTokenType tokentype)
{
GetState *_state = (GetState *) state;
+ int lex_level = _state->lex->lex_level;
+
+ /* Check for whole-object match */
+ if (lex_level == 0 && _state->npath == 0)
+ {
+ if (_state->normalize_results && tokentype == JSON_TOKEN_STRING)
+ {
+ /* we want the de-escaped string */
+ _state->next_scalar = true;
+ }
+ else if (_state->normalize_results && tokentype == JSON_TOKEN_NULL)
+ {
+ _state->tresult = (text *) NULL;
+ }
+ else
+ {
+ /*
+ * This is a bit hokey: we will suppress whitespace after the
+ * scalar token, but not whitespace before it. Probably not worth
+ * doing our own space-skipping to avoid that.
+ */
+ char *start = _state->lex->input;
+ int len = _state->lex->prev_token_terminator - start;
+
+ _state->tresult = cstring_to_text_with_len(start, len);
+ }
+ }
if (_state->next_scalar)
{
/* a de-escaped text value is wanted, so supply it */
*************** 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);
--- 1080,1096 ----
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,1145 ****
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;
else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
have_array = true;
! container = &jb->root;
for (i = 0; i < npath; i++)
{
--- 1105,1160 ----
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);
! /* Identify whether we have object, array, or scalar at top-level */
! container = &jb->root;
if (JB_ROOT_IS_OBJECT(jb))
have_object = true;
else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
have_array = true;
+ else
+ {
+ Assert(JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb));
+ /* Extract the scalar value, if it is what we'll return */
+ if (npath <= 0)
+ jbvp = getIthJsonbValueFromContainer(container, 0);
+ }
! /*
! * If the array is empty, return the entire LHS object, on the grounds
! * that we should do zero field or element extractions. For the
! * non-scalar case we can just hand back the object without much work. For
! * the scalar case, fall through and deal with the value below the loop.
! * (This inconsistency arises because there's no easy way to generate a
! * JsonbValue directly for root-level containers.)
! */
! if (npath <= 0 && jbvp == NULL)
! {
! if (as_text)
! {
! PG_RETURN_TEXT_P(cstring_to_text(JsonbToCString(NULL,
! container,
! VARSIZE(jb))));
! }
! else
! {
! /* not text mode - just hand back the jsonb */
! PG_RETURN_JSONB(jb);
! }
! }
for (i = 0; i < npath; i++)
{
*************** get_jsonb_path_all(FunctionCallInfo fcin
*** 1157,1174 ****
char *indextext = TextDatumGetCString(pathtext[i]);
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();
}
--- 1172,1188 ----
char *indextext = TextDatumGetCString(pathtext[i]);
char *endptr;
+ errno = 0;
lindex = strtol(indextext, &endptr, 10);
! if (endptr == indextext || *endptr != '\0' || errno != 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();
}
--- 1210,1220 ----
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..bb4d9ed 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,741 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::json -> 'b';
! ?column?
! ----------
! null
! (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?
----------
--- 749,759 ----
(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
--- 779,807 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::json ->> 'b';
! ?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'];
--- 982,1014 ----
-- corner cases for same
select '{"a": {"b":{"c": "foo"}}}'::json #> '{}';
+ ?column?
+ ---------------------------
+ {"a": {"b":{"c": "foo"}}}
+ (1 row)
+
+ select '[1,2,3]'::json #> '{}';
?column?
----------
! [1,2,3]
! (1 row)
!
! select '"foo"'::json #> '{}';
! ?column?
! ----------
! "foo"
! (1 row)
!
! select '42'::json #> '{}';
! ?column?
! ----------
! 42
! (1 row)
!
! select 'null'::json #> '{}';
! ?column?
! ----------
! null
(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?
--------------
--- 1018,1034 ----
(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 '[{"b": "c"}, {"b": "cc"}]'::json
*** 985,990 ****
--- 1077,1088 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::json #> array['1','b'];
+ ?column?
+ ----------
+ null
+ (1 row)
+
select '"foo"'::json #> array['z'];
?column?
----------
*************** select '42'::json #> array['0'];
*** 1004,1009 ****
--- 1102,1131 ----
(1 row)
select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}';
+ ?column?
+ ---------------------------
+ {"a": {"b":{"c": "foo"}}}
+ (1 row)
+
+ select '[1,2,3]'::json #>> '{}';
+ ?column?
+ ----------
+ [1,2,3]
+ (1 row)
+
+ select '"foo"'::json #>> '{}';
+ ?column?
+ ----------
+ foo
+ (1 row)
+
+ select '42'::json #>> '{}';
+ ?column?
+ ----------
+ 42
+ (1 row)
+
+ select 'null'::json #>> '{}';
?column?
----------
*************** 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?
--------------
--- 1138,1154 ----
(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 '[{"b": "c"}, {"b": "cc"}]'::json
*** 1067,1072 ****
--- 1197,1208 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::json #>> array['1','b'];
+ ?column?
+ ----------
+
+ (1 row)
+
select '"foo"'::json #>> array['z'];
?column?
----------
diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out
index 077fcbd..83c1d7d 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,741 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::json -> 'b';
! ?column?
! ----------
! null
! (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?
----------
--- 749,759 ----
(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
--- 779,807 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::json ->> 'b';
! ?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'];
--- 982,1014 ----
-- corner cases for same
select '{"a": {"b":{"c": "foo"}}}'::json #> '{}';
+ ?column?
+ ---------------------------
+ {"a": {"b":{"c": "foo"}}}
+ (1 row)
+
+ select '[1,2,3]'::json #> '{}';
?column?
----------
! [1,2,3]
! (1 row)
!
! select '"foo"'::json #> '{}';
! ?column?
! ----------
! "foo"
! (1 row)
!
! select '42'::json #> '{}';
! ?column?
! ----------
! 42
! (1 row)
!
! select 'null'::json #> '{}';
! ?column?
! ----------
! null
(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?
--------------
--- 1018,1034 ----
(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 '[{"b": "c"}, {"b": "cc"}]'::json
*** 985,990 ****
--- 1077,1088 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::json #> array['1','b'];
+ ?column?
+ ----------
+ null
+ (1 row)
+
select '"foo"'::json #> array['z'];
?column?
----------
*************** select '42'::json #> array['0'];
*** 1004,1009 ****
--- 1102,1131 ----
(1 row)
select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}';
+ ?column?
+ ---------------------------
+ {"a": {"b":{"c": "foo"}}}
+ (1 row)
+
+ select '[1,2,3]'::json #>> '{}';
+ ?column?
+ ----------
+ [1,2,3]
+ (1 row)
+
+ select '"foo"'::json #>> '{}';
+ ?column?
+ ----------
+ foo
+ (1 row)
+
+ select '42'::json #>> '{}';
+ ?column?
+ ----------
+ 42
+ (1 row)
+
+ select 'null'::json #>> '{}';
?column?
----------
*************** 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?
--------------
--- 1138,1154 ----
(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 '[{"b": "c"}, {"b": "cc"}]'::json
*** 1067,1072 ****
--- 1197,1208 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::json #>> array['1','b'];
+ ?column?
+ ----------
+
+ (1 row)
+
select '"foo"'::json #>> array['z'];
?column?
----------
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index ea4d6e1..eb37da7 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,536 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::jsonb -> 'b';
! ?column?
! ----------
! null
! (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?
----------
--- 544,554 ----
(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?
--- 574,602 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::jsonb ->> 'b';
! ?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'];
--- 1345,1377 ----
-- corner cases for same
select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
+ ?column?
+ ----------------------------
+ {"a": {"b": {"c": "foo"}}}
+ (1 row)
+
+ select '[1,2,3]'::jsonb #> '{}';
+ ?column?
+ -----------
+ [1, 2, 3]
+ (1 row)
+
+ select '"foo"'::jsonb #> '{}';
?column?
----------
! "foo"
! (1 row)
!
! select '42'::jsonb #> '{}';
! ?column?
! ----------
! 42
! (1 row)
!
! select 'null'::jsonb #> '{}';
! ?column?
! ----------
! null
(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?
----------
--- 1381,1391 ----
(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
*** 1336,1348 ****
(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?
----------
--- 1440,1494 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::jsonb #> array['1','b'];
+ ?column?
+ ----------
+ null
+ (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 '[1,2,3]'::jsonb #>> '{}';
+ ?column?
+ -----------
+ [1, 2, 3]
+ (1 row)
+
+ select '"foo"'::jsonb #>> '{}';
+ ?column?
+ ----------
+ foo
+ (1 row)
+
+ select '42'::jsonb #>> '{}';
+ ?column?
+ ----------
+ 42
+ (1 row)
+
+ select 'null'::jsonb #>> '{}';
?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?
----------
--- 1501,1511 ----
(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
*** 1410,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
--- 1560,1589 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::jsonb #>> array['1','b'];
+ ?column?
+ ----------
+
+ (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?
----------
--- 2273,2283 ----
(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..f3bfc7b 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,536 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::jsonb -> 'b';
! ?column?
! ----------
! null
! (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?
----------
--- 544,554 ----
(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?
--- 574,602 ----
(1 row)
select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z';
! ?column?
! ----------
!
! (1 row)
!
! select '{"a": "c", "b": null}'::jsonb ->> 'b';
! ?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'];
--- 1345,1377 ----
-- corner cases for same
select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
+ ?column?
+ ----------------------------
+ {"a": {"b": {"c": "foo"}}}
+ (1 row)
+
+ select '[1,2,3]'::jsonb #> '{}';
+ ?column?
+ -----------
+ [1, 2, 3]
+ (1 row)
+
+ select '"foo"'::jsonb #> '{}';
?column?
----------
! "foo"
! (1 row)
!
! select '42'::jsonb #> '{}';
! ?column?
! ----------
! 42
! (1 row)
!
! select 'null'::jsonb #> '{}';
! ?column?
! ----------
! null
(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?
----------
--- 1381,1391 ----
(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
*** 1336,1348 ****
(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?
----------
--- 1440,1494 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::jsonb #> array['1','b'];
+ ?column?
+ ----------
+ null
+ (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 '[1,2,3]'::jsonb #>> '{}';
+ ?column?
+ -----------
+ [1, 2, 3]
+ (1 row)
+
+ select '"foo"'::jsonb #>> '{}';
+ ?column?
+ ----------
+ foo
+ (1 row)
+
+ select '42'::jsonb #>> '{}';
+ ?column?
+ ----------
+ 42
+ (1 row)
+
+ select 'null'::jsonb #>> '{}';
?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?
----------
--- 1501,1511 ----
(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
*** 1410,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
--- 1560,1589 ----
(1 row)
+ select '[{"b": "c"}, {"b": null}]'::jsonb #>> array['1','b'];
+ ?column?
+ ----------
+
+ (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?
----------
--- 2273,2283 ----
(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/sql/json.sql b/src/test/regress/sql/json.sql
index 4db5547..c980132 100644
*** a/src/test/regress/sql/json.sql
--- b/src/test/regress/sql/json.sql
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 248,253 ****
--- 248,254 ----
select '[{"b": "c"}, {"b": "cc"}]'::json -> 1;
select '[{"b": "c"}, {"b": "cc"}]'::json -> 3;
select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z';
+ select '{"a": "c", "b": null}'::json -> 'b';
select '"foo"'::json -> 1;
select '"foo"'::json -> 'z';
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 259,264 ****
--- 260,266 ----
select '[{"b": "c"}, {"b": "cc"}]'::json ->> 1;
select '[{"b": "c"}, {"b": "cc"}]'::json ->> 3;
select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z';
+ select '{"a": "c", "b": null}'::json ->> 'b';
select '"foo"'::json ->> 1;
select '"foo"'::json ->> 'z';
*************** select '{"f2":["f3",1],"f4":{"f5":99,"f6
*** 312,317 ****
--- 314,323 ----
-- corner cases for same
select '{"a": {"b":{"c": "foo"}}}'::json #> '{}';
+ select '[1,2,3]'::json #> '{}';
+ select '"foo"'::json #> '{}';
+ select '42'::json #> '{}';
+ select 'null'::json #> '{}';
select '{"a": {"b":{"c": "foo"}}}'::json #> array['a'];
select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null];
select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', ''];
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 323,333 ****
--- 329,344 ----
select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','z','b'];
select '[{"b": "c"}, {"b": "cc"}]'::json #> array['1','b'];
select '[{"b": "c"}, {"b": "cc"}]'::json #> array['z','b'];
+ select '[{"b": "c"}, {"b": null}]'::json #> array['1','b'];
select '"foo"'::json #> array['z'];
select '42'::json #> array['f2'];
select '42'::json #> array['0'];
select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}';
+ select '[1,2,3]'::json #>> '{}';
+ select '"foo"'::json #>> '{}';
+ select '42'::json #>> '{}';
+ select 'null'::json #>> '{}';
select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a'];
select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null];
select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', ''];
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 339,344 ****
--- 350,356 ----
select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','z','b'];
select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['1','b'];
select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['z','b'];
+ select '[{"b": "c"}, {"b": null}]'::json #>> array['1','b'];
select '"foo"'::json #>> array['z'];
select '42'::json #>> array['f2'];
select '42'::json #>> array['0'];
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 141dda9..ed266d5 100644
*** a/src/test/regress/sql/jsonb.sql
--- b/src/test/regress/sql/jsonb.sql
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 117,122 ****
--- 117,123 ----
select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1;
select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3;
select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
+ select '{"a": "c", "b": null}'::jsonb -> 'b';
select '"foo"'::jsonb -> 1;
select '"foo"'::jsonb -> 'z';
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 128,133 ****
--- 129,135 ----
select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 1;
select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 3;
select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z';
+ select '{"a": "c", "b": null}'::jsonb ->> 'b';
select '"foo"'::jsonb ->> 1;
select '"foo"'::jsonb ->> 'z';
*************** SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6
*** 283,288 ****
--- 285,294 ----
-- corner cases for same
select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
+ select '[1,2,3]'::jsonb #> '{}';
+ select '"foo"'::jsonb #> '{}';
+ select '42'::jsonb #> '{}';
+ select 'null'::jsonb #> '{}';
select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a'];
select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', null];
select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', ''];
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 294,304 ****
--- 300,315 ----
select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','z','b'];
select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['1','b'];
select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['z','b'];
+ select '[{"b": "c"}, {"b": null}]'::jsonb #> array['1','b'];
select '"foo"'::jsonb #> array['z'];
select '42'::jsonb #> array['f2'];
select '42'::jsonb #> array['0'];
select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}';
+ select '[1,2,3]'::jsonb #>> '{}';
+ select '"foo"'::jsonb #>> '{}';
+ select '42'::jsonb #>> '{}';
+ select 'null'::jsonb #>> '{}';
select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a'];
select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', null];
select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', ''];
*************** select '{"a": [{"b": "c"}, {"b": "cc"}]}
*** 310,315 ****
--- 321,327 ----
select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','z','b'];
select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['1','b'];
select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['z','b'];
+ select '[{"b": "c"}, {"b": null}]'::jsonb #>> array['1','b'];
select '"foo"'::jsonb #>> array['z'];
select '42'::jsonb #>> array['f2'];
select '42'::jsonb #>> array['0'];