Re: Proposal: Add JSON support

Joseph Adams <joeyadams3(dot)14159(at)gmail(dot)com> writes:
> Perhaps there could be conversion functions. E.g.:

Yeah, that's what I was thinking about.

> json_to_string('"hello"') yields 'hello'
> json_to_number('3.14159') yields '3.14159' as text
> (it is up to the user to cast it to the number type s/he wants)
> json_to_bool('true') yields TRUE
> json_to_null('null') yields NULL, json_null('nonsense') fails

> string_to_json('hello') yields '"hello"' as JSON
> number_to_json(3.14159) yields '3.14159' as JSON
> bool_to_json(TRUE) yields 'true' as JSON
> null_to_json(NULL) yields 'null' as JSON (kinda useless)

The null cases seem a bit useless. What might be helpful is to
translate JSON 'null' to and from SQL NULL in each of the other
conversions, in addition to their primary capability.

I'd go with using NUMERIC as the source/result type for the numeric
conversions. Forcing people to insert explicit coercions from text
isn't going to be particularly convenient to use.

> I wonder if these could all be reduced to two generic functions, like
> json_to_value and value_to_json.

value_to_json(any) might work, but the other way could not; and it seems
better to keep some symmetry between the directions.

regards, tom lane