Version 9.4 of PostgreSQL was released last month, which meant I got to experience a developer tradition unlike any other: Postgres fanatics being snarky about their database of choice on Twitter. These folks will say almost everything. They’ll tell you how your own databsae is worse (especially if your database is MongoDB), how you can replace it with Postgres (regardless of your requirements and environment), and how any issues you may find with Postgres may actually be a problem with you.
In all fairness, I have nothing against PostgreSQL. It is a fine database, and I really am impressed with its feature list. But I find its community to be far too much of the “tell not show” variety, and in my opinion this is the absolute worst way to promote a product to the uninitiated.
So when something like this pops up in my Twitter feed, the only way I, a Postgres neophyte, can learn how to use the database’s shiny new JSON features is to figure it out myself. Now I’d like to share what I’ve found for anyone else in my boat.
(author’s note - when I say I am a PostgreSQL neophyte, I mean it. If anything about this post is factually inaccurate, I apologize.)
Postgres 9.4 introduces a new data type named jsonb, which stores JSON data in a parsed, binary format. The idea is that you spend a little more in storage space to gain faster processing later on. Also, because jsonb is parsed, you can perform advanced lookups and sorting operations.
So how do we add JSON documents to a table? The process is actually fairly simple. Here’s a sample CREATE statement for a table with a jsonb field:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name TEXT NOT NULL,
quantity INT,
jsondata jsonb,
reserved INT DEFAULT 0);So now we have a table with a JSON field named jsondata. Let’s add a row containing some JSON data:
insert into products(product_name, quantity, jsondata, reserved)
values ('name, 1, '{"bar": "baz", "balance": 7.77, "active": false}'::jsonb, 3);As you can see, you have to wrap your JSON string in single quotes, with the ::jsonb specifier tacked on the end. A little weird if you ask me, but it works.
Another point of note: the jsonb data type will not accept malformed JSON. If you try it, you will get an error stating the input is invalid.
Now, how about reading JSON? The output of a basic select statement looks straightforward:
=# select jsondata from products;
jsondata
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
{"bar": "baz", "active": false, "balance": 8.00}
{"foo": "bar"}
(3 rows)If, on the other hand, you want to do something more complex, you will have to rely on Postgres’ special JSON operators. For example, the @> operator lets you search JSON documents for those containing a specific JSON value:
=# select jsondata from products where jsondata @> '{ "bar": "baz"}';
jsondata
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
{"bar": "baz", "active": false, "balance": 8.00}
(2 rows)There’s also the #> operator, which lets you look up values by key:
=# select jsondata #>'{"bar"}' from products;
?column?
----------
"baz"
"baz"
(3 rows)This same operator can be used for the purposes of sorting:
=# select jsondata from products where jsondata @> '{ "bar": "baz"}' order by jsondata #> '{"balance"}' desc;
jsondata
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 8.00}
{"bar": "baz", "active": false, "balance": 7.77}
(2 rows)There’s a lot more to these operators than I’ve shown here, but I’ve yet to explore them to their fullest extent. Suffice to say that it looks like they’re capable of performing powerful, flexible queries on JSON data, albeit in a fairly ugly, unintuitive way.
Lastly, I’d like to quickly discuss driver support. Having support for JSON is all well and good, but how do we use it in our language of choice? I wrote up a simple Java program to test accessing JSON via JDBC. My findings are that it works, but it sure as heck isn’t fancy.
To get this sample program to run, I downloaded the latest pre-release version of the Postgres 9.4 JDBC driver from here. I then created a basic project in Eclipse, added the JDBC driver JAR to the build path, and created the following class file:
Naturally, I also had PostgreSQL 9.4 running, and had created the products table we’ve been using throughout this post.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class PgJsonTest {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
Class.forName("org.postgresql.Driver");
/*
* In the following lines, replace 'testdb' with the name of your db
* and username.
*/
String url = "jdbc:postgresql://localhost/testdb";
Properties props = new Properties();
props.setProperty("user","testdb");
Connection conn = DriverManager.getConnection(url, props);
// A basic SELECT statement.
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM products");
while (rs.next())
{
System.out.print("Contents of Column 4");
System.out.println(rs.getString(4));
}
rs.close();
st.close();
// You can also insert new records via JDBC
st = conn.createStatement();
st.execute("insert into products (product_name, quantity, jsondata, " +
"reserved) values ('name', 2, '{\"bar\": \"baz\", " +
"\"balance\": 8.00, \"active\": false}'::jsonb, 3);");
rs.close();
st.close();
/*
* An example of using one of the more advanced SELECT queries
*/
st = conn.createStatement();
rs = st.executeQuery(
"select jsondata from products where jsondata @> " +
"'{ \"bar\": \"baz\"}' order by jsondata #> " +
"'{\"balance\"}' desc");
while (rs.next())
{
System.out.print("Results of Query");
System.out.println(rs.getString(1));
}
}
}Here’s some sample output I got after running this Java code:
Contents of Column 4{"bar": "baz", "active": false, "balance": 7.77}
Contents of Column 4{"bar": "baz", "active": false, "balance": 8.00}
Contents of Column 4{"foo": "bar"}
Results of Query{"bar": "baz", "active": false, "balance": 8.00}
Results of Query{"bar": "baz", "active": false, "balance": 7.77}
Basically, you can work with JSON fields just fine in JDBC, but I haven’t found a way to read them in as anything other than a String. If you want to map your JSON to a Java object, or even to a simple Map, you’ll have to do it on your own.
In conclusion, I will reiterate that yes, this new JSON support is impressive. But the syntax and driver support is not at all elegant. Whether or not this matters to you will depend entirely on your wants and needs.
For further reading on Postgres’ JSON support, consult the following links: