Скачать или смотреть Solving the JSON Query Issue: Extracting Data from Multiple Rows in PostgreSQL

Learn how to efficiently select JSON data from multiple rows in PostgreSQL by using LATERAL joins for matched keys.
---
This video is based on the question https://stackoverflow.com/q/73657125/ asked by the user 'NaN' ( https://stackoverflow.com/u/17283697/ ) and on the answer https://stackoverflow.com/a/73663442/ provided by the user 'jjanes' ( https://stackoverflow.com/u/1721239/ ) 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: Select JSON from multiple rows by keys Postgress

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.
---
Solving the JSON Query Issue: Extracting Data from Multiple Rows in PostgreSQL

In the world of data handling and manipulation, encountering issues while querying your database is common. One such issue arises when trying to select JSON data from multiple rows based on specific keys in PostgreSQL. If you are faced with an error message stating that "more than one row returned by a subquery used as an expression," you're not alone. This guide will walk you through the problem and offer a clear solution to achieve your desired results.

The Problem Explained

Imagine you have a table named hit_count_13306823936951630992 containing a JSONB column named data. Your goal is to extract specific key-value pairs from this JSONB data using SQL. However, executing the following query results in an error:

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

The error arises because you are trying to use a subquery that returns multiple rows for a function which expects a single row. In this case, the jsonb_each() function is being applied incorrectly across the entire table.

What's Missing?

The key mistake here is trying to process the JSONB data of the table collectively. In order to correctly extract data, you need to manage each row individually. This is where LATERAL joins come into play.

The Solution: Using LATERAL Joins

To resolve this issue, you can use a CROSS JOIN LATERAL, which allows you to expand each row into multiple rows according to the structure of the JSONB data. By doing this, you will be able to filter and aggregate the data correctly without running into subquery issues.

Here’s the Updated Query:

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

Breakdown of the Query:

CROSS JOIN LATERAL: This join type allows you to invoke the jsonb_each() function on each individual row of the data column. It effectively treats the rows of hit_count_13306823936951630992 as individual sets for further processing.

jsonb_each(data): This function decomposes the JSONB object into a set of key-value pairs for each row, enabling you to access specific keys.

Filtering with WHERE key IN: By specifying which keys you are interested in (in this case, 8517 and 1099), you can limit the results to only those key-value pairs that meet your criteria.

Conclusion

Using LATERAL joins in PostgreSQL is an effective solution to retrieve specific JSON key-value pairs across multiple rows in your table. Instead of attempting to pull all data at once, treating each row independently allows you to avoid common pitfalls and errors.

This method is not only efficient but also aligns perfectly with how relational databases operate, ensuring you can manipulate and extract data with ease.

Now you can implement this knowledge in your SQL queries and make the most out of your JSON data in PostgreSQL! Happy querying!