Скачать или смотреть How to Split SQL Table with JSON Fields into Multiple Rows in PostgreSQL

Discover how to easily transform your PostgreSQL tables containing JSON fields into multiple rows using the `jsonb_each` function.
---
This video is based on the question https://stackoverflow.com/q/71175184/ asked by the user 'Zapan' ( https://stackoverflow.com/u/18244025/ ) and on the answer https://stackoverflow.com/a/71175515/ provided by the user 'Ftisiot' ( https://stackoverflow.com/u/7605762/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Split SQL Table with fields containing JSON into multiple rows

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
A Guide to Splitting SQL Tables with JSON Fields in PostgreSQL

JSON has become an increasingly popular format for data storage, especially when working with relational databases like PostgreSQL. However, when you want to manipulate or query data stored in JSON format, you may find yourself facing a challenge. Specifically, how do you split a single SQL table containing fields with JSON into multiple rows? In this post, we will explore a solution to this common problem, breaking it down into clear and manageable steps.

Understanding the Problem

Imagine you have a PostgreSQL table, t, structured like this:

idjson1{"tag1":45, "tag2": 3, "tag5": 10}2{"tag5":35, "tag6": 7, "tag8": 10, "tag10": 4}3{"tag2":10, "tag800": 6}In this table, you have an id field and a json field. The JSON data represents various tags and their respective values. Your goal is to transform this data into a more structured format, producing a table that looks like this:

idkeyValue1tag1451tag231tag5102tag5352tag672tag8102tag1043tag2103tag8006Why This Matters

Converting JSON data into a row-based format is crucial for reporting and analysis. Properly structured tables allow you to perform SQL queries and aggregations, making your data easier to understand and work with.

The Solution: Using jsonb_each in PostgreSQL

To achieve the desired transformation, we will utilize the powerful jsonb_each function provided by PostgreSQL. This function allows you to expand a JSON object into a set of key-value pairs, which can then be manipulated as needed.

Step 1: Creating the Table

First, create a new table to hold your JSON data:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Inserting Data

Next, populate your table with the JSON data:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Querying the Data

Now it's time to transform your JSON data into multiple rows. Use the following SQL query to do so:

[[See Video to Reveal this Text or Code Snippet]]

In this query:

test is the name of your table holding the data.

jsonb_each(test.mydata) expands each jsonb field into a set which can be selected alongside the id.

Step 4: Running the Query

By executing the above SQL statement, you will obtain the desired output, where each tag from your JSON data appears in its own row with the corresponding ID and value.

Conclusion

Working with JSON data in PostgreSQL can be straightforward, especially with the right tools at your disposal. By leveraging the jsonb_each function, you can easily turn nested JSON structures into a flat, manageable format suitable for queries and analysis.

Now that you know how to split an SQL table with JSON fields into multiple rows, you can effectively work with large datasets and implement meaningful insights from your data. If you have any questions or need further clarification, feel free to reach out in the comments!