Re: JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ? - Mailing list pgsql-hackers

On Wed, 2012-05-02 at 12:06 -0700, Andrew Dunstan wrote:
> 
> 
> On Wed, May 2, 2012 at 2:29 AM, Hannu Krosing <hannu@2ndquadrant.com>
> wrote:
>         
>         
>         
>         I don't object to row_to_json() and array_to_json() functions
>         being
>         there as a convenience and as the two "official" functions
>         guaranteed to
>         return "JSON text".
>         
> 
> So given that do we do anything about this now, or wait till 9.3?

Sorry for missing this mail, followed this only on list
I hope it is ok to CC this back to list



I'd like the json support in 9.2 updated as follows


Generic to_json(...) returning a "JSON value"
=============================================

we should have a generic to_json(...) both for eas and use and for easy
extensibility, as explained below.

to_json(...) should work for all types, returning a "json value" similar
to what current json_in does, but for all types, not just cstring.

We could keep row_to_json() and array_to_json() as official json-text
returning functions 


Configurable and extensible to_json()
======================================

When working on structured types, always the first try for getting an
element-as-json should be running to_json(element) and only if this
fails fall back to current "use text representation" code.

this enables two important things

1) configurable json-coding of values not explicitly supported by
standard

You can read about an attempt to standardise json-date formart here

http://weblogs.asp.net/bleroy/archive/2008/01/18/dates-and-json.aspx .

By allowing developers just to define their own to_json(date) function
we give them the power do decide which one to use. And if we honour
search_path when looking up the to_json() functions, then they can even
choose to have different conventions for different applications.

2) flexibility in adding support for extension types, like representing
hstore as object/dict by just providing the to_json(hstore, ...)
functions in hstore extension

Pretty-printing
===============

If we were to support prettyprinting of anything more complex than
single level structs (record or array), then we need to pass "ident"
into the to_json() function

my recommendation would be to have the signature 

to_json(datum any, ident int)

with ident = NULL meaning no prettyprint , ident =0 meaninf top level,
or "starting at left margin" and anything else meaning the amount of
spaces needed to be added to the beginning of all rows exept the first
one, for example the query 

hannu=# select array_to_json(array(select test from test limit 2),true);                           array_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"}]
 
(1 row)

could return this:

[{'data': 'testdata', 'id': 9, 'tstamp': '2012-05-01 09:44:50.175189'},{'data': 'testdata', 'id': 10, 'tstamp':
'2012-05-0109:45:50.260276'}]
 

if it would call to_json(row, 1) for getting each row prettyprinted with
ident 1


Getting a record _from_ json()
==============================

JSON support would be much more useful if we supported the function of
converting the other way as well, that is from json to record

The best model easy to support seems to be what Merlin suggested, that
is the populate_record(record,hstore) function and corresponding #=
operator (see: http://www.postgresql.org/docs/9.1/static/hstore.html)



The complete plan for 9.2 once more
===================================

What is needed to nicely round up a  simple and powerful json type is

1. the "json" TYPE
  an agreement tha json type represents a "JSON value" (this is what   json_in currently does)


2. json output

2.1 doing the right thing with internal values
  to_json() function for converting to this "JSON value" for any type.  default implementation for 'non-json' types
returnstheir   postgresql textual representation in double quotes    (date -> "2012-05-01 09:45:50.260276"
 
  structured types use to_json() for getting values internally,   so that by defining to_json(hstore) you can
automaticallyget   hstore represented in javascript object or dictionary representation
 
  hannu=# select row_to_json(r)           from (select 1::int id, '"foo"=>1, "bar"=>"baz"'::hstore)r;
  should not return this:
                       row_to_json                        ------------------------------------------------------
{"id":1,"hstore":"\"bar\"=>\"baz\",\"foo\"=>\"1\""}  (1 row)
 
  but this                       row_to_json
------------------------------------------------------  {"id":1,"hstore":{"bar": "baz", "foo":"1"}}  (1 row)
 

2.1 getting the pretty-printing right for structured types 
  to_json(any, indent) functions for getting recursive prettyprinting.  we might also need another argument telling the
"pagewidth" we want  to pretty print to.
 


3. json input for records and arrays

3.1 json row to record
  a function to convert from json to record, so that we can use json  also as input format. modeled after
populate_record(record,hstore) from http://www.postgresql.org/docs/9.1/static/hstore.html 
 
  one json_to_row should be enough, as we can use

3.2 json array to json rows 
  if we also want to use input arrays , say send all invoice rows as   a json array, we could add a
unnest_json(json_array_of_rowsjson) returns table(json_row json)    function and then do the inputting as
 
  insert into invoice_row   select json_to_row(null::invoice_row, json_row)  from (select json_row as
unnest_json(json_array_of_rows))s;
 


> cheers
> 
> andrew

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