by Craig Hafer on February 9, 2023

Over the past few weeks I've been having to deal with nested JSON columns in Postgres. It was turning into me just throwing queries at it until something stuck. Well, as it would turn out, it's actually very simple! I just needed to take the time to sit down and learn about a few operators and what they're actually doing.

With that being said, here is a condensed version of some things I learned that helped me navigate through JSON columns in Postgres.

First, let's create a very basic table containing a json column

create table sample_table (json_data jsonb);

Now, let's seed this table with some data about a few different vehicles

insert into sample_table
values 
    ('{ "year": "2011", "make":"Toyota", "model":"Camry", "misc": {"color": "Gray", "doors": "4"}}'),
    ('{ "year": "2017", "make":"Honda", "model":"Civic", "misc": {"color": "White", "doors": "4"}}'),
    ('{ "year": "2017", "make":"Toyota", "model":"Camry", "misc": {"color": "Red", "doors": "2"}}'),
    ('{ "year": "2023", "make":"Honda", "model":"Accord"}'),
    ('{ "year": "1908", "make":"Ford", "model":"T", "misc": {"doors": "2"}}')
;

Now that we have our data, let's go over some of the basics.

If you've read my post about the Bacon Cannon, then you know that I am a firm believer that unique operators deserve unique names. So, as I cover some unique operators, I will be throwing in their nicknames, at no extra charge to you!

Using the -> (Stabby) operator will return JSON data. So here we can see a few examples of how we would get the json values out of the column:

select json_data -> 'make' from sample_table;
select json_data -> 'model' from sample_table;
select json_data -> 'year' from sample_table;
select json_data -> 'misc' from sample_table;

As you can see, 'misc' is a nested JSON object. If you want to go another layer deeper into a nested JSON object, we can simply add another ->

select json_data -> 'misc' -> 'color' as color from sample_table;

Now, as I said before, this isn't giving us text values. These quotations are an indicator that ->is returning JSON. In order to grab the text value we need to use the ->> (Double Stabby) operator. This is equivalent to adding ::text afterwards, typecasting it to a text.

select json_data -> 'misc' ->> 'color' as color from sample_table;

A simpler way to do nested JSON attributes is to use the #> (Waffle Cone) operator with an array of the path you want to follow.

select json_data #> Array['misc','color'] as color from sample_table;

Just like before, a single > returns JSON, so if we wanted to grab the actual text value, we'll need to use the #>> (Double Waffle Cone) operator.

select json_data #>> Array['misc','color'] as color from sample_table;

Okay, but what if you want the entire row that matches a certain criteria? We'll move this into a where clause, and then use the ? operator.

A good way to think of this operator is to replace it with the word "contains".

For example the next query would read in english as

"select all from sample_table where json_data's misc doors contains the value 4"

This query will return to us every row where there is a 4 door car

select * from sample_table
where json_data #> Array['misc', 'doors'] ? '4'
;

This query will return to us every row containing a Toyota or a Honda

select * from sample_table
where json_data -> 'make' ?| Array['Toyota','Honda']
;

This query will return to us every row containing both color and doors keys inside of misc.

select * from sample_table
where json_data -> 'misc' ?& Array['color', 'doors']
;

(Notice this doesn't contain the row that has just {"misc": {"doors": "2"}}

If you are looking for a specific JSON object inside of another, you want to use the @> *(Penguin)*operator.

For example, if were looking specifically for {"key": "value"}:

select * from sample_table
where json_data @> '{"make": "Toyota"}'
;

As you might have guessed, we can look for nested JSON objects

This will return to us every row containing red, 2-door vehicles"

select * from sample_table
where json_data @> '{"misc": {"color":"Red", "doors":"2"}}'
;

There is also the <@ (Ice Cream Cone or Reverse Penguin) operator that behaves the same, only in reverse.

select * from sample_table
where '{"misc": {"color":"Red", "doors":"2"}}' <@ json_data
;

I hope this helps clear up any confusion you might have had about querying nested JSON objects in Postgres.

If you want to learn more about these operators or if you want to see what else you can do, here is a link to the docs for JSON functions in Postgres.