Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save blackstrip/42d37abb808549b239405ae96b2204d8 to your computer and use it in GitHub Desktop.
Save blackstrip/42d37abb808549b239405ae96b2204d8 to your computer and use it in GitHub Desktop.
Using JSON in Postgres by example

PostgreSQL JSON Cheatsheet

Using JSON in Postgres by example.

Quick setup via Docker

  1. Download and install: Docker Toolbox
  2. Open Docker Quickstart Terminal
  3. Start a new postgres container:
    docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres
  4. 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

  1. CREATE DATABASE my_database WITH ENCODING = UTF8;
  2. \l: Check that your new database has been created
  3. \c my_database Connect to your new database
  4. CREATE TABLE IF NOT EXISTS my_table ( my_data JSONB );
  5. \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;

See also

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment