Get some JSON in your database | TechRepublic

Get some JSON in your database | TechRepublic:

Takeaway: If you deal with JSON day in, day out, at least have a database that can handle JSON as well.

As JavaScript continues its creeping march across the programming landscape, one database has grasped the new JSON reality and has built it deep into its upcoming release.

PostgreSQL 9.3 will come bundled with new functions that will allow for JSON generation and manipulation, as highlighted in this series of blog posts by PostgreSQL developer and VMware employee Michael Paquier.

The existing version of PostgreSQL, 9.2.x, already has support for a JSON field type, and the ability to make sure any data to be stored as JSON is well formed.

In the upcoming 9.3 release, the database will gain the ability to fetch from fields within the JSON strings, and also have the ability to store JSON or arrays within a JSON string.

Examples that Paquier gives of how the JSON data is stored are:

CREATE TABLE aa (a int, b json);
INSERT INTO aa VALUES (1, '{"f1":1,"f2":true,"f3":"Hi I''m \"Daisy\""}');
INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');
INSERT INTO aa VALUES (3, '{"f1":[1,"Robert \"M\"",true],"f2":[2,"Kevin \"K\"",false]}');

And the queries that return said data:

SELECT b->'f3' AS f1 FROM aa WHERE a = 1;
f1
--------------------
"Hi I'm \"Daisy\""

SELECT b->'f1'->1 as f1_0 FROM aa WHERE a = 3;
f1_0
----------------
"Robert \"M\""

You’ll notice the -> operator that is used to fetch data from within JSON, and there is also a ->> operator that returns plain text — for instance, instead of returning “Hi I’m \”Daisy\”" with -> operator, ->> returns Hi I’m “Daisy”. Do read the above linked blog posts to understand the operators properly.

There will also be json_agg and to_json functions to turn a row or field in legitimate JSON, respectively.

After spending an unproductive amount of time creating pass-through scripts and web pages that simply convert rowsets into JSON to be passed onto JavaScript applications, having the option to have all this work completed on the database side is welcome.

It also continues the trend of JSON displacing XML structures for small datasets.

I have lately found myself preferring JSON feeds for simple syndication purposes. Maybe I am just sick and tired of wrestling with XML parsers and their associated slowness and resource hunger — and trying to prevent myself from implementing the very bad idea of replacing said parsers with regexs to fetch the one field I am interested in.

The idea of being able to have strict type checking and manipulation on the database, while allowing the JavaScript to be as free and wild as it wants to be, it also heartening.

Having these abilities baked into the heart of the database itself is an added bonus that I wish many more database vendors would offer.

What do you think? Have I spent too much time in JavaScript-land to think this a good idea? Do you already use libraries and extensions for the same purpose in your database?