PATCH: Add hstore_to_json()

On Wed, Dec 16, 2009 at 2:28 PM, David E. Wheeler <david@kineticode.com> wrote:

I just realized that this was easy to do, and despite my complete lack of C skillz was able to throw this together in a couple of hours. It might be handy to some, though the possible downsides are:

* No json_to_hstore().
* Leads to requests for hstore_to_yaml(), hstore_to_xml(), etc.
* Andrew Gierth said “no” when I suggested it.

But it's kind of handy, too. Thoughts?

I like it. The regression tests you've added seem to cover a lot of
cases that aren't really different without covering some that are
probably worth trying, like multiple key/value pairs. Also, the
comment in the function you've added looks like a cut-and-paste from
somewhere else, which might not be the best way to document. With
regard to the underlying issue, why can't we just use a StringInfo and
forget about it?

Also, your indentation is not entirely consistent. If this gets
consensus, that will have to be fixed before it can be committed, so
it would be nice if you could do that rather than leaving it for the
eventual committer.

...Robert

On Dec 16, 2009, at 2:45 PM, Robert Haas wrote:

I like it. The regression tests you've added seem to cover a lot of
cases that aren't really different without covering some that are
probably worth trying, like multiple key/value pairs. Also, the
comment in the function you've added looks like a cut-and-paste from
somewhere else, which might not be the best way to document. With
regard to the underlying issue, why can't we just use a StringInfo and
forget about it?

Dunno. I just duped hstore_out(). I agree there should be more edge cases.

Also, your indentation is not entirely consistent. If this gets
consensus, that will have to be fixed before it can be committed, so
it would be nice if you could do that rather than leaving it for the
eventual committer.

The indentation is also largely copied; wouldn't pg_indent fix it?

Best,

David

On Wed, Dec 16, 2009 at 5:58 PM, David E. Wheeler <david@kineticode.com> wrote:

On Dec 16, 2009, at 2:45 PM, Robert Haas wrote:

I like it.  The regression tests you've added seem to cover a lot of
cases that aren't really different without covering some that are
probably worth trying, like multiple key/value pairs.  Also, the
comment in the function you've added looks like a cut-and-paste from
somewhere else, which might not be the best way to document.  With
regard to the underlying issue, why can't we just use a StringInfo and
forget about it?

Dunno. I just duped hstore_out(). I agree there should be more edge cases.

Also, your indentation is not entirely consistent.  If this gets
consensus, that will have to be fixed before it can be committed, so
it would be nice if you could do that rather than leaving it for the
eventual committer.

The indentation is also largely copied; wouldn't pg_indent fix it?

Yeah, eventually, but that's not really a great way of dealing with it.

http://archives.postgresql.org/pgsql-hackers/2009-12/msg01208.php

...Robert

On ons, 2009-12-16 at 11:28 -0800, David E. Wheeler wrote:

I just realized that this was easy to do, and despite my complete lack of C skillz was able to throw this together in a couple of hours. It might be handy to some, though the possible downsides are:

* No json_to_hstore().
* Leads to requests for hstore_to_yaml(), hstore_to_xml(), etc.
* Andrew Gierth said “no” when I suggested it.

But it's kind of handy, too. Thoughts?

Should we create a json type before adding all kinds of json formatted
data? Or are we content with json as text?

On Dec 18, 2009, at 4:49 AM, Peter Eisentraut wrote:

Should we create a json type before adding all kinds of json formatted
data? Or are we content with json as text?

json_data_type++

D

On Fri, Dec 18, 2009 at 11:32 AM, David E. Wheeler <david@kineticode.com> wrote:

On Dec 18, 2009, at 4:49 AM, Peter Eisentraut wrote:

Should we create a json type before adding all kinds of json formatted
data?  Or are we content with json as text?

json_data_type++

What would that do for us?

I'm not opposed to it, but it seems like the more important thing
would be to provide functions or operators that can do things like
extract an array, extract a hash key, identify whether something is a
hash, list, or scalar, etc.

...Robert

On Dec 18, 2009, at 8:51 AM, Robert Haas wrote:

What would that do for us?

I'm not opposed to it, but it seems like the more important thing
would be to provide functions or operators that can do things like
extract an array, extract a hash key, identify whether something is a
hash, list, or scalar, etc.

Such things would be included with such a data type, no?

Best,

David

Robert Haas wrote:

On Fri, Dec 18, 2009 at 11:32 AM, David E. Wheeler <david@kineticode.com> wrote:

On Dec 18, 2009, at 4:49 AM, Peter Eisentraut wrote:

Should we create a json type before adding all kinds of json formatted
data? Or are we content with json as text?

json_data_type++

What would that do for us?

I'm not opposed to it, but it seems like the more important thing
would be to provide functions or operators that can do things like
extract an array, extract a hash key, identify whether something is a
hash, list, or scalar, etc.

In principle it's not a bad idea to have a JSON type for several
reasons. First, it's a better match than hstore for serializing an
arbitrary tuple, because unlike hstore it can have nested arrays and
composites, just as tuples can. Second, it might well be very useful if
we could easily return results as JSON to AJAX applications, which are
increasingly becoming the norm. And similarly we might be able to reduce
application load if Postgres could perform operations on JSON, rather
than having to return it all to the client to process.

I think it would be useful if someone produced a JSON module as, say, a
pgFoundry project, to start with, and we would then be better able to
assess its usefulness. An interesting question would be how one might
sanely index such things.

You're correct that we don't necessarily need a new type, we could just
make it text and have a bunch of operations, but that seems to violate
the principle of data type abstraction a bit. If the operations can be
sure that the object is valid JSON they could skip a bunch of sanity
checks that they would otherwise need to do if just handed an arbitrary
piece of text.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

You're correct that we don't necessarily need a new type, we could just
make it text and have a bunch of operations, but that seems to violate
the principle of data type abstraction a bit.

I think the relevant precedent is that we have an xml type. While I
surely don't want to follow the SQL committee's precedent of inventing
a ton of special syntax for xml support, it might be useful to look at
that for suggestions of what functionality would be useful for a json
type.

[ I can already hear somebody insisting on a yaml type :-( ]

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

You're correct that we don't necessarily need a new type, we could just
make it text and have a bunch of operations, but that seems to violate
the principle of data type abstraction a bit.

I think the relevant precedent is that we have an xml type. While I
surely don't want to follow the SQL committee's precedent of inventing
a ton of special syntax for xml support, it might be useful to look at
that for suggestions of what functionality would be useful for a json
type.

[ I can already hear somebody insisting on a yaml type :-( ]

Now that's a case where I think a couple of converter functions at most
should meet the need.

cheers

andrew

Andrew Dunstan wrote:

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

You're correct that we don't necessarily need a new type, we could just
make it text and have a bunch of operations, but that seems to violate
the principle of data type abstraction a bit.

I think the relevant precedent is that we have an xml type. While I
surely don't want to follow the SQL committee's precedent of inventing
a ton of special syntax for xml support, it might be useful to look at
that for suggestions of what functionality would be useful for a json
type.

[ I can already hear somebody insisting on a yaml type :-( ]

Now that's a case where I think a couple of converter functions at most
should meet the need.

I can see this feature getting web developers more excited about
Postgres.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

[ I can already hear somebody insisting on a yaml type :-( ]

Now that's a case where I think a couple of converter functions at most
should meet the need.

Well, actually, now that you mention it: how much of a json type would
be duplicative of the xml stuff? Would it be sufficient to provide
json <-> xml converters and let the latter type do all the heavy lifting?
(If so, this patch ought to be hstore_to_xml instead.)

regards, tom lane

Tom Lane escribi�:

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

[ I can already hear somebody insisting on a yaml type :-( ]

Now that's a case where I think a couple of converter functions at most
should meet the need.

Well, actually, now that you mention it: how much of a json type would
be duplicative of the xml stuff? Would it be sufficient to provide
json <-> xml converters and let the latter type do all the heavy lifting?
(If so, this patch ought to be hstore_to_xml instead.)

But then there's the matter of overhead: how much would be wasted by
transforming to XML, and then parsing the XML back to transform to JSON?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane escribi�:

Well, actually, now that you mention it: how much of a json type would
be duplicative of the xml stuff? Would it be sufficient to provide
json <-> xml converters and let the latter type do all the heavy lifting?
(If so, this patch ought to be hstore_to_xml instead.)

But then there's the matter of overhead: how much would be wasted by
transforming to XML, and then parsing the XML back to transform to JSON?

Well, that would presumably happen only when sending data to or from the
client. It's not obvious that it would be much more expensive than the
syntax checking you'd have to do anyway.

If there's some reason to think that operating on json data would be
much less expensive than operating on xml, there might be a case for
having two distinct sets of operations internally, but I haven't heard
anybody make that argument.

regards, tom lane

On Fri, Dec 18, 2009 at 3:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane escribió:

Well, actually, now that you mention it: how much of a json type would
be duplicative of the xml stuff?  Would it be sufficient to provide
json <-> xml converters and let the latter type do all the heavy lifting?
(If so, this patch ought to be hstore_to_xml instead.)

But then there's the matter of overhead: how much would be wasted by
transforming to XML, and then parsing the XML back to transform to JSON?

Well, that would presumably happen only when sending data to or from the
client.  It's not obvious that it would be much more expensive than the
syntax checking you'd have to do anyway.

If there's some reason to think that operating on json data would be
much less expensive than operating on xml, there might be a case for
having two distinct sets of operations internally, but I haven't heard
anybody make that argument.

One problem is that there is not a single well-defined mapping between
these types. I would say generally that XML and YAML both have more
types of constructs than JSON. The obvious ways of translating an
arbitrary XML document to JSON are likely not to be what people want
in particular cases.

I think the performance argument is compelling, too, but we can't even
try benchmarking it unless we can define what we're even talking
about.

...Robert

+1 for such a feature, simply to avoid the need of
writing a hstore-parser (which wasn't too bad
to write, but it felt unnecessary). Doesn't
matter to me if it's hstore-to-json or hstore-to-xml
or hstore-to-yaml. Just something that parsers are
readily available for.

Heck, I wouldn't mind if hstore moved to using any one
of those for it's external representations by default.

Tom Lane wrote:

a ton of special syntax for xml support, ...a json type...
[ I can already hear somebody insisting on a yaml type :-( ]

If these were CPAN-like installable modules, I'd hope
there would be eventually. Don't most languages and
platforms have both YAML and JSON libraries? Yaml's
user-defined types are an example of where this might
be useful eventually.

Tom Lane wrote:

Well, actually, now that you mention it: how much of a json type would
be duplicative of the xml stuff? Would it be sufficient to provide
json <-> xml converters and let the latter type do all the heavy lifting?

I imagine eventually a JSON type could validate fields using
JSON Schema. But that's drifting away from hstore.

(If so, this patch ought to be hstore_to_xml instead.)

Doesn't matter to me so long as it's any format with readily
available parsers.

On fre, 2009-12-18 at 11:51 -0500, Robert Haas wrote:

On Fri, Dec 18, 2009 at 11:32 AM, David E. Wheeler <david@kineticode.com> wrote:

On Dec 18, 2009, at 4:49 AM, Peter Eisentraut wrote:

Should we create a json type before adding all kinds of json formatted
data? Or are we content with json as text?

json_data_type++

What would that do for us?

At the moment it would be more of a placeholder, because if we later
decide to add full-blown JSON-constructing and -destructing
functionality, it would be difficult to change the signatures of all the
existing functionality.

On Fri, Dec 18, 2009 at 4:39 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On fre, 2009-12-18 at 11:51 -0500, Robert Haas wrote:

On Fri, Dec 18, 2009 at 11:32 AM, David E. Wheeler <david@kineticode.com> wrote:

On Dec 18, 2009, at 4:49 AM, Peter Eisentraut wrote:

Should we create a json type before adding all kinds of json formatted
data?  Or are we content with json as text?

json_data_type++

What would that do for us?

At the moment it would be more of a placeholder, because if we later
decide to add full-blown JSON-constructing and -destructing
functionality, it would be difficult to change the signatures of all the
existing functionality.

Good thought.

...Robert

Robert Haas wrote:

On Fri, Dec 18, 2009 at 3:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane escribi�:

Well, actually, now that you mention it: how much of a json type would
be duplicative of the xml stuff? Would it be sufficient to provide
json <-> xml converters and let the latter type do all the heavy lifting?
(If so, this patch ought to be hstore_to_xml instead.)

But then there's the matter of overhead: how much would be wasted by
transforming to XML, and then parsing the XML back to transform to JSON?

Well, that would presumably happen only when sending data to or from the
client. It's not obvious that it would be much more expensive than the
syntax checking you'd have to do anyway.

If there's some reason to think that operating on json data would be
much less expensive than operating on xml, there might be a case for
having two distinct sets of operations internally, but I haven't heard
anybody make that argument.

One problem is that there is not a single well-defined mapping between
these types. I would say generally that XML and YAML both have more
types of constructs than JSON. The obvious ways of translating an
arbitrary XML document to JSON are likely not to be what people want
in particular cases.

Right. XML semantics are richer, as I pointed out when we were
discussing the various EXPLAIN formats.

I think the performance argument is compelling, too, but we can't even
try benchmarking it unless we can define what we're even talking
about.

Yes, there is indeed reason to think that JSON processing, especially
parsing, will be more efficient, and I suspect we can provide ways of
accessing the data that are lots faster than XPath. JSON is designed to
be lightweight, XML is not.

Mind you, the XML processing is not too bad - I have been working much
of the last few months on a large custom billing system which produces
XML output to create paper/online invoices from, and the XML
construction is one of the fastest parts of the whole system.

cheers

andrew