jsonb.pl

#!/usr/bin/perl
use strict;
use warnings;

use Data::Dumper;
use DBD::Pg 3.5.0;

my $dbh = DBI->connect("dbi:Pg:", '', '',
  {
    AutoCommit => 1,
    RaiseError => 1,
    PrintError => 1,
  }) || die "Can't connect: $!\n";

if ( $dbh->{pg_server_version} < 90400 ) {
  die "Please use against Postgresql 9.4 or later!\n";
}

$dbh->do('
   CREATE TEMPORARY TABLE book (
       title text not null,
       info jsonb not null
  );'
);

my $insert = "INSERT INTO book (title, info) VALUES (?, ?)";
my $sth = $dbh->prepare($insert);
$sth->execute('Our Uncle', '{"year": 1985, "category": "novel", "author": "Arnon Grunberg" }');
$sth->execute('The Wild Things', '{"year": 1999, "author": "Dave Eggers"}');

# get all book s that have a category set
my $res = $dbh->selectall_arrayref(
  # first ? is 'exists' operator, second is bind parameter
  'SELECT title FROM book WHERE info::jsonb \? ?',
  # add a bind value
  undef, 'category');

print Dumper ($res);

$dbh->disconnect;