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

On Wed, 2012-05-02 at 12:06 -0700, Andrew Dunstan wrote:
>
>
> On Wed, May 2, 2012 at 2:29 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)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-01 09: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 returns their
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 automatically get
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 "page width" 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_rows json) 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/