In modern applications, it is common to store semi-structured data in JSON format inside a relational database like PostgreSQL. However, to analyze this data properly, you need a way to transform it into a tabular structure that can be queried with standard SQL.

In this article, we will demonstrate a real-world example of reading a JSONB object stored in a table named bitcoin and retrieving exchange metrics as rows.

Table Structure

Suppose we have the following table:

CREATE TABLE IF NOT EXISTS bitcoin (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
)

The data column contains a JSON object like this:

{
  "x4t": { "ask": 113211.71, "bid": 108642.08, "time": 1757469418 },
  "xapo": { "ask": 111164.74, "bid": 110778.57, "time": 1757469418 },
  "fiwind": { "ask": 113747.74, "bid": 111528.27, "time": 1757469467 },
  "time": "2025-09-10T01:58:09.266128+00:00"
}

Each key (except for time) represents an exchange and contains fields such as ask, bid, and time.

Expanding JSON into Rows

To work with this data in a relational manner, we can use PostgreSQL’s jsonb_each function, which transforms a JSON object into a set of key–value pairs.

Here’s the query:

SELECT 
  key AS exchange,
  value->>'ask' AS ask,
  value->>'bid' AS bid,
  value->>'time' AS ts_unix,
  value->>'totalAsk' AS total_ask,
  value->>'totalBid' AS total_bid,
  b.data->>'time' AS global_time,
  b.created_at
FROM bitcoin b,
     jsonb_each(b.data)
WHERE key <> 'time';

Sample Output

This query produces a result like the following:

exchange	ask	bid	ts_unix	total_ask	total_bid	global_time	created_at
x4t	113211.71	108642.08	1757469418	113211.71	105382.82	2025-09-10T01:58:09.266128+00:00	2025-09-10 02:00:00
xapo	111164.74	110778.57	1757469418	111164.74	110778.57	2025-09-10T01:58:09.266128+00:00	2025-09-10 02:00:00
fiwind	113747.74	111528.27	1757469467	113747.74	111528.27	2025-09-10T01:58:09.266128+00:00	2025-09-10 02:00:00

Each exchange is returned as a separate row, making it much easier to apply aggregations, filters, and joins.

Benefits of This Approach

• Flexibility: No need to alter the schema when adding new exchanges.
• Compatibility: The data can be filtered, aggregated, and joined with standard SQL.
• Performance: PostgreSQL supports indexing on specific JSONB keys for faster lookups.