Postgres key-value Reference Card

17 Mar 2017

I have been spending more time in postgres using its hstore/json fields. It’s a great way to augment a relational database with a slick key-value store. I like this combination of relational and key-value a lot.

One thing I have found confusing, especially working the key-value fields from Rails: the query format for different key-value fields and how to do common key-value operations such as: records with certain keys, records with a key value, etc.

There are many similarities with each type of field, but enough differences where I have to drop right into a SQL console to write things out myself.

Instead, here is a reference card for how to query for records with a:

key that exist in the field
key not in the field
key equaling a value
key not equaling a value
key with a group of values
specific value (regardless of key)
not a specific value (regardless of key)
list of all keys in a key-value column

and a tip I found I always need when working with the key-value field in Ruby on Rails.

Let’s get started with a fresh postgres install and setup a table to try stuff out.

Install and setup postgres

This is how I installed and setup postgres for the examples

Setup:

Add latest postgres repo

$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"

Install postgres > 9.4 & postgres-contrib (for hstore)

note: postgres 9.4 introduced JSONB. Also, Ubuntu 14.04’s default for postgres is still 9.3!

$ sudo apt-get install postgres postgres-contrib

$ sudo -u postgres psql postgres
psql (9.6.2, server 9.3.16)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q

add user

# CREATE USER <username> WITH SUPERUSER;

note: I grant user superuser privilege because hstore requires a user to create extension an extension on a table.

Create Database & Table

$ createdb sample

log into the sample database on postgres:

$ psql sample

enable hstore extension

# CREATE EXTENSION hstore;

create table stuff

# CREATE TABLE stuff(id          SERIAL NOT NULL PRIMARY KEY,
                     data_hstore HSTORE,
                     data_json   JSON,
                     data_jsonb  JSONB);

Sample Data

INSERT INTO stuff VALUES ( 1, '"a" => "b"', '{ "a": "b" }', '{ "a": "b" }' );
INSERT INTO stuff VALUES ( 2, '"a" => "c"', '{ "a": "c" }', '{ "a": "c" }' );
INSERT INTO stuff VALUES ( 3, '"a" => "d"', '{ "a": "d" }', '{ "a": "d" }' );
INSERT INTO stuff VALUES ( 4, '"e" => "b"', '{ "e": "b" }', '{ "e": "b" }' );

The following records are the ones to be used with the commands below.

Current table entries

SELECT * FROM stuff;

Result:

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  1 | "a"=>"b"    | { "a": "b" } | {"a": "b"}
  2 | "a"=>"c"    | { "a": "c" } | {"a": "c"}
  3 | "a"=>"d"    | { "a": "d" } | {"a": "d"}
  4 | "e"=>"b"    | { "e": "b" } | {"e": "b"}
(4 rows)

Accessors

As far as I can tell from the postgres documentation:

-> returns text
->> returns an object

So, from my experience, -> will work with hstore, json, and jsonb types. ->> only works with json and jsonb types.

Keep an eye out on the accessor being used in the examples below.

Records with a key that has any value

Basically, any records that has a particular key. Great for filtering out records where a key is known, but not all of its values.

SELECT * FROM stuff WHERE data_hstore -> 'a' IS NOT NULL;
SELECT * FROM stuff WHERE data_json   -> 'a' IS NOT NULL;
SELECT * FROM stuff WHERE data_jsonb  -> 'a' IS NOT NULL;

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  1 | "a"=>"b"    | { "a": "b" } | {"a": "b"}
  2 | "a"=>"c"    | { "a": "c" } | {"a": "c"}
  3 | "a"=>"d"    | { "a": "d" } | {"a": "d"}
(3 rows)

Records without a key

The inverse of the previous query, filter all records without a key.

SELECT * FROM stuff WHERE data_hstore -> 'a' IS NULL;
SELECT * FROM stuff WHERE data_json   -> 'a' IS NULL;
SELECT * FROM stuff WHERE data_jsonb  -> 'a' IS NULL;

Result:

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  4 | "e"=>"b"    | { "e": "b" } | {"e": "b"}
(1 row)

Records with a key equal to a value

Any records with a specific key-value pair.

SELECT * FROM stuff WHERE data_hstore ->  'a' = 'b';
SELECT * FROM stuff WHERE data_json   ->> 'a' = 'b';
SELECT * FROM stuff WHERE data_jsonb  ->> 'a' = 'b';

Result:

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  1 | "a"=>"b"    | { "a": "b" } | {"a": "b"}
(1 row)

note: accessors for hstore and json/jsonb differ here.

Records where a key does not have a value

The inverse of the previous query, filter out all key-value pairs.

SELECT * FROM stuff WHERE data_hstore ->  'a' != 'b';
SELECT * FROM stuff WHERE data_json   ->> 'a' != 'b' ;
SELECT * FROM stuff WHERE data_jsonb  ->> 'a' != 'b' ;

Result:

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  2 | "a"=>"c"    | { "a": "c" } | {"a": "c"}
  3 | "a"=>"d"    | { "a": "d" } | {"a": "d"}
x(2 rows)

Records with a key equal to a group of values

This is a great one to filter for a key to a known set of values.

SELECT * FROM stuff WHERE data_hstore ->  'a' IN ('b', 'c');
SELECT * FROM stuff WHERE data_json   ->> 'a' = 'b' OR  "data_json" ->> 'a' = 'c';
SELECT * FROM stuff WHERE data_jsonb  ->  'a' ?| ARRAY['b', 'c'];

Result:

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  1 | "a"=>"b"    | { "a": "b" } | {"a": "b"}
  2 | "a"=>"c"    | { "a": "c" } | {"a": "c"}
(2 rows)

This answer was very insightful in the JSONB query. I am a bit surprised there isn’t something similar for the JSON query.

Records which any key has a value:

SELECT * FROM stuff WHERE data_hstore::text LIKE '%b%';
SELECT * FROM stuff WHERE data_json::text   LIKE '%b%';
SELECT * FROM stuff WHERE data_jsonb::text  LIKE '%b%';

Result:

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  1 | "a"=>"b"    | { "a": "b" } | {"a": "b"}
  4 | "e"=>"b"    | { "e": "b" } | {"e": "b"}
(2 rows)

This will only work reliably when a value never exists as a key.

Records which any key does not have a value:

SELECT * FROM stuff WHERE data_hstore::text NOT LIKE '%b%';
SELECT * FROM stuff WHERE data_json::text   NOT LIKE '%b%';
SELECT * FROM stuff WHERE data_jsonb::text  NOT LIKE '%b%';

Result:

 id | data_hstore |  data_json   | data_jsonb
----+-------------+--------------+------------
  2 | "a"=>"c"    | { "a": "c" } | {"a": "c"}
  3 | "a"=>"d"    | { "a": "d" } | {"a": "d"}
(2 rows)

Thanks to Sergey for for showing the way to search for any key with a value.

Get all possible keys in a column

Return all the keys that exists in a key/value column of a table.

SELECT skeys(data_hstore)           AS fields FROM stuff;
SELECT json_object_keys(data_json)  AS fields FROM stuff;
SELECT json_object_keys(data_jsonb) AS fields FROM stuff;

Result:

 fields
--------
 a
 a
 a
 e
(4 rows)

If the keys are unknown ahead of time, this is essential to finding out all the possible keys that maybe in a key-value field!

HSTORE fields use skeys while JSON and JSONB use json_object_keys.

For Rails / ActiveRecord

For some reason, the ‘sql’ part of the query must contain parenthesis for the key-value item, like so:

Stuff.where('(data_hstore -> "a") IS NOT NULL')
Stuff.where('(data_json   -> "a") IS NOT NULL')
Stuff.where('(data_jsonb  -> "a") IS NOT NULL')

Otherwise, there will be a database transaction error on the connection and it will have to be reset.

Conclusion

A quick reference on how to select items from the Postgresql’s key value field: hstore, json, and jsonb.

If there are any improvements to the examples or additional ones you want to see, tweet me (@redgreenrepeat) or send me a quick email (andrew_at_redgreenrepeat.com)