How to Use JSONB Metadata to Keep Web Visualization Data Compact

Keep the top-level grain aligned with what the visualization draws

For many custom web visualizations, the most useful result set is not the most normalized table and not the flattest possible export. It is a visualization-facing read model: one row per primary thing the visualization needs to draw, with the supporting detail for that thing close at hand.

Suppose a map draws one mark per country. The page needs the country's name, current value, region, and perhaps a few fields used for color or size. When a person clicks the country, the visualization also needs a 60-month timeseries, a trade-partner breakdown, and a few annotations.

A fully exploded result might repeat the country name, region, current value, and other parent fields once for every month or every partner. If you join several one-to-many cuts together before sending them to the browser, the result can become both long and repetitive.

A compact result can instead look like this:

country_id	country	current_value	metadata
DEU	Germany	84	{"timeseries":[...],"partner_breakdown":[...],"annotations":[...]}
FRA	France	79	{"timeseries":[...],"partner_breakdown":[...],"annotations":[...]}

The top-level table stays at one row per country. The nested JSONB value carries detail that belongs to that country and is normally used with it.

In this guide, metadata means a JSONB companion field used by the visualization. Some of the nested values may be descriptive metadata, while others may be genuine subordinate data such as a timeseries. If a name such as detail, viz_data, or payload is clearer in your schema, use it.

Why this can be more efficient

The first benefit is less repetition. An exploded timeseries repeats parent identifiers and labels across every observation. A nested array can store the parent fields once and the changing period/value pairs underneath them.

The second benefit is less browser-side data plumbing. The visualization can read row.metadata.timeseries or row.metadata.partner_breakdown directly instead of joining, grouping, or pivoting a large flat table after it loads. That can simplify D3 or JavaScript code and reduce the amount of temporary structure the page has to create before it draws anything.

The third benefit is a cleaner contract between the query and the visualization. Top-level columns can represent the fields that drive the main marks, filters, and encodings. JSONB can carry detail-on-demand for tooltips, click states, small multiples, sparklines, annotations, or secondary panels.

But fewer rows does not automatically mean fewer bytes. A nested document can still be large, and JSON property names add their own overhead. PostgreSQL stores jsonb in a structured binary representation for database processing, but a web application ultimately serializes that structure to JSON for the browser. The optimization is therefore the shape of the result, not a promise that JSONB itself makes network transfer smaller.

Package relevant additional data cuts with the parent row

This pattern is especially useful when the page has a primary view plus related detail that should be available immediately when the user interacts.

Useful JSONB contents can include:

• a timeseries used for a tooltip sparkline or detail chart;
• a category, partner, demographic, or product breakdown used after a click;
• precomputed comparison values such as rank, percentile, prior-period value, or change;
• annotations tied to dates or events;
• small arrays of geographic or network attributes that belong to the parent feature;
• tooltip facts or explanatory labels that would otherwise require another lookup;
• per-record display instructions such as a preferred label, icon key, or emphasis flag;
• provenance or source notes that need to travel with the record.

The common feature is that these values are subordinate to the parent record and are usually fetched at the same time. If a cut has its own independent analytical life, it probably deserves its own relational structure.

Build the JSONB after each cut has been aggregated

PostgreSQL gives you tools such as jsonb_build_object and jsonb_agg to construct nested results in SQL. A common pattern is to group a child table into one ordered JSONB array per parent and then attach that array to the parent row.

For a timeseries, the core expression can look like jsonb_agg(jsonb_build_object('period', period, 'value', value) ORDER BY period). You can then place that array under a key such as timeseries with jsonb_build_object.

If the visualization needs several one-to-many cuts, aggregate them separately before combining them. For example, build one JSONB timeseries per country and one JSONB partner breakdown per country, then join those two already-aggregated results to the country table. Do not join raw monthly rows to raw partner rows and aggregate afterward: that can create a month-by-partner multiplication before the JSON is built.

This is one of the strongest reasons to think of the query result as a read model. The database can do the expensive grouping once, close to the data, and hand the browser the structure it actually needs.

Use ordinary columns for fields that control the main visualization

Do not bury everything in JSONB. Keep the fields that define the primary grain and the main interactive controls at the top level.

If the map colors countries by current_value, filters by region, joins on country_id, and labels by country, those are good ordinary columns. The visualization and the database can work with them directly.

A useful rule is:

• Top-level columns: identity, joins, primary filters, sort keys, headline measures, and fields used across many views.
• JSONB metadata: subordinate arrays and details that travel with the parent and are normally consumed together.

That boundary keeps the result compact without sacrificing the relational operations that databases are good at.

Use JSONB as a visualization-facing read model, not necessarily the source of truth

There is no requirement that the underlying warehouse or application tables use this shape. Your source data can remain normalized. The compact JSONB result can be produced by a query, view, or materialized view specifically for the visualization.

That separation is often healthy. Normalized source tables preserve reusable facts, constraints, and joinability. The visualization-facing query packages only the subset and structure required by the page.

This also makes it easier to change the presentation later. A new visualization can build a different read model from the same source data without forcing the source schema to match one page's JavaScript.

How this works in Rhubarb

For a PostgreSQL data source, a Rhubarb query can return ordinary columns alongside a JSONB field. The nested object is included with the visualization data, so custom JavaScript can read a structure such as row.metadata.timeseries directly rather than requiring every point to exist as another top-level row.

A useful instruction to the Rhubarb Assistant is: “Keep one row per country. Preserve the current value and filter fields as ordinary columns. Package monthly history and the partner breakdown into a JSONB metadata field, and have the visualization read those nested arrays for the detail views.”

That gives the Assistant a clear target grain as well as a clear boundary between primary fields and supporting cuts. It can write the PostgreSQL query and the visualization code around the same data contract.

This does not bypass Rhubarb's data-size limits. The complete nested object still counts toward the serialized query result, which is exactly what you want from a safety boundary. The goal is to avoid unnecessary repetition and work, not to hide bytes inside one column.

Do not use JSONB when the child data needs independent analysis

JSONB is less attractive when the nested values are constantly filtered, joined, grouped, indexed, or updated on their own. PostgreSQL can query and index JSONB, but an ordinary relational column is often simpler when a field is a first-class analytical dimension.

Be cautious when:

• users need server-side filtering across individual points inside the nested timeseries;
• the same child records are shared by many parents and would be duplicated in each JSONB object;
• the nested object grows to thousands or millions of points per row;
• independent updates to a small child value would require rewriting a large parent document;
• downstream exports, BI tools, or analysts expect one observation per row;
• constraints and referential integrity are important for the nested elements.

If those conditions dominate, keep the child data relational and query or load it separately. A compact visualization payload should not make the underlying data harder to govern.

Measure the serialized payload, not the apparent width of the table

A four-column result can be larger than a forty-column result if one of those four columns contains a huge JSON document. Likewise, 200 parent rows can still contain 100,000 nested observations.

When you evaluate this pattern, compare at least three things:

1. the number of bytes actually serialized and sent to the browser;
2. the database time required to build the result;
3. the browser time and memory required to parse, group, and render it.

Also compare the JavaScript complexity. A slightly larger payload can still be a good trade if it eliminates multiple requests, repeated joins, or a large amount of browser-side reshaping. The right optimization target is a fast, understandable page—not the smallest row count in isolation.

A practical rule of thumb

Put a data cut into a JSONB metadata field when all three statements are mostly true:

1. the cut clearly belongs to one parent record;
2. the visualization usually needs it whenever that parent is loaded or interacted with;
3. the cut rarely needs to be filtered, joined, aggregated, or updated independently on the server.

Timeseries and compact breakdowns often fit that rule very well. Large reusable fact tables usually do not.

Used this way, JSONB is not a replacement for relational design. It is a way to create a purpose-built handoff between relational data and an interactive web visualization: keep the top-level table short, attach the relevant supporting cuts to the records that use them, and send the browser a structure that resembles the visualization it is about to render.

Frequently asked questions

Does JSONB automatically make a visualization payload smaller?

No. JSONB changes how structured data is stored and queried in PostgreSQL, but the browser still receives serialized JSON. The payload gets smaller only when the nested shape removes repetition, avoids unnecessary rows or fields, or lets you return only the cuts the visualization actually needs.

What kinds of data are good candidates for a JSONB metadata field?

Good candidates are subordinate structures that belong to one parent record and are normally consumed with it: a short timeseries, a category breakdown, tooltip facts, annotations, source notes, per-entity display settings, or a small set of precomputed comparison values.

When should I keep data relational instead of putting it in JSONB?

Keep data relational when you frequently filter, join, aggregate, validate, or update it independently, when the nested document becomes very large, or when many downstream tools expect a tidy table. JSONB works best as a deliberate visualization-facing read model, not as an excuse to hide an entire warehouse in one column.