PostgreSQL to Include JSON as a Core Type

In what I think is a rather cool move, PostgreSQL will be adding support for JSON as a core data type for database fields.

When I’ve written any kind of database-driven project I’ve typically used MySQL or just SQLite, mostly due to familiarity. But this might be the stimulus for me to seriously try out PostreSQL in my next project. I’ve been moving more and more to using JSON as an interchange format when my programs need to talk to other software, especially over a network (see my REST module for Warewulf as an example). The libraries are solid for just about every language, and both Perl and Python supply very good functionality for translating variables to and from JSON with minimal pain. In Python in particular, working with JSON is not quite as easy as Pickle-ing, but it’s close.

What would be nice is if I could find a database which let me search for specific key/values in a JSON field from SQL, i.e. “SELECT * FROM Users WHERE Address%City == ‘Urbana’”, where City is a key in the JSON field Address. This may very well exist somewhere, and if it does, feel free to let me know: not a lot of the software I write has much to do with large databases, but the proportion has been increasing so I’ve been trying to get better-acquainted with the popular options.

Questions, comments, interesting anecdotes? Tweet to me at @ajdecon, or send me an email at ajdecon@ajdecon.org.