by Craig Hafer on June 29, 2023

A few months ago I wrote a blog post that talks about Dealing with nested JSON objects in PostgreSQL. In there I talk about using the Stabby, Waffle Cone, and Penguin operators to navigate through a JSON object. In this post, I want to talk about what to do when that JSON object contains an array.

To keep this post simple, we're going to create a new table that has a jsonb column in it, add data to the column, and then navigate through it to get the data we want. If you already have your own data you want to navigate through, feel free to skip to the navigating portion. As a bonus at the very end, I will go over using the jsonb_array_elements postgres function.

Let's create a simple table that just has a jsonb column, so that we can test this out. We can do that with the following statement:

create table your_table_name (jsonb_column_name jsonb);

Now that we have the table setup, let's populate it with some data.

INSERT INTO your_table_name (jsonb_column_name)
VALUES ('{"key-1": [{"sub-key-1": "sub-value-1"},{"sub-key-2": "sub-value-2"}], "key-2": "value-2"}')
;

Here is a visual representation of this JSON object:

{
  "key-1": 
    [
      {"sub-key-1": "sub-value-1"},
      {"sub-key-2": "sub-value-2"}
    ], 
  "key-2": "value-2"
}

First, let's verify that our data is indeed where we expect it to be. We can check that by running this:

select jsonb_column_name from your_table_name;

We should see that it returns something similar to this:

Next, using the Stabby (->) operator, let's traverse through this column.

select jsonb_column_name->'key-1' as key_1 from your_table_name;

That will return to us the following:

Now, for the part we've all been waiting for! To traverse through the array, we simply use the Stabby operator again, "stabbing" whatever index of the array we want!

select 
jsonb_column_name->'key-1'->0 as sub_key_1, 
jsonb_column_name->'key-1'->1->'sub-key-2' as sub_value_2 
from your_table_name
;

This query should return the following:

As you can see, you can continue navigating through the objects inside the array, like normal, just using the Stabby operator after the array index.

This also works with negative numbers, to navigate from the end of the array, starting with -1.

select jsonb_column_name->'key-1'->-1 as sub_key_2 from your_table_name;

Which can be seen here:

If you grab from an index that is out of bounds, it will just return null.

select jsonb_column_name->'key-1'->999 as out_of_bounds_example from your_table_name;

Which can be seen here:

In this post we covered the fact that if there is an array in your JSON object, you simply use the Stabby operator to dig into the array, by its index. We saw that we are able to grab from the end of the array using a negative index. We also saw that going out of bounds in the array returned null.

As promised, I will now go over using the Postgres function jsonb_array_elements.

Let's say we needed to iterate over each element in the array from above. We wouldn't want to do something like this:

select jsonb_column_name->'key-1'->0, jsonb_column_name->'key-1'->1 from your_table_name

This would obviously get very tedious, even if we were building it out programmatically. So instead, we can use jsonb_array_elements on our jsonb array to do all of that for us!

To make this example make more sense, let's use some data that you might actually see in a real application. Here is the JSON data we will be using for this example:

{
  "users": [
    {"id": "91273", "name":"brian", "email":"brian@example.com"},
    {"id": "71867", "name":"dom",   "email":"dom@example.com", "sibling_ids": ["42680"]},
    {"id": "42680", "name":"mia",   "email":"mia@example.com", "sibling_ids": ["71867"]}
  ]
}

Here, we use our jsonb column to hold an array of users, with some information about the users.

As you can see, some data is specific to each user.

So, to give us each element in the array we would run this query:

select jsonb_array_elements(jsonb_column_name->'users') as users from your_table_name;

Which would return to us:

Here is an example grabbing multiple points of data:

select
jsonb_array_elements(jsonb_column_name->'users')->>'id' as id,
jsonb_array_elements(jsonb_column_name->'users')->>'name' as name
from your_table_name
;

Which would return to us:

Lastly, if data is specific only to certain elements of the array, it will just return null for indices that it doesn't apply to.

select 
jsonb_array_elements(jsonb_column_name->'users')->>'id' as id,
jsonb_array_elements(jsonb_column_name->'users')->'sibling_ids' as sibling_ids 
from your_table_name
;

Which can be seen here: