# How to flatten JSON array in SQL using jsonb_array_elements?

By Bobby Iliev, 2022-10-19. https://bobbyiliev.com/post/how-to-flatten-json-array-in-sql-using-jsonb-array-elements/

## Introduction

The `jsonb_array_elements` function returns a set of rows, one for each element of the input array. The input array can be a top-level array or a nested array.

In this tutorial, we will learn how to flatten JSON array in SQL using `jsonb_array_elements`.

## `jsonb_array_elements` Example

Let's start by creating a table with a JSON column:

```sql
CREATE TABLE users (
    id INT,
    name TEXT,
    data JSONB
);
```

Next, let's insert some data into the table:

```sql
INSERT INTO users VALUES
    (1, 'John', '{"age": 30, "hobbies": ["gaming", "coding"]}'::JSONB),
    (2, 'Jane', '{"age": 25, "hobbies": ["reading", "cooking"]}'::JSONB);
```

After that query the table as normal:

```sql
SELECT * FROM users;
```

Output:

```sql
 id | name |          data
----+------+-----------------------
  1 | John | {"age": 30, "hobbies": ["gaming", "coding"]}
  2 | Jane | {"age": 25, "hobbies": ["reading", "cooking"]}
(2 rows)
```

Let's use the `jsonb_array_elements` function to flatten the `hobbies` array:

```sql
SELECT
    id,
    name,
    data->>'age' AS age,
    hobby
FROM users, jsonb_array_elements(data->'hobbies') hobby;
```

Output:

```sql
 id | name | age | hobby
----+------+-----+-------
  1 | John |  30 | gaming
  1 | John |  30 | coding
  2 | Jane |  25 | reading
  2 | Jane |  25 | cooking
(4 rows)
```

## Creating a [Materialized View](https://materialize.com/docs/sql/create-materialized-view/)

Now that we have our query, let's create a materialized view with the `jsonb_array_elements` function to store the flattened data:

```sql
CREATE MATERIALIZED VIEW users_hobbies AS
    SELECT
        id,
        name,
        data->>'age' AS age,
        hobby
    FROM users, jsonb_array_elements(data->'hobbies') hobby;
```

Let's query the materialized view:

```sql
SELECT * FROM users_hobbies;
```

Output:

```sql
 id | name | age | hobby
----+------+-----+-------
  1 | John |  30 | gaming
  1 | John |  30 | coding
  2 | Jane |  25 | reading
  2 | Jane |  25 | cooking
```

With PostgreSQL, when creating a materialized view, your query is executed and the results are stored in the materialized view. This means that the materialized view is not updated when the underlying table is updated. To update the materialized view, you need to run the [`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html) command.

If you want to have a live materialized view, that is incrementally updated in milliseconds when the underlying table is updated, you can use Materialize. Materialize is a streaming SQL database that supports incremental updates. You can learn more about Materialize [here](https://materialize.com?utm_source=bobbyiliev).

## Conclusion

This tutorial showed you how to flatten JSON array in SQL using `jsonb_array_elements`.

To learn more about SQL in general, check out this free SQL eBook:

> [Free Introduction to SQL eBook](https://sql.bobby.sh/)

To learn more about Materialize, check out the official documentation:

> [Materialize Documentation](https://materialize.com/docs/)