PostgreSQL JSON data types

NoSQL in SQL?

Nick Moore <nick@mnemote.com>

JSON — JavaScript Object Notation

{
   "id": "1220",
   "title": "PostgreSQL JSON data types",
   "subtitle": "NoSQL in SQL?",
   "tags": [ "SQL", "NoSQL", "JSON" ],
   "author": {
       "name": "Nick Moore",
       "email": "nick@mnemote.com"
   }
}

JSON — Portability

JSON types are (more or less) Javascript literal types
Very similar to native Python/Perl types
Easy to use from Javascript frontends via AJAX/REST
Easy to template on frontend or backend
Relatively easy to hand-edit

⇒ Used in NoSQL "Document Store" databases
(MongoDB*, CouchDB)

* Actually BSON

JSON in SQL

Flexibility

Avoid ‘Kitchen Sink’ tables
Avoid Entity-Attribute-Value Antipattern
Avoid ‘Maze of Twisty Joins’
Don't need explicit database migrations for new features

JSON in PostgreSQL

PostgreSQL 9.3 adds JSON Datatype
Basically a text field but enforces valid JSON
PostgreSQL 9.4 adding JSONB datatype
Same thing, more efficient*

*a lot like BSON

JSON in PostgreSQL — CREATE TABLE

CREATE TABLE Profiles (
    id SERIAL PRIMARY KEY,
    data JSON
);

JSON in PostgreSQL — Creating

INSERT INTO Profiles (data) VALUES (
    '{
        "occupation": "hacker",
        "likes": [ "beer", "sql" ]
    }'
);

JSON in PostgreSQL — Extracting

select data->>'occupation' from profiles;

select * from profiles
  where data->>'occupation' = 'hacker';

JSON Operators & Functions

Builtin JSON Operators and Functions manipulate JSON objects

->>	Lookup Element (as text)
#>>	Lookup Element Path (as text)
row_to_json	Turn a row into a JSON structure
json_agg	Aggregate records into a JSON array
json_each	Unpack a JSON object into key/value pairs
json_array_elements	Unpack a JSON array

... and many others ...

Indexing JSON Values

Indexes on Expressions

CREATE INDEX profiles_occupation
  ON profiles ((data->>'occupation'));

SELECT * FROM profiles
WHERE data->>'occupation' = 'hacker';

Indexing JSON Array Values — 1

Multi-valued attributes
```
"likes": [ "beer", "sql" ]
```
Use GIN indexes to store multiple values per row.
Unfortunately, support isn't brilliant
Use a helper function ...

Indexing JSON Array Values — 2

CREATE OR REPLACE FUNCTION
    public.json_to_text_array(jarr JSON)
  RETURNS text[] LANGUAGE sql IMMUTABLE
AS $$
  SELECT ARRAY_AGG(ROW_TO_JSON(T)->>'x')
  FROM (
    SELECT JSON_ARRAY_ELEMENTS(jarr) AS x
  ) AS T
$$;

Indexing JSON Array Values — 3

CREATE OR REPLACE FUNCTION
    public.json_to_text_array(jarr JSON)
  RETURNS text[] LANGUAGE sql IMMUTABLE
AS $$
  SELECT ARRAY_AGG(ROW_TO_JSON(T)->>'x')
  FROM (
    SELECT JSON_ARRAY_ELEMENTS(jarr) AS x
  ) AS T
$$;

Indexing JSON Array Values — 4

CREATE OR REPLACE FUNCTION
    public.json_to_text_array(jarr JSON)
  RETURNS text[] LANGUAGE sql IMMUTABLE
AS $$
  SELECT ARRAY_AGG(ROW_TO_JSON(T)->>'x')
  FROM (
    SELECT JSON_ARRAY_ELEMENTS(jarr) AS x
  ) AS T
$$;

Indexing JSON Array Values — 5

CREATE OR REPLACE FUNCTION
    public.json_to_text_array(jarr JSON)
  RETURNS text[] LANGUAGE sql IMMUTABLE
AS $$
  SELECT ARRAY_AGG(ROW_TO_JSON(T)->>'x')
  FROM (
    SELECT JSON_ARRAY_ELEMENTS(jarr) AS x
  ) AS T
$$;

Indexing JSON Array Values — 6

CREATE OR REPLACE FUNCTION
    public.json_to_text_array(jarr JSON)
  RETURNS text[] LANGUAGE sql IMMUTABLE
AS $$
  SELECT ARRAY_AGG(ROW_TO_JSON(T)->>'x')
  FROM (
    SELECT JSON_ARRAY_ELEMENTS(jarr) AS x
  ) AS T
$$;

Indexing JSON Array Values — 7

CREATE INDEX profiles_likes
ON profiles USING gin (
  json_to_text_array(data->'likes')
);

SELECT * FROM profiles
WHERE json_to_text_array(data->'likes')
  @> ARRAY['beer'];

Indexing JSON Array Values — 8

Inspired by "Index for finding an element in a JSON array" on stack overflow
Could also index multiple attributes at once
I'll post a nicer version on my blog

Comparison with Normalized SQL

Benchmarks are fundamentally unhelpful, but ...

Fewer table joins
Fewer seeks ⇒ should have better IO performance
Less translation in backend code

Comparison with MongoDB / CouchDB

I like NoSQL, but I miss ...

Transactions
Joins with relational tables
Ad-hoc querying

Summary

Postgres JSON columns are:

Easy to use
Flexible
Indexable
Efficient

Would also work in MS SQL Server, others ...

... really anything which supports indexes on expressions and user-defined functions.