How to flatten JSON array in SQL using jsonb_array_elements?

How to flatten JSON array in SQL using jsonb_array_elements?

Written by Bobby Iliev on Oct 19th, 2022 ・ Views ・ Report Post

Bobby Iliev Visit User Profile

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:

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

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

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:

SELECT * FROM users;

Output:

 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:

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

Output:

 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

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

🤩 Our Amazing Sponsors 👇
DigitalOcean View Website
DigitalOcean offers a simple and reliable cloud hosting solution that enables developers to get their website or application up and running quickly.
Laravel News View Website
Laravel News keeps you up to date with everything Laravel. Everything from framework news to new community packages, Laravel tutorials, and more.
Genesis View Website
A Laravel Starter Kit that includes Authentication, User Dashboard, Edit Profile, and a set of UI Components. 
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:

SELECT * FROM users_hobbies;

Output:

 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 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.

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

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

Materialize Documentation

Comments (0)