Skip site navigation (1) Skip section navigation (2)

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

From: Hannu Krosing <hannu(at)2ndQuadrant(dot)com>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>, PavelStehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ?
Date: 2012-05-01 12:02:40
Message-ID: 1335873760.3106.66.camel@hvost (view raw or flat)
Thread:
Lists: pgsql-hackers
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/


Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2012-05-01 12:21:24
Subject: proposal: additional error fields
Previous:From: Simon RiggsDate: 2012-05-01 07:38:11
Subject: Re: Future In-Core Replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group