Under the wire

Monday, January 16. 2012

On Wednesday, four days before the start of the final commitfest for release 9.2 of PostgreSQL, Robert Haas published YA patch to include JSON as a core type. Basically, his patch just parses the text to make sure it was valid JSON, and stores it as text. I'd just about given up on getting this into release 9.2, but I thought his patch was just a bit too minimal, so I put on the running shoes to add in some functionality to produce JSON from the database: query_to_json(), array_to_json() and record_to_json(). Yesterday I put out this patch, extending his, right on the deadline for this commitfest and thus for this release. A few simple examples from the regression tests:

SELECT query_to_json('select x as b, x * 2 as c from generate_series(1,3) x',false);
                query_to_json                
---------------------------------------------
 [{"b":1,"c":2},{"b":2,"c":4},{"b":3,"c":6}]
(1 row)

SELECT array_to_json('{{1,5},{99,100}}'::int[]);
  array_to_json   
------------------
 [[1,5],[99,100]]
(1 row)

-- row_to_json
SELECT row_to_json(row(1,'foo'));
     row_to_json     
---------------------
 {"f1":1,"f2":"foo"}
(1 row)

and a slightly less simple example:

SELECT row_to_json(q) 
FROM (SELECT $$a$$ || x AS b, 
         y AS c, 
         ARRAY[ROW(x.*,ARRAY[1,2,3]),
               ROW(y.*,ARRAY[4,5,6])] AS z 
      FROM generate_series(1,2) x, 
           generate_series(4,5) y) q;
                            row_to_json                             
--------------------------------------------------------------------
 {"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
 {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
 {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
 {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
(4 rows)

We need to sort out a few encoding issues, but I'm now fairly hopeful that we'll have some very useful and fast JSON functionality in release 9.2.

Posted by Andrew Dunstan in PostgreSQL at 17:55 | Comments (16) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

This is absolutely great. Many thanks! I always thought the XML handling in SQL Server was very good in comparison to others (PostgreSQL and virtually non-existent in MySQL), so seeing another web format being supported is great. 
 
Thanks!

#1 Richard Gibson on 2012-01-16 19:33 (Reply)

Fantastic. Particularly once coupled with PL/V8 this is going to be really exciting for a lot of folks in the web community.

#2 Peter van Hardenberg on 2012-01-16 20:22 (Reply)

Super, Andrew! (We have clients clamoring for this feature). Much-needed, long-awaited and now, right around the corner.

#3 Lou Picciano on 2012-01-16 21:11 (Reply)

Conversion between json and hstore would also be useful.

#4 Guan Yang on 2012-01-16 23:29 (Reply)

Well, in the first place we can't do that in the core types, because hstore is an extension type. Beyond that, there are difficulties in that a) hstore is a flat structure, but json is an arbitrarily deep tree structure, and b) we're storing JSON as text, so we'd need to parse it. Probably we'll be able to do something of the kind, at least in the JSON->hstore direction, using PLV8. It shouldn't be too hard to create an extension which did hstore->JSON. I'll look at that.

#4.1 Andrew Dunstan on 2012-01-17 00:03 (Reply)

this conversion can be done in hstore module - JSONHStore, but I don't see a advantage of this step - HStore is too flat - but it can be good for interoperability.

#4.1.1 Pavel Stehule (Homepage) on 2012-01-17 00:23 (Reply)

I realize the necessary limitations of such a feature, at least in the JSON -> hstore direction, but I think it would be great for interoperability. hstore is increasingly being used in environments where JSON is common. JSON parsers are a lot more common in the real world than hstore parsers. 
 
(I recently had to write an hstore parser, and it wasn't very fun.)

#4.1.1.1 Guan Yang on 2012-01-17 23:21 (Reply)

Well, don't do that <img src="/andrew/templates/default/img/emoticons/smile.png" alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" /> Turning an hstore into XML, for example, is pretty easy. See http://people.planetpostgresql.org/andrew/index.php?/archives/63-hstore-and-XML-fun.html

#4.1.1.1.1 Andrew Dunstan on 2012-01-18 04:55 (Reply)

Thank you Andrew, this is exactly the feature I need. Do you have a way to donate money to you, or to the project?

#5 Joel Parker Henderson (Homepage) on 2012-01-17 02:12 (Reply)

If you want to donate to the project, please go to http://www.postgresql.org/about/donate_pg_org. 
I have no idea how much money you're talking about, but if you want to sponsor more features, I or my colleagues will be happy to talk to you.

#5.1 Andrew Dunstan on 2012-01-17 02:54 (Reply)

Hi Andrew, 
 
This is great, thanks! 
I have a feature suggestion <img src="/andrew/templates/default/img/emoticons/smile.png" alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" /> 
It would also be very handy to have conversion to JSON also available as a an aggregation function, so that you could write things like: 
 
SELECT id, aggregate_to_json(value) 
FROM arandomtable 
GROUP BY id

#6 Filipe (Homepage) on 2012-01-17 19:11 (Reply)

You write it like this: 
SELECT id, array_to_json(array_agg(value)) 
FROM arandomtable 
GROUP BY id

#6.1 Andrew Dunstan on 2012-01-17 19:32 (Reply)

Thanks Andrew, this is awesome stuff. I just tried it out on the below query to nest data and this is going to be a game changer once it matures a bit. Specifically (and I didn't see how to get it to work otherwise), using fx for key names makes it tuff to use today. How come it just doesn't use the column names, I'm pretty sure any valid column identifier in postgres would also be a valid javascript identifier. 
 
Still, very cool that it doesn't double parse the json: 
 
select row_to_json(q) from (select ,(select array(select row_to_json(q2) from (select from contacts_info where contacts_info.contact_id = contacts.id)q2)) from contacts) q; 
 
produces: 
 
{ 
"f1": 1, 
"f2": "Person", 
"f3": "2012-03-09 01:16:07.791984-08", 
"f4": "KrazyKrut", 
"f5": null, 
"f6": "Troy Kruthoff", 
"f7": "Troy", 
"f8": "Kruthoff", 
"f9": null, 
"f10": [ 
{ 
"f1": 1, 
"f2": "Contact::Phone", 
"f3": 1, 
"f4": null, 
"f5": "916-555-1212", 
"f6": null, 
"f7": null, 
"f8": null 
} 
] 
}

#7 Troy on 2012-03-10 13:04 (Reply)

Are you using the 9.1 backport? Most of this loss of column labels has been fixed in the development branch, which will become Postgres 9.2.

#7.1 Andrew Dunstan on 2012-03-10 19:32 (Reply)

Yes, the backport from your bitbucket repo. I looked at the source to see if I could fix it quickly, but it looks like line 900 of json.c is using attname, and since I haven't touched C in over a decade, I'll just assume it's beyond my paygrade! 
 
But that's awesome it will work as expected in 9.2 
 
I'm going to second what Richard Gibson said above about webapps. I'm building a webapp with node.js and like so many others in the node community, looked at mongodb first for the json support. However, we were spending a lot of time trying to solve things that postgres can do easily. This lead us to plv8 and the forthcoming json type. 
 
I've spent the last few days replacing mongo with postgres and it looks very promising. After trying a few different approaches, I think what is going to work best for us is doing our CRUD via plv8 with a text in and a return type of text, which will get parsed/stringify in the function.

#7.1.1 Troy on 2012-03-11 06:21 (Reply)

The changes have been backported if you feel brave and want to try them out: 
https://bitbucket.org/adunstan/pgdevel/changesets/tip/rowexpr91

#7.1.1.1 Andrew Dunstan on 2012-03-19 17:23 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images. Wiki format allowed
	Remember Information? Subscribe to this entry