Re: JSON manipulation functions

On Thu, May 13, 2010 at 9:47 PM, Joseph Adams
<joeyadams3(dot)14159(at)gmail(dot)com> wrote:
> The following function returns the type of any JSON value.
>
> json_type as enum ('null', 'string', 'number', 'boolean', 'object', 'array')
> json_type(json) returns json_type

Seems reasonable.

> Would it be a bad idea to give an enum and a function the same name
> (which appears to be allowed by PostgreSQL) ?  If so, json_type(json)
> could be json_typeof(json) or something instead.

No, I think that's a fine idea.

> I thought about having predicates like IS STRING and IS NUMBER,
> similar to the IS DOCUMENT predicate used for XML.  However, a major
> problem with that approach is that it could lead to confusion
> involving IS NULL.  By my understanding, the JSON datatype will just
> be a specialization of TEXT (it just validates the input).  Like TEXT,
> a JSON string can be 'null'.  'null'::JSON is not NULL.  Bear in mind
> that json_to_*('null') is NULL, though.

Even aside from the possible semantic confusion, I don't think that we
should make any changes to our core grammar (gram.y) to support JSON.
It's not really necessary and it's better not to add extra stuff to
the grammar unless we really need it.

> I also thought about having a series of json_is_* functions.  I don't
> think it's a bad idea, but I think json_type is a better solution.

I agree.

> == text/number/boolean conversion ==
>
> These functions each convert a non-compound JSON value to its
> respective return type.  Run-time type checking is performed; a
> conversion will throw an error if the input JSON is not the correct
> type.  If the JSON value is 'null', then the return value will be
> NULL.
>
> json_to_text(json) returns text
> json_to_number(json) returns numeric
> json_to_bool(json) returns boolean

Can '3' be converted to a number, or only if it's written without the quotes?

> These functions convert values to JSON.  Passing NULL to any of the
> functions below will return 'null':
>
> text_to_json(text) returns json
> number_to_json(numeric) returns json
> bool_to_json(boolean) returns json
>
> There could be generic value_to_json(any), but not a
> json_to_value(json) function.  See
> http://archives.postgresql.org/pgsql-hackers/2010-04/msg00321.php for
> more details.

Seems OK.

> Conversion to/from number or boolean can also be achieved with
> casting.  Note well that '"string"'::JSON::TEXT is '"string"', not the
> string's actual value.  json_to_text is needed for this conversion.
> For this reason, casting JSON might seem like something to recommend
> against.  However, IMHO, casting numbers and booleans to/from JSON is
> fine and dandy; the paragraphs below give some weight to this.
>
> I originally considered making json_to_number and number_to_json work
> with TEXT instead of NUMERIC.  However, as Tom Lane pointed out in the
> above link, "Forcing people to insert explicit coercions from text
> isn't going to be particularly convenient to use.".  Nevertheless,
> NUMERIC introduces a problem.  For instance, if you say:
>
> SELECT '-1e-38'::NUMERIC;
>
> This conversion knocks out the scientific notation and produces a
> 41-character string.  I seriously doubt that all outside applications
> will handle 41-character numbers correctly.

Maybe not, but I don't think it's your problem to fix it if they
don't. If people want to have fine-grained control over the JSON that
gets generated, they can always generate the value as text and cast it
to JSON.

> Perhaps there should be individual functions for specific data types,
> or maybe just a handful for particular cases.  There might be
> json_to_int, json_to_float, and json_to_numeric.  In any case,
> converting to/from number types can be achieved quite easily with
> casting.

Personally I'd go with just json_to_numeric for starters. We can
always add the others if and when it's clear that they are useful.

> == array/object conversion ==
>
> The json_object function converts a tuple to a JSON object.  If there
> are duplicate column names, there will be duplicate keys in the
> resulting JSON object.
>
> json_object([content [AS name] [, ...]]) returns json

Seems good.

> Likewise, the json_array function converts a tuple to a JSON array.
> Column names are ignored.
>
> json_array([content [AS name] [, ...]]) returns json

I think this is pointless and should be omitted.

> The json_agg function reduces a set of JSON values to a single array
> containing those values.
>
> aggregate json_agg(json) returns json

Very useful, I like that.

> json_object and json_agg can be used together to convert an entire
> result set to one JSON array:
>
> SELECT json_agg(json_object(*)) FROM tablename;

Spiffy.

> json_keys gets the keys of a JSON object as a set.
>
> json_keys(json) returns setof text

I would tend to make this return text[] rather than SETOF text.

> json_values gets the values of a JSON object or the iems of a JSON
> array as a set.
>
> json_values(json) returns setof json

Similarly I would make this return json[].

> Note that all JSON slicing and splicing operations retain the original
> formatting of JSON content.

Good.

> == Miscellaneous ==
>
> The features below would be nice, but will probably not be regarded as
> required for this Google Summer of Code project to be considered
> complete.
>
> json_cleanup accepts a superset of JSON and, if it can, cleans it up
> and returns a valid JSON string.  This superset of JSON supports the
> following extra features:
>
>  * Comments:
>   - Single-line comments with // and #
>   - C-style comments: /* comment */
>  * Unquoted object keys: {key: "value"}
>  * Single quote strings: 'single quotes; "double quotes" do not need
> to be escaped here'
>  * Single quote escape allowed: "It\'s allowed, but it's not necessary"
>  * Lax number format (+ sign allowed; digits may be omitted on one
> side of the decimal point).
>
> json_cleanup(text) returns json
>
> Example:
>
> SELECT json_cleanup('{/*comment*/number: +.3}');
> -- Result is '{"number": 0.3}'::JSON

This might be nice to have, or not. I don't think it's a big deal either way.

> -> retrieves an item of a JSON object by key.  If the object has
> duplicate keys, the first key listed will be retrieved.  Example:
>
> SELECT ('{"foo": 50, "foo": 100, "bar": "string"}'::JSON)->"foo"
> -- Result is '50'::JSON
>
> [] retrieves a value of a JSON array/object by (one-based) index.  In
> other words, value[n] is equivalent to selecting the nth row of
> json_values(value) (provided value is of type JSON).  Examples:
>
> SELECT ('[1,2,3,4]'::JSON)[3]
> -- Result is '3'::JSON
>
> SELECT ('{"a": 1, "b": 2, "c": 3, "d": 4}'::JSON)[3]
> -- Result is '3'::JSON

I think some kind of array deference and object deference mechanism is
absolutely, positively 100% required. I don't know whether the
particular syntax you've proposed here is best or whether we should
pick another syntax or just use function notation, but I think we
definitely need *something*.

I also think we need a function called something like json_length()
which returns the length of a list or the number of keys in an object.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company