When I joined SpringTree I left behind my trusty PHP + MySQL stack and switched (mostly) to a Node.js + PostgreSQL environment. And while the switch from PHP to typescript was very interesting, in this blog I want to focus on PostgreSQL and what it has to offer and things I like about it. Now, not everything I’m going to be writing about is just related to PostgreSQL and could be done in a lot of other database systems, but the syntax I will be using for them will be PostgreSQL based. With each heading, I will supply a db-fiddle, so you can see the table structures we’re working with and can play along while reading.

Working with JSON

https://www.db-fiddle.com/f/vbyLzBQCyTrn7vtKLQfFmx/1

PostgreSQL has a lot of build in helper functions to help you work with JSON data in a table. Let’s do a little crash course and work our way up. Getting data from a JSON object is as easy as using an arrow, or a double-headed arrow `select data -> value` will return a JSON object, while `select data ->> value` will return a string, this can be combined with type casting to get whatever you want. Let’s see some examples:

select json_data ->> 'value'
FROM temp_data;

This will get us all the JSON data, but what if we just want a subset? Let’s see how we can get data that includes a piece of a JSON object:

select json_data ->> 'job', (json_data ->> 'value')::int
from temp_data
where json_data @> '{"job":"programmer"}'::jsonb;

So here we make a new JSON object and see if that is contained within the object we’re searching trough, awesome! Of course, we could have also done something like `where json_data ->> ‘job’ = ‘programmer’` but the first way is more flexible.

What if we just want all the data that has a ‘job’ key in the JSON data? Again, super easy in PostgreSQL:

select json_data ->> 'job', (json_data ->> 'value')::int
from temp_data
where json_data ? 'job';

Working with JSON arrays

JSON doesn’t just have objects, we can also have arrays with whatever we want. We can do some fun stuff with this, let’s unfold the values, so we get some rows:

select jsonb_array_elements(json_array) as id
from temp_data

So instead of the one row we are expecting, we are getting 3 rows with a single id. This is because we turned the array elements into rows, with this data we can do whatever we want:

select temp_data.id, temp_data.json_data
from (select jsonb_array_elements(json_array) as id
 from temp_data
) json_rows
left join temp_data on json_rows.id::int = temp_data.id;

Here, we use the values within the array to join rows in our own table based on their id’s and return all the data within.

Alright, time for some other stuff, let’s make a new table and start working with that:

create table temp_user(
	id serial primary key, 
   	name text,
   	job text,
   	value int,
   	age int
);

insert into temp_user (name, job, value, age) values ('Pieter', 'developer', 777, 31);
insert into temp_user (name, job, value, age) values ('Jan', 'developer', 888, 99);

Let’s look at an opposite example, say we’re working with a normal table structure, so no JSON columns, but we need some JSON output. Let’s build some.

select json_agg(json_build_object('id', id, 'name', name, 'value', value, 'age', age)) from temp_user;`

Here we use 2 different functions, first we build a JSON object for each row where we first supply a string that will be the key in the object we are building, and then we pass a value, which we get from the row we’re working on. The other function gathers all those objects and aggregates them in a JSON string. We’ve just turned this new table into a JSON string holding each row as an object!

Window function

https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/4762

Let’s move on to some other powerful stuff, window functions. These let you do calculations across a set of rows that are related to the current row. Let’s look at an example:

CREATE TABLE sales (
  name text,
  type text,
  value integer
);

INSERT INTO sales VALUES ('Book #1', 'book', 100);
INSERT INTO sales VALUES ('Book #2', 'book', 200);
INSERT INTO sales VALUES ('Book #3', 'book', 300);

INSERT INTO sales VALUES ('Car #1', 'car', 69000);
INSERT INTO sales VALUES ('Car #2', 'car', 35000);
INSERT INTO sales VALUES ('Car #3', 'car', 8100);

The table we’re working with has 2 kinds of sales data, books and cars. Say we want to get the average sales price and the percentage this sale was of the total number. This is made very easy with some window functions:

select 
  name, 
  value,
  avg(value) over (partition by type) as avg_value,
  value / sum(value) over (partition by type)::float * 100 as percentage_total
from sales;

What if we have a lot of sales, and we only want the top 2 sales of each type. This again is made very easy with window functions.

select 
  name, 
  value,
  avg(value) over (partition by type) as avg_value,
  value / sum(value) over (partition by type)::float * 100 as percentage_total,
  row_nr
from (select 
*,
 row_number() OVER (partition by type order by value desc) as row_nr
 from sales
) sales
where row_nr in (1,2);

In the sub query, we select the data from our table, and we add row numbers based on the value by ordering it. Then we limit the results by only selecting the row numbers 1 and 2, and from there we do the same thing we did before. As you can see, window functions can be very powerful, and can greatly enhance your performance if used right, for example limiting the amount of rows you do calculations on to join other tables on.

Static data

https://www.db-fiddle.com/f/4BNvZFybMupr4hcoXiqwSe/0

Sometimes it’s very handy to have a set piece of data available in your query, but you don’t want to get it for every row. In comes the `with` query, this gives you the ability to run complex query(s) and store the result for use within your main query. While this might sound daunting this is very easy to do, let’s see an example:

with total_sales as (
select 
sum(value) as total
from sales
)
select 
sales.*,
 ( select total from total_sales) as total_sales
 from sales;

So here we see 2 queries, one following the `WITH` and contained within parentheses () and one following. The one that follows is using data that is returned from the first, you can use any query you want within the with query such as window functions, complex joins, groupings etc. These results are returned and are able to be used like a table, so we can select from it (as in the example) or we can join it in our main query. You can do very complex things with queries, especially when you go recursive, such as looking through trees. Luckily, the PostgreSQL documentation has some very good examples for this, so I’ll just point you in that direction.

I hope you’ve learned something new, or maybe I just refreshed that part of your brain where long-forgotten things dwell. Either way, thanks for reading this blog.

Here are some documentation links if you want to follow up on the topics discussed here:

Json functions

Window functions

With query