PostgreSQL 9.4’s JSONB and perl DBD::Pg
As you’ll know by now, PostgreSQL 9.4 is out and it is better than ever. The most notable change is the new JSONB data type, where you can store ‘document type’ data in your relational database, basically allowing for supporting many use cases that previously required MongoDB or similar NoSQL solutions.
A couple of functions and operators have been added to support JSONB, one of these is the ? operator which can be used to check if a key exists. This caused a problem for Perl’s DBD::Pg module as it uses the ? character as a bind operator. Since version 3.5.0 and up, you can now escape a ? if you want to use it as an operator instead of a bind parameter. I’ll show briefly how you can leverage this.
Example: the most stupid schema ever
Because so many SQL examples are using books and authors, I’ll throw in the obligatory book table. But this time with JSONB!
Of course this is only an example, but one of the use cases of JSONB is that you can add as many attributes to an object as you want without the need of changing the schema and your database will still be fast. You can also create indexes on JSON attributes if needed.
So in my example we’ll create the most stupid database table layout:
CREATE TEMPORARY TABLE book ( title text not null, info jsonb );
So we’ll store the book title in a column and we’ll create a second column in the JSONB format to store attributes. For instance, let’s insert these values:
INSERT INTO book (title, info) VALUES ('Our Uncle', '{"year": 1985, "category": "novel", "author": "Arnon Grunberg" }'), ('The Wild Things', '{"year": 1999, "author": "Dave Eggers"}');
Some SQL queries on the JSONB
This allows us to query on the book. For instance, this would return the author of ‘The Wild Things’:
SELECT info::jsonb->'author' FROM book WHERE title = 'The Wild Things';
But what if we want to get a list of all books that have a category set? This is where the ? operator comes into play. The ? operator can be used to check existence of a key. You can also use it to check if a value is part of a JSON array. But for our example, the query would be like this:
SELECT title FROM book WHERE info::jsonb ? 'category';
Apply that to perl!
Now let’s see what that would look like in perl.
You’ll need DBD::Pg 3.50 or newer to be able to use the \ character to be able to escape the operator. Remember, a regular ‘?’ is use as bind character when using Perl DBI. If you’d use an older version of DBD::Pg you’d still be able to leverage the JSONB features, just not the ? operator!
If you’d use it on a version of DBD::Pg that is prior to 3.5.0, you’d get:
DBD::Pg::db selectall_arrayref failed: called with 1 bind variables when 2 are needed at jsonb.pl line 32.
JuanL
says:This is nice, I wonder when some perl ORMs just like DBIx::Class will provide some support for JSONB type.
Application FWs like Catalyst or Dancer would really benefit from this.
Thank you for your article.