From 41dd50e84df39e31595f3472b0cb6d00f63b3f99 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 22 Aug 2014 13:17:58 -0400 Subject: Fix corner-case behaviors in JSON/JSONB field extraction operators. Cause the path extraction operators to return their lefthand input, not NULL, if the path array has no elements. This seems more consistent since the case ought to correspond to applying the simple extraction operator (->) zero times. Cause other corner cases in field/element/path extraction to return NULL rather than failing. This behavior is arguably more useful than throwing an error, since it allows an expression index using these operators to be built even when not all values in the column are suitable for the extraction being indexed. Moreover, we already had multiple inconsistencies between the path extraction operators and the simple extraction operators, as well as inconsistencies between the JSON and JSONB code paths. Adopt a uniform rule of returning NULL rather than throwing an error when the JSON input does not have a structure that permits the request to be satisfied. Back-patch to 9.4. Update the release notes to list this as a behavior change since 9.3. --- doc/src/sgml/func.sgml | 6 +++++- doc/src/sgml/json.sgml | 3 --- doc/src/sgml/release-9.4.sgml | 33 +++++++++++++++++++++++++++++++++ 3 files changed, 38 insertions(+), 4 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 13c71af8f01..c715ca25508 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10152,10 +10152,14 @@ table2-mapping There are parallel variants of these operators for both the - json and jsonb types. The operators + json and jsonb types. + The field/element/path extraction operators return the same type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text. + The field/element/path 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. diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index a56942a9496..37dd611aeb7 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -415,9 +415,6 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui' the "tags" key is common, defining an index like this may be worthwhile: --- 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')); Now, the WHERE clause jdoc -> 'tags' ? 'qui' diff --git a/doc/src/sgml/release-9.4.sgml b/doc/src/sgml/release-9.4.sgml index 5233ed256ae..aba8092c5bc 100644 --- a/doc/src/sgml/release-9.4.sgml +++ b/doc/src/sgml/release-9.4.sgml @@ -136,6 +136,39 @@ + + + The json + #> text[] path extraction operator now + returns its lefthand input, not NULL, if the array is empty (Tom Lane) + + + + This is consistent with the notion that this represents zero + applications of the simple field/element extraction + operator ->. Similarly, json + #>> text[] with an empty array merely + coerces its lefthand input to text. + + + + + + Corner cases in + the JSON + field/element/path extraction operators now return NULL rather + than raising an error (Tom Lane) + + + + For example, applying field extraction to a JSON array now yields NULL + not an error. This is more consistent (since some comparable cases such + as no-such-field already returned NULL), and it makes it safe to create + expression indexes that use these operators, since they will now not + throw errors for any valid JSON input. + + + Cause consecutive whitespace in