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

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

* Actually BSON

JSON in SQL

Flexibility

JSON in PostgreSQL

*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_jsonTurn a row into a JSON structure
json_aggAggregate records into a JSON array
json_eachUnpack a JSON object into key/value pairs
json_array_elementsUnpack 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

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

Comparison with Normalized SQL

Benchmarks are fundamentally unhelpful, but ...

Comparison with MongoDB / CouchDB

I like NoSQL, but I miss ...

Summary

Postgres JSON columns are:

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

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

QUESTIONS?