On Wed, 2012-05-02 at 12:06 -0700, Andrew Dunstan wrote:
> On Wed, May 2, 2012 at 2:29 AM, Hannu Krosing <ha...@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

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


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);
 [{"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:

    {"id":1,"hstore":"\"bar\"=>\"baz\", \"foo\"=>\"1\""}
   (1 row)

   but this
    {"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/

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to