JSON manipulation functions
in [PgSql]
|
Prev: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
Next: [HACKERS] fillfactor gets set to zero for toast tables
From: Magnus Hagander on 25 May 2010 08:38 On Tue, May 25, 2010 at 12:57, Robert Haas <robertmhaas(a)gmail.com> wrote: > On Tue, May 25, 2010 at 5:37 AM, Joseph Adams > <joeyadams3.14159(a)gmail.com> wrote: >> I started a wiki article for brainstorming the JSON API: >> http://wiki.postgresql.org/wiki/JSON_API_Brainstorm . I also made >> substantial changes to the draft of the API based on discussion here >> and on the #postgresql IRC channel. >> >> Is it alright to use the wiki for brainstorming, or should it stay on >> the mailing list or go somewhere else? > > Well, I think it's fine to use the wiki for brainstorming, but before > you change the design you probably need to talk about it here. You > can't rely on everyone on -hackers to follow changes on a wiki page > somewhere. It looks like the API has been overhauled pretty heavily > since the last version we talked about here, and I'm not sure I > understand it. The general idea that most people have been using, and that I think is correct, is to have the discussion here on the list, and then keep a summary of the current state of it on the wiki page so it's easier for someone entering the discussion to catch up on where it is. >> I'll try not to spend too much time quibbling over the specifics as I >> tend to do. While the brainstorming is going on, I plan to start >> implementing the datatype by itself so I can establish an initial >> working codebase. > > Sounds good. Agreed. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Joseph Adams on 25 May 2010 10:52 > Well, I think it's fine to use the wiki for brainstorming, but before > you change the design you probably need to talk about it here. You > can't rely on everyone on -hackers to follow changes on a wiki page > somewhere. It looks like the API has been overhauled pretty heavily > since the last version we talked about here, and I'm not sure I > understand it. I'll try to explain it in one big nutshell: Instead of, for instance, json_to_number('5') and number_to_json(5), I propose changing it to from_json(5)::INT and to_json('5'). Note how from_json simply returns TEXT containing the underlying value for the user to cast. I plan to make calling to_json/from_json with arrays or objects (e.g. to_json(ARRAY[1,2,3]) and from_json('[1,2,3]') ) throw an error for now, as implementing all the specifics of this could be quite distracting. If I'm not mistaken, json_object([content [AS name] [, ...]] | *) RETURNS json can't be implemented without augmenting the grammar (as was done with xmlforest), so I considered making it take a RECORD parameter like the hstore(RECORD) function does, as was suggested on IRC. However, this may be inadequate for selecting some columns but not others. Using examples from hstore: SELECT hstore(foo) FROM foo; => '"e"=>"2.71828", "pi"=>"3.14159"' -- this works, but what if we only want one field? SELECT hstore(pi) FROM foo; -- function type error SELECT hstore(row(pi)) FROM foo; => '"f1"=>"3.14159"' -- field name is lost SELECT hstore(bar) FROM (select pi FROM foo) AS bar; => '"f1"=>"3.14159"' -- ugly, and field name is *still* lost To get (and set, which I overlooked before), use json_get and json_set. These take "JSONPath" expressions, but I don't plan to implement all sorts of fancy features during the summer. However, I do plan to support some kind of parameter substitution so you can do this: json_get('[0,1,4,9,16,25]', '[%]' %% 2) => '4'::TEXT For this use case, though, it would be simpler to say: '[0,1,4,9,16,25]'::JSON -> 2 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 25 May 2010 12:49 On Tue, May 25, 2010 at 10:52 AM, Joseph Adams <joeyadams3.14159(a)gmail.com> wrote: >> Well, I think it's fine to use the wiki for brainstorming, but before >> you change the design you probably need to talk about it here. You >> can't rely on everyone on -hackers to follow changes on a wiki page >> somewhere. It looks like the API has been overhauled pretty heavily >> since the last version we talked about here, and I'm not sure I >> understand it. > > I'll try to explain it in one big nutshell: > > Instead of, for instance, json_to_number('5') and number_to_json(5), I > propose changing it to from_json(5)::INT and to_json('5'). Note how > from_json simply returns TEXT containing the underlying value for the > user to cast. I plan to make calling to_json/from_json with arrays or > objects (e.g. to_json(ARRAY[1,2,3]) and from_json('[1,2,3]') ) throw > an error for now, as implementing all the specifics of this could be > quite distracting. I don't see how that's an improvement over the previous design. It seems like it adds a lot of extra casting and removes useful list operations without any corresponding advantage. > If I'm not mistaken, json_object([content [AS name] [, ...]] | *) > RETURNS json can't be implemented without augmenting the grammar (as > was done with xmlforest), so I considered making it take a RECORD > parameter like the hstore(RECORD) function does, as was suggested on > IRC. However, this may be inadequate for selecting some columns but > not others. Using examples from hstore: > > SELECT hstore(foo) FROM foo; => '"e"=>"2.71828", "pi"=>"3.14159"' > -- this works, but what if we only want one field? > > SELECT hstore(pi) FROM foo; > -- function type error > > SELECT hstore(row(pi)) FROM foo; => '"f1"=>"3.14159"' > -- field name is lost > > SELECT hstore(bar) FROM (select pi FROM foo) AS bar; => '"f1"=>"3.14159"' > -- ugly, and field name is *still* lost Yeah. I'm not sure what to do about this problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 27 May 2010 15:35 Joseph Adams <joeyadams3.14159(a)gmail.com> writes: > I tried making a function named json_type that has the same name as > the type json_type. However, this doesn't work as expected: > SELECT json_type('[1,2,3]'); > Instead of calling json_type with '[1,2,3]' casted to JSON, it's > trying to cast '[1,2,3]' to json_type. Is there a way to override > this behavior, or would I be better off renaming the function? Well, that might not be the behavior you expected, but that doesn't make it wrong. The above is, by convention, equivalent to '[1,2,3]'::json_type, so it's acting as per convention. If the function is a cast function (which it is), it *should* be named after the destination type. Doing anything else will violate numerous longstanding expectations. You might want to read the comments about function-calls-as-casts in func_get_detail(). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 27 May 2010 15:37
On Thu, May 27, 2010 at 3:35 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > If the function is a cast function (which it is), I don't think it is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |