PostgreSQL JSON Cheatsheet

Using JSON in Postgres by example.

Quick setup via Docker

Download and install: Docker Toolbox
Open Docker Quickstart Terminal
Start a new postgres container:
docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres
Connect via:
docker exec -i -t my-postgres psql -E -U postgres

Magic words

\q: Quit
\c __database__: Connect to a database
\d __table__: Show table definition including triggers
\dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
\l: List databases
\dn: List schemas
\df: List functions
\dv: List views
\df+ __function : Show function SQL code
\x: Expanded display (disables ASCII tables)
\pset pager off: Disable console pagnation (no --More--!)
\timing: Show query timing stats

Storing and querying JSON

Creating a new schema

CREATE DATABASE my_database WITH ENCODING = UTF8;
\l: Check that your new database has been created
\c my_database Connect to your new database
CREATE TABLE IF NOT EXISTS my_table ( my_data JSONB );
\d Check that your new table has been created

Inserting data

INSERT INTO my_table VALUES ('{
  "name": "my_json",
  "date": "2016-01-21 00:00",
  "priority": {"rank": 10, "name": "Lowest"},
  "grouping": "group-1"
}');

INSERT INTO my_table VALUES ('{
  "name": "foo",
  "date": "2016-02-20 14:05",
  "priority": {"rank": 1, "name": "Critical"},
  "grouping": "group-1"
}');

INSERT INTO my_table VALUES ('{
  "name": "bar",
  "date": "2015-06-25 20:59",
  "priority": {"rank": 2, "name": "Important"},
  "grouping": "group-1"
}');

INSERT INTO my_table VALUES ('{
  "name": "baz",
  "date": "2016-01-01 13:01",
  "priority": {"rank": 5, "name": "Moderate"},
  "grouping": "group-2"
}');

Selecting

Select all data from the table and return it as a JSON array:

SELECT json_agg(my_data) FROM my_table;

Select a single JSON property:

SELECT my_data->>'name' AS name FROM my_table;

The -> operator returns the original JSON type (which might be an object), whereas ->> returns text. You can use the -> to return a nested object and thus chain the operators.

Selecting data from a nested object

SELECT my_data->>'name' AS name,
       my_data->'priority'->>'name' AS priority
FROM my_table;

Sorting

Sort by priority rank:

SELECT * FROM my_table ORDER BY CAST(my_data->'priority'->>'rank' AS integer) ASC;

Sort by date:

SELECT * FROM my_table ORDER BY to_date(my_data->>'date', 'YYYY-MM-DD');

Filtering

Find data from group-1:

SELECT * FROM my_table WHERE my_data->>'grouping' = 'group-1';

Finding data with a priority rank greater than or equal to 5:

SELECT * FROM my_table WHERE CAST(my_data->'priority'->>'rank' AS integer) >= 5;

Filtering data between a date range:

SELECT *
FROM my_table
WHERE to_date(my_data->>'date', 'YYYY-MM-DD')
    BETWEEN '2016-01-01'
    AND     '2016-01-31';

Show newest item for each group:

SELECT DISTINCT ON (grouping)
  my_data->>'grouping' AS grouping,
  my_data->>'name' AS name,
  my_data->>'date' AS date
FROM my_table
ORDER BY my_data->>'grouping', to_date(my_data->>'date', 'YYYY-MM-DD') DESC;

Counting the number of rows in each grouping:

SELECT my_data->>'grouping' AS grouping,
       count(my_data)
FROM my_table
GROUP BY my_data->>'grouping';

Indexing

Creating a new index:

CREATE INDEX my_grouping_index
ON my_table ((my_data->>'grouping'));

Creating a new multi-column index:

CREATE INDEX my_grouping_multi_column_index
ON my_table ((my_data->>'date') DESC, (my_data->>'grouping'));

Check index has been created: \d my_table

Use \timing to show stats on how your indexes improve your query speed.

Removing data

Delete a specific row:
DELETE FROM my_table WHERE my_data->>'grouping' = 'group-1';

Empty table:
TRUNCATE my_table;

Delete table:
DROP TABLE my_table;

Delete database:
DROP DATABASE my_database;

blackstrip/postgres-json-cheatsheet.md