JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ?

Started by Hannu Krosingalmost 14 years ago38 messageshackers

hannu@tm.ee

almost 14 years ago

Hi hackers

After playing around with array_to_json() and row_to_json() functions a
bit it I have a question - why do we even have 2 variants *_to_json()

Collapsing array_to_json() and row_to_json() into just to_json()
----------------------------------------------------------------

As the functionality is not yet release maybe we could still rethink the
interface and have just one which can deal with all types :

to_json(any) returns json

the current two versions have to call the "any" variant internally
anyhow, to convert individual field values, so why not just expose the
full functionality as a single to_json() function

You almost can get the "any" functionality now by wrapping the type in
an array and afterwards strip outermost [] from the result.

Is there any good reason why not expose any_to_json() it directly ?

CREATE OR REPLACE FUNCTION to_json(obj anyelement)
RETURNS json AS $$
BEGIN
RETURN substring(array_to_json(array[obj])::text FROM '^\[(.*)\]$');
END;
$$ LANGUAGE plpgsql;

hannu=# select to_json('1'::text) ;
to_json
---------
"1"
(1 row)

hannu=# select to_json(test) from test limit 2;
to_json
-------------------------------------------------------------------
{"id":9,"data":"testdata","tstamp":"2012-05-01 09:44:50.175189"}
{"id":10,"data":"testdata","tstamp":"2012-05-01 09:45:50.260276"}
(2 rows)

Maybe we can remove the *_to_json(functions completely :)
---------------------------------------------------------

As a separate note, could we go even further and fold all this
functionality into an universal cast, so that attaching ::json to any
object will automagically work ?

Removing current limitation of PL/pgSQL
----------------------------------------

The above plpgsql definition of to_json() does not currently work with
anonymous records defined inline or arrays of such records, but only
because Pl/PgSQL functions currently don't accept these types

hannu=# select to_json(s) from (select 1::int as i, 't'::text as t union
select 2,'x')s;
ERROR: PL/pgSQL functions cannot accept type record
CONTEXT: compilation of PL/pgSQL function "to_json" near line 1

I think PL/pgSQL could now start accepting such records as the wrinkles
which made it hard to (recursively) get the needed info for anonymous
records were ironed out when developing the *_to_json() functions so all
of it just works.

IIRC some of this needed improving data available in core, and was not
just extra surgery done directly inside the *_to_json() functions.

hannu=# select row_to_json(s) from (select 1::int as i, 't'::text as t
union select 2,'x')s;
row_to_json
-----------------
{"i":1,"t":"t"}
{"i":2,"t":"x"}
(2 rows)

and even

hannu=# select row_to_json(s) from (select 1::int as i, (select z
from(select 2::int as j, 'x'::text as x)z) as t union select 2,null)s;
row_to_json
-----------------------------
{"i":1,"t":{"j":2,"x":"x"}}
{"i":2,"t":null}
(2 rows)

--
-------
Hannu Krosing
PostgreSQL Unlimited Scalability and Performance Consultant
2ndQuadrant Nordic
PG Admin Book: http://www.2ndQuadrant.com/books/