diff -r 6dad7dad36a6 doc/src/sgml/func.sgml --- a/doc/src/sgml/func.sgml Sat Jul 27 15:00:58 2013 -0400 +++ b/doc/src/sgml/func.sgml Mon Jul 29 15:06:37 2013 +0800 @@ -10040,7 +10040,7 @@ 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"} @@ -10054,7 +10054,7 @@ 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 @@ -10137,7 +10137,7 @@ 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]]') @@ -10150,6 +10150,67 @@ + + + + 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 + + + + + json_is_object + + json_is_object(json) + + boolean + + Check if the outermost JSON value is an object. + + json_is_object('{"f1":true}') + 't' + + + + + json_is_array + + json_is_array(json) + + boolean + + Check if the outermost JSON value is an array. + + json_is_array('[1,2,3]') + 't' + + + + + json_is_scalar + + json_is_scalar(json) + + boolean + + Check if the outermost JSON value is a scalar. (Strings, numbers, and the + true, false, and null literals are all + scalar values.) + + json_is_scalar('"hello world"') + 't' + @@ -10182,6 +10243,14 @@ + + + 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 -r 6dad7dad36a6 src/backend/utils/adt/json.c --- a/src/backend/utils/adt/json.c Sat Jul 27 15:00:58 2013 -0400 +++ b/src/backend/utils/adt/json.c Mon Jul 29 15:06:37 2013 +0800 @@ -1826,3 +1826,166 @@ } 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)); +} + +/* + * SQL function json_is_object(json) -> boolean + * + * Operates in a similar manner to json_typeof(). + */ +Datum +json_is_object(PG_FUNCTION_ARGS) +{ + text *json = PG_GETARG_TEXT_P(0); + + JsonLexContext *lex = makeJsonLexContext(json, false); + JsonTokenType tok; + int is_object; + + /* 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: + is_object = 1; + break; + case JSON_TOKEN_ARRAY_START: + case JSON_TOKEN_STRING: + case JSON_TOKEN_NUMBER: + case JSON_TOKEN_TRUE: + case JSON_TOKEN_FALSE: + case JSON_TOKEN_NULL: + is_object = 0; + break; + default: + elog(ERROR, "unexpected json token: %d", tok); + } + + PG_RETURN_BOOL(is_object); +} + +/* + * SQL function json_is_array(json) -> boolean + * + * Operates in a similar manner to json_typeof(). + */ +Datum +json_is_array(PG_FUNCTION_ARGS) +{ + text *json = PG_GETARG_TEXT_P(0); + + JsonLexContext *lex = makeJsonLexContext(json, false); + JsonTokenType tok; + int is_array; + + /* Lex exactly one token from the input and check its type. */ + json_lex(lex); + tok = lex_peek(lex); + switch (tok) + { + case JSON_TOKEN_ARRAY_START: + is_array = 1; + break; + case JSON_TOKEN_OBJECT_START: + case JSON_TOKEN_STRING: + case JSON_TOKEN_NUMBER: + case JSON_TOKEN_TRUE: + case JSON_TOKEN_FALSE: + case JSON_TOKEN_NULL: + is_array = 0; + break; + default: + elog(ERROR, "unexpected json token: %d", tok); + } + + PG_RETURN_BOOL(is_array); +} + +/* + * SQL function json_is_scalar(json) -> boolean + * + * Operates in a similar manner to json_typeof(). + */ +Datum +json_is_scalar(PG_FUNCTION_ARGS) +{ + text *json = PG_GETARG_TEXT_P(0); + + JsonLexContext *lex = makeJsonLexContext(json, false); + JsonTokenType tok; + int is_scalar; + + /* 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: + case JSON_TOKEN_ARRAY_START: + is_scalar = 0; + break; + case JSON_TOKEN_STRING: + case JSON_TOKEN_NUMBER: + case JSON_TOKEN_TRUE: + case JSON_TOKEN_FALSE: + case JSON_TOKEN_NULL: + is_scalar = 1; + break; + default: + elog(ERROR, "unexpected json token: %d", tok); + } + + PG_RETURN_BOOL(is_scalar); +} diff -r 6dad7dad36a6 src/include/catalog/pg_proc.h --- a/src/include/catalog/pg_proc.h Sat Jul 27 15:00:58 2013 -0400 +++ b/src/include/catalog/pg_proc.h Mon Jul 29 15:06:37 2013 +0800 @@ -4150,6 +4150,14 @@ 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"); +DATA(insert OID = 3969 ( json_is_object PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 16 "114" _null_ _null_ _null_ _null_ json_is_object _null_ _null_ _null_ )); +DESCR("is the json value an object"); +DATA(insert OID = 3970 ( json_is_array PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 16 "114" _null_ _null_ _null_ _null_ json_is_array _null_ _null_ _null_ )); +DESCR("is the json value an array"); +DATA(insert OID = 3971 ( json_is_scalar PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 16 "114" _null_ _null_ _null_ _null_ json_is_scalar _null_ _null_ _null_ )); +DESCR("is the json value neither an object nor an array"); /* 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 -r 6dad7dad36a6 src/include/utils/json.h --- a/src/include/utils/json.h Sat Jul 27 15:00:58 2013 -0400 +++ b/src/include/utils/json.h Mon Jul 29 15:06:37 2013 +0800 @@ -33,6 +33,11 @@ extern void escape_json(StringInfo buf, const char *str); +extern Datum json_typeof(PG_FUNCTION_ARGS); +extern Datum json_is_object(PG_FUNCTION_ARGS); +extern Datum json_is_array(PG_FUNCTION_ARGS); +extern Datum json_is_scalar(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 -r 6dad7dad36a6 src/test/regress/expected/json.out --- a/src/test/regress/expected/json.out Sat Jul 27 15:00:58 2013 -0400 +++ b/src/test/regress/expected/json.out Mon Jul 29 15:06:37 2013 +0800 @@ -962,3 +962,23 @@ null \u0000 escape (1 row) +--json_typeof() and the json_is_*() functions +select value, json_typeof(value), json_is_object(value), json_is_array(value), json_is_scalar(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 | json_is_object | json_is_array | json_is_scalar +----------------------+-------------+----------------+---------------+---------------- + 123.4 | number | f | f | t + -1 | number | f | f | t + "foo" | string | f | f | t + true | boolean | f | f | t + false | boolean | f | f | t + null | null | f | f | t + [1, 2, 3] | array | f | t | f + [] | array | f | t | f + {"x":"foo", "y":123} | object | t | f | f + {} | object | t | f | f + | | | | +(11 rows) + diff -r 6dad7dad36a6 src/test/regress/expected/json_1.out --- a/src/test/regress/expected/json_1.out Sat Jul 27 15:00:58 2013 -0400 +++ b/src/test/regress/expected/json_1.out Mon Jul 29 15:06:37 2013 +0800 @@ -958,3 +958,23 @@ null \u0000 escape (1 row) +--json_typeof() and the json_is_*() functions +select value, json_typeof(value), json_is_object(value), json_is_array(value), json_is_scalar(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 | json_is_object | json_is_array | json_is_scalar +----------------------+-------------+----------------+---------------+---------------- + 123.4 | number | f | f | t + -1 | number | f | f | t + "foo" | string | f | f | t + true | boolean | f | f | t + false | boolean | f | f | t + null | null | f | f | t + [1, 2, 3] | array | f | t | f + [] | array | f | t | f + {"x":"foo", "y":123} | object | t | f | f + {} | object | t | f | f + | | | | +(11 rows) + diff -r 6dad7dad36a6 src/test/regress/sql/json.sql --- a/src/test/regress/sql/json.sql Sat Jul 27 15:00:58 2013 -0400 +++ b/src/test/regress/sql/json.sql Mon Jul 29 15:06:37 2013 +0800 @@ -310,3 +310,9 @@ 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() and the json_is_*() functions +select value, json_typeof(value), json_is_object(value), json_is_array(value), json_is_scalar(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);