Скачать или смотреть Extracting Keys and Values from JSONB in PostgreSQL

Learn how to efficiently extract keys and values from a JSONB field in PostgreSQL, displaying them in separate columns.
---
This video is based on the question https://stackoverflow.com/q/74030078/ asked by the user 'Tms91' ( https://stackoverflow.com/u/7658051/ ) and on the answer https://stackoverflow.com/a/74030182/ provided by the user 'Stefanov.sm' ( https://stackoverflow.com/u/2302032/ ) 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: SQL: extract keys and values of a jsonb field as rows of two separate columns

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.
---
Extracting Keys and Values from JSONB in PostgreSQL: A Step-by-Step Guide

Handling JSON data can be a powerful tool in database management, especially when you're working with PostgreSQL. The JSONB type enables you to store JSON data in a binary format, which registers more efficiently and allows for quick access. However, one common challenge developers encounter is how to extract keys and values from a JSONB field and organize them into two separate columns.

The Problem

Suppose you have a PostgreSQL database containing a table with a JSONB field. The field stores information in a structured JSON format, as shown below:

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

You want to develop a SQL query that allows you to retrieve the keys and values in the following format:

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

Fortunately, PostgreSQL provides a solution for that.

The Solution

To achieve this, you'll want to use the jsonb_each() function, an invaluable tool in PostgreSQL when working with JSONB data. This function iterates over key-value pairs within your JSONB field and enables you to manipulate them as needed.

Step-by-Step Guide

Select the JSONB Field
Begin your query by selecting the desired JSONB field from your table:

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

Use the jsonb_each() Function
Next, apply the jsonb_each() function to iterate through the selected JSONB data:

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

Here’s a breakdown of this SQL statement:

jsonb_each() will transform the JSON object into a set of key-value pairs.

(value ->> 0) extracts the first element from the array associated with each key; this is pivotal, especially when the values are stored as arrays.

Example Query

To illustrate this in action, let’s say we directly insert the JSON as a literal in our SQL:

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

Executing this query would return the desired outcome:

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

Generalization of the Query

What if your JSON structure were to change? For example, if the values were not in arrays but were individual integers instead:

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

In this case, the query can be simplified as follows:

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

Conclusion

By utilizing PostgreSQL's JSONB capabilities along with the jsonb_each() function, you can efficiently extract and organize keys and values from JSONB fields. This not only enhances data readability but also improves data handling processes. Whether your values are in arrays or are single elements, you can adjust your queries accordingly.

In summary, with just a few lines of SQL code, you can sophisticatedly manage and retrieve JSONB data in your PostgreSQL database!