PostgreSQL
First released in 1995, PostgreSQL has long been the second-most-popular open-source database (behind MySQL, available on every Linux hosting package). On the technical front, though, many consider PostgreSQL superior to MySQL, especially given uncertainty over the latter in the wake of Oracle buying Sun. Since PostgreSQL features binaries for most platforms and drivers for most programming languages, it's worth evaluating for your next open-source database project, whether it needs a relational database or NoSQL.Upload Your ResumeEmployers want candidates like you. Upload your resume. Show them you're awesome.
Why JSON?
JSON, short for JavaScript Object Notation, is a lightweight data-interchange format that is easy to read and write. It's how JavaScript holds objects in memory, and it's generally easier to work with than XML. Here's an example:{"menu": { "id": "file", "value": "File", "popup": { "menuitem": [NoSQL databases store data in JSON documents. This makes it easy to have lists or complex hierarchies of values within one document. Unlike a relational database, which stores values in a fixed type table structure, JSON documents are much more flexible; you can mix different data values in a document, and attach extra values to objects.{"value": "New", "onclick": "CreateNewDoc()"},
{"value": "Open", "onclick": "OpenDoc()"},
{"value": "Close", "onclick": "CloseDoc()"}
] } }}
PostgreSQL and JSON
In PostgreSQL 9.2, the JSON data type was introduced along with a couple of associated functions; the next update, 9.3, saw an expansion in the number of JSON functions and operators. The current 9.4 beta (3) added the jsonb type, which is a binary version of JSON plus more operators and functions. The jsonb type is significant because unlike json documents, which are an exact copy of the input document and include white spaces between tokens, jsonb doesn’t preserve insignificant white spaces, the order of object keys, or duplicate keys. It's almost always better to store documents in jsonb than json as the comparison operators only use jsonb, not json. Before you start using JSON in PostgreSQL you should familiarize yourself with rfc7159, which covers using JSON as a data-interchange format. SQL queries in PostgreSQL use this format for JSON.Working With JSON
I like the way the PostgreSQL online documentation lets you quickly flip from the 9.3 to the 9.4 page in one click, so you can see the jsonb type and the new functions. In addition to 9.3 and 9.4, there’s also the EnterpriseDB option, with the Postgres Extended Datatype Developer Kit (PGXDK), a free AMI that simplifies the development of Postgres-based applications that integrate NoSQL technologies with JSON and Python. EnterpriseDB is a third-party company that provides an enterprise version of PostgresSQL, along with support. If you want to go down the PostgreSQL JSON path, and are not already on EnterpriseDB, then I recommend you get started with 9.4, even at beta, and store text as jsonb. Here, for example, is a simple query.select * from jsonb_array_elements_text('["alpha", "beta", "charlie"]')This returns the three strings:
alpha beta charlieOr how about:
select jsonb_array_length('["alpha", "beta", "charlie"]')That returns “3.” Ready to get to work—or at least learn a little more? If you are new to JSON, start with the W3Schools JSON tutorial. Once you're up to speed with JSON, if you are new to NoSQL concepts, watch Martin Fowler's one-hour video on NoSQL. After that, you'll be ready to install PostgreSQL and can then start creating JSON using PostgreSQL JSON functions and operators. (Be aware that PostgreSQL JSON only stores documents in UTF-8 encoding and is quite strict about this encoding. Trying to use text encoded in UTF-16, UTF-32, etc. may cause errors.)
Related Articles
- A NoSQL Alternative: Disk Folders
- Are Database Administrators Doomed?
- Deflating the Hype Over In-Memory Databases