rmtsrc · June 7, 2024 05:21 · Feb 23, 2016 · Feb 21, 2016 · Feb 21, 2016 · Feb 20, 2016
diff --git a/postgres-json-cheatsheet.md b/postgres-json-cheatsheet.md
@@ -23,7 +23,8 @@ Using JSON in Postgres by example.
 * `\dv`: List views
 * `\df+ __function` : Show function SQL code
 * `\x`: Expanded display (disables ASCII tables)
-* `\timing` Show query timing stats
+* `\pset pager off`: Disable console pagnation (no `--More--`!)
+* `\timing`: Show query timing stats
 
 ## Storing and querying JSON
 

diff --git a/postgres-json-cheatsheet.md b/postgres-json-cheatsheet.md
@@ -32,7 +32,7 @@ Using JSON in Postgres by example.
 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 my_table ( my_data JSONB );`
+4. `CREATE TABLE IF NOT EXISTS my_table ( my_data JSONB );`
 5. `\d` Check that your new table has been created
 
 ### Inserting data

diff --git a/postgres-json-cheatsheet.md b/postgres-json-cheatsheet.md
@@ -69,9 +69,9 @@ INSERT INTO my_table VALUES ('{
 
 ### Selecting
 
-Check your data has been inserted:  
+Select all data from the table and return it as a JSON array:  
 ```SQL
-SELECT * FROM my_table;
+SELECT json_agg(my_data) FROM my_table;
 ```
 
 Select a single JSON property:  
@@ -176,4 +176,5 @@ Delete database:
 * [Is PostgreSQL Your Next JSON Database?](https://www.compose.io/articles/is-postgresql-your-next-json-database/)
 * [Querying JSON in Postgres](http://schinckel.net/2014/05/25/querying-json-in-postgres/)
 * [What can you do with PostgreSQL and JSON?](http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/)
+* [Faster JSON Generation with PostgreSQL](https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql)
 * [Kartones/postgres-cheatsheet.md](https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546)
diff --git a/postgres-json-cheatsheet.md b/postgres-json-cheatsheet.md
@@ -107,7 +107,7 @@ Find data from group-1:
 SELECT * FROM my_table WHERE my_data->>'grouping' = 'group-1';
 ```
 
-Finding data with a priority rank equal to or greater than 5:  
+Finding data with a priority rank greater than or equal to 5:  
 ```SQL
 SELECT * FROM my_table WHERE CAST(my_data->'priority'->>'rank' AS integer) >= 5;
 ```

diff --git a/postgres-json-cheatsheet.md b/postgres-json-cheatsheet.md
@@ -1,4 +1,4 @@
-# PostgreSQL JSON Sheetsheet
+# PostgreSQL JSON Cheatsheet
 
 Using JSON in Postgres by example.
 

diff --git a/postgres-json-cheatsheet.md b/postgres-json-cheatsheet.md
@@ -79,7 +79,7 @@ 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.
+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  
 ```SQL

diff --git a/postgres-json-cheatsheet.md b/postgres-json-cheatsheet.md
@@ -0,0 +1,179 @@
+# PostgreSQL JSON Sheetsheet
+
+Using JSON in Postgres by example.
+
+## Quick setup via Docker
+
+1. Download and install: [Docker Toolbox](https://www.docker.com/products/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)
+* `\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 my_table ( my_data JSONB );`
+5. `\d` Check that your new table has been created
+
+### Inserting data
+
+```SQL
+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
+
+Check your data has been inserted:  
+```SQL
+SELECT * FROM my_table;
+```
+
+Select a single JSON property:  
+```SQL
+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  
+```SQL
+SELECT my_data->>'name' AS name,
+       my_data->'priority'->>'name' AS priority
+FROM my_table;
+```
+
+### Sorting
+
+Sort by priority rank:  
+```SQL
+SELECT * FROM my_table ORDER BY CAST(my_data->'priority'->>'rank' AS integer) ASC;
+```
+
+Sort by date:  
+```SQL
+SELECT * FROM my_table ORDER BY to_date(my_data->>'date', 'YYYY-MM-DD');
+```
+
+### Filtering
+
+Find data from group-1:  
+```SQL
+SELECT * FROM my_table WHERE my_data->>'grouping' = 'group-1';
+```
+
+Finding data with a priority rank equal to or greater than 5:  
+```SQL
+SELECT * FROM my_table WHERE CAST(my_data->'priority'->>'rank' AS integer) >= 5;
+```
+
+Filtering data between a date range:  
+```SQL
+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:  
+```SQL
+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:  
+```SQL
+SELECT my_data->>'grouping' AS grouping,
+       count(my_data)
+FROM my_table
+GROUP BY my_data->>'grouping';
+```
+
+### Indexing
+
+Creating a new index:  
+```SQL
+CREATE INDEX my_grouping_index
+ON my_table ((my_data->>'grouping'));
+```
+
+Creating a new [multi-column index](http://www.postgresql.org/docs/current/interactive/indexes-multicolumn.html):  
+```SQL
+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
+
+* [Is PostgreSQL Your Next JSON Database?](https://www.compose.io/articles/is-postgresql-your-next-json-database/)
+* [Querying JSON in Postgres](http://schinckel.net/2014/05/25/querying-json-in-postgres/)
+* [What can you do with PostgreSQL and JSON?](http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/)
+* [Kartones/postgres-cheatsheet.md](https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546)