From 4d212bac1752e1bad6f3aa6242061c393ae93a0a Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Thu, 10 Oct 2013 12:21:59 -0400 Subject: [PATCH] json_typeof function. Andrew Tipton. --- doc/src/sgml/func.sgml | 31 +++++++++++++++-- src/backend/utils/adt/json.c | 52 ++++++++++++++++++++++++++++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.h | 2 ++ src/include/utils/json.h | 2 ++ src/test/regress/expected/json.out | 29 ++++++++++++++++ src/test/regress/expected/json_1.out | 29 ++++++++++++++++ src/test/regress/sql/json.sql | 15 ++++++++ 8 files changed, 158 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index be7b00b1f3..e397386bf2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10095,7 +10095,7 @@ table2-mapping json - Returns JSON object pointed to by path_elems. + Returns JSON value pointed to by path_elems. json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') {"f5":99,"f6":"foo"} @@ -10109,7 +10109,7 @@ table2-mapping text - Returns JSON object pointed to by path_elems. + Returns JSON value pointed to by path_elems. json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') foo @@ -10192,7 +10192,7 @@ table2-mapping SETOF json - Expands a JSON array to a set of JSON elements. + Expands a JSON array to a set of JSON values. json_array_elements('[1,true, [2,false]]') @@ -10205,6 +10205,23 @@ table2-mapping + + + + json_typeof + + json_typeof(json) + + text + + Returns the type of the outermost JSON value as a text string. The types are + object, array, string, number, + boolean, and null. (See note below regarding the + distinction between a JSON null and a SQL NULL.) + + json_typeof('-123.4') + number + @@ -10237,6 +10254,14 @@ table2-mapping + + + The json_typeof function's null return value should not be confused + with a SQL NULL. While calling json_typeof('null'::json) will return null, + calling json_typeof(NULL::json) will return a SQL NULL. + + + See also about the aggregate function json_agg which aggregates record diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c index 9f3f5d4feb..b6199fbf68 100644 --- a/src/backend/utils/adt/json.c +++ b/src/backend/utils/adt/json.c @@ -1826,3 +1826,55 @@ escape_json(StringInfo buf, const char *str) } appendStringInfoCharMacro(buf, '\"'); } + +/* + * SQL function json_typeof(json) -> text + * + * Returns the type of the outermost JSON value as TEXT. Possible types are + * "object", "array", "string", "number", "boolean", and "null". + * + * Performs a single call to json_lex() to get the first token of the supplied + * value. This initial token uniquely determines the value's type. As our + * input must already have been validated by json_in() or json_recv(), the + * initial token should never be JSON_TOKEN_OBJECT_END, JSON_TOKEN_ARRAY_END, + * JSON_TOKEN_COLON, JSON_TOKEN_COMMA, or JSON_TOKEN_END. + */ +Datum +json_typeof(PG_FUNCTION_ARGS) +{ + text *json = PG_GETARG_TEXT_P(0); + + JsonLexContext *lex = makeJsonLexContext(json, false); + JsonTokenType tok; + char *type; + + /* Lex exactly one token from the input and check its type. */ + json_lex(lex); + tok = lex_peek(lex); + switch (tok) + { + case JSON_TOKEN_OBJECT_START: + type = "object"; + break; + case JSON_TOKEN_ARRAY_START: + type = "array"; + break; + case JSON_TOKEN_STRING: + type = "string"; + break; + case JSON_TOKEN_NUMBER: + type = "number"; + break; + case JSON_TOKEN_TRUE: + case JSON_TOKEN_FALSE: + type = "boolean"; + break; + case JSON_TOKEN_NULL: + type = "null"; + break; + default: + elog(ERROR, "unexpected json token: %d", tok); + } + + PG_RETURN_TEXT_P(cstring_to_text(type)); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 5b2749c417..58ed8a3fe9 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201310091 +#define CATALOG_VERSION_NO 201310101 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 3e523989c3..08586ae064 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4150,6 +4150,8 @@ DATA(insert OID = 3960 ( json_populate_record PGNSP PGUID 12 1 0 0 0 f f f f DESCR("get record fields from a json object"); DATA(insert OID = 3961 ( json_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 114 16" _null_ _null_ _null_ _null_ json_populate_recordset _null_ _null_ _null_ )); DESCR("get set of records with fields from a json array of objects"); +DATA(insert OID = 3968 ( json_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "114" _null_ _null_ _null_ _null_ json_typeof _null_ _null_ _null_ )); +DESCR("get the type of a json value"); /* uuid */ DATA(insert OID = 2952 ( uuid_in PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2950 "2275" _null_ _null_ _null_ _null_ uuid_in _null_ _null_ _null_ )); diff --git a/src/include/utils/json.h b/src/include/utils/json.h index 4845f5f618..62fd7c7829 100644 --- a/src/include/utils/json.h +++ b/src/include/utils/json.h @@ -33,6 +33,8 @@ extern Datum json_agg_finalfn(PG_FUNCTION_ARGS); extern void escape_json(StringInfo buf, const char *str); +extern Datum json_typeof(PG_FUNCTION_ARGS); + /* functions in jsonfuncs.c */ extern Datum json_object_field(PG_FUNCTION_ARGS); extern Datum json_object_field_text(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index 1a357988e4..d7e9694132 100644 --- a/src/test/regress/expected/json.out +++ b/src/test/regress/expected/json.out @@ -962,3 +962,32 @@ select json '{ "a": "null \u0000 escape" }' ->> 'a' as not_unescaped; null \u0000 escape (1 row) +--json_typeof() function +select value, json_typeof(value) + from (values (json '123.4'), + (json '-1'), + (json '"foo"'), + (json 'true'), + (json 'false'), + (json 'null'), + (json '[1, 2, 3]'), + (json '[]'), + (json '{"x":"foo", "y":123}'), + (json '{}'), + (NULL::json)) + as data(value); + value | json_typeof +----------------------+------------- + 123.4 | number + -1 | number + "foo" | string + true | boolean + false | boolean + null | null + [1, 2, 3] | array + [] | array + {"x":"foo", "y":123} | object + {} | object + | +(11 rows) + diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out index 201fcb2d20..c9710d22e4 100644 --- a/src/test/regress/expected/json_1.out +++ b/src/test/regress/expected/json_1.out @@ -958,3 +958,32 @@ select json '{ "a": "null \u0000 escape" }' ->> 'a' as not_unescaped; null \u0000 escape (1 row) +--json_typeof() function +select value, json_typeof(value) + from (values (json '123.4'), + (json '-1'), + (json '"foo"'), + (json 'true'), + (json 'false'), + (json 'null'), + (json '[1, 2, 3]'), + (json '[]'), + (json '{"x":"foo", "y":123}'), + (json '{}'), + (NULL::json)) + as data(value); + value | json_typeof +----------------------+------------- + 123.4 | number + -1 | number + "foo" | string + true | boolean + false | boolean + null | null + [1, 2, 3] | array + [] | array + {"x":"foo", "y":123} | object + {} | object + | +(11 rows) + diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql index 4c4c6958bb..ff7675fad0 100644 --- a/src/test/regress/sql/json.sql +++ b/src/test/regress/sql/json.sql @@ -310,3 +310,18 @@ select json '{ "a": "\ude04X" }' -> 'a'; -- orphan low surrogate select json '{ "a": "the Copyright \u00a9 sign" }' ->> 'a' as correct_in_utf8; select json '{ "a": "dollar \u0024 character" }' ->> 'a' as correct_everywhere; select json '{ "a": "null \u0000 escape" }' ->> 'a' as not_unescaped; + +--json_typeof() function +select value, json_typeof(value) + from (values (json '123.4'), + (json '-1'), + (json '"foo"'), + (json 'true'), + (json 'false'), + (json 'null'), + (json '[1, 2, 3]'), + (json '[]'), + (json '{"x":"foo", "y":123}'), + (json '{}'), + (NULL::json)) + as data(value); -- 2.40.0