Making JSONB Feel Less Cumbersome for MongoDB Users

A customer recently asked a great question about working with jsonb attributes in YugabyteDB:

1. Are JSONB attribute values always returned as text?
2. MongoDB users might view the need for casting as cumbersome.
3. Are there techniques to lessen the amount of code or casting?

This is a very reasonable question, especially for users coming from MongoDB.

MongoDB documents can naturally store polymorphic values, and if you insert a numeric value, MongoDB aggregation operations can generally treat that value as numeric. In SQL, the rules are more explicit. A JSONB document can contain a JSON number, but SQL aggregate functions like AVG(), SUM(), and arithmetic expressions operate on SQL types such as integer, numeric, or double precision.

The good news is that YugabyteDB gives you several techniques to make this cleaner.

The first step is understanding the difference between the JSONB operators.

`->` vs. `->>`

If you use ->>, the value is returned as text.

If you use ->, the value is returned as jsonb.

Here is a simple example:

				
					SELECT
    '{"WhatAMI?":1}'::jsonb AS some_jsonb,
    '{"WhatAMI?":1}'::jsonb->'WhatAMI?' AS value_as_jsonb,
    '{"WhatAMI?":1}'::jsonb->>'WhatAMI?' AS value_as_text;

Example output:

				
					   some_jsonb    | value_as_jsonb | value_as_text
-----------------+----------------+---------------
 {"WhatAMI?": 1} | 1              | 1
(1 row)

At first glance, both extracted values look the same.

But they are not the same type.

In ysqlsh, you can use \gdesc to inspect the result column types:

				
					\gdesc

Example output:

				
					.   Column      | Type
----------------+-------
 some_jsonb     | jsonb
 value_as_jsonb | jsonb
 value_as_text  | text
(3 rows)

✅ Rule of Thumb

Use -> when you want to preserve the value as jsonb. Use ->> when you intentionally want the scalar value as text.

Why `AVG()` Still Needs a SQL Numeric Type

Preserving the value as jsonb does not automatically make it a SQL number.

For example, this query does not work:

				
					SELECT avg(('{"price": 10.50}'::jsonb->'price'));

The value inside the JSON document is numeric, but the SQL function AVG() does not operate on jsonb.

To use the value in a SQL aggregate, extract it and cast it to a SQL numeric type:

				
					SELECT avg(('{"price": 10.50}'::jsonb->>'price')::numeric);

Example result:

				
					.        avg
---------------------
 10.5000000000000000
(1 row)

For SQL math, comparisons, sorting, and aggregation, cast the extracted value to the SQL type you need.

Example Table

Let’s create a simple table with a JSONB payload:

				
					CREATE TABLE orders_jsonb (
    id bigserial PRIMARY KEY,
    payload jsonb NOT NULL
);

Insert a few documents:

				
					INSERT INTO orders_jsonb (payload) VALUES
    ('{"customer": "Dan", "amount": 19.95, "status": "paid"}'),
    ('{"customer": "Bob",   "amount": 42.50, "status": "paid"}'),
    ('{"customer": "Joe", "amount": 13.25, "status": "pending"}');

Now calculate the average order amount:

				
					SELECT avg((payload->>'amount')::numeric) AS avg_amount
FROM orders_jsonb;

Example result:

				
					.    avg_amount
---------------------
 25.2333333333333333

That works, but yes, repeating the cast everywhere can feel a little noisy.

Fortunately, you have several options to reduce that repetition.

Technique 1: Use a View to Hide the Cast

One simple way to reduce repeated casting is to create a view that exposes commonly used JSONB attributes as typed SQL columns.

				
					CREATE VIEW orders_jsonb_v AS
SELECT
    id,
    payload,
    payload->>'customer' AS customer,
    (payload->>'amount')::numeric AS amount,
    payload->>'status' AS status
FROM orders_jsonb;

Now your query becomes much cleaner:

				
					SELECT avg(amount) AS avg_amount
FROM orders_jsonb_v;

You can also group by status naturally:

				
					SELECT
    status,
    count(*) AS order_count,
    avg(amount) AS avg_amount
FROM orders_jsonb_v
GROUP BY status
ORDER BY status;

🛠️ Practical Pattern

If your application stores flexible JSONB documents but frequently queries the same attributes, create a view that exposes those attributes as normal typed SQL columns.

Technique 2: Add a Check Constraint for Type Safety

If the application expects amount to always be numeric, you can enforce that at the database layer.

				
					ALTER TABLE orders_jsonb
ADD CONSTRAINT orders_jsonb_amount_is_number
CHECK (
    jsonb_typeof(payload->'amount') = 'number'
);

Now this insert succeeds:

				
					INSERT INTO orders_jsonb (payload)
VALUES ('{"customer": "Jason", "amount": 99.99, "status": "paid"}');

But this one fails:

				
					INSERT INTO orders_jsonb (payload)
VALUES ('{"customer": "Paige", "amount": "not-a-number", "status": "paid"}');

Example output:

				
					ERROR:  new row for relation "orders_jsonb" violates check constraint "orders_jsonb_amount_is_number"
DETAIL:  Failing row contains (5, {"amount": "not-a-number", "status": "paid", "customer": "Paige"...).

This gives you some of the schema safety of a relational model while still keeping the flexibility of JSONB.

Technique 3: Use a Generated Column for Frequently Queried Attributes

For attributes that are queried often, you can expose the JSONB value as a real typed column.

				
					CREATE TABLE orders_jsonb_generated (
    id bigserial PRIMARY KEY,
    payload jsonb NOT NULL,
    amount numeric GENERATED ALWAYS AS ((payload->>'amount')::numeric) STORED
);

Insert some documents:

				
					INSERT INTO orders_jsonb_generated (payload) VALUES
    ('{"customer": "Alice", "amount": 19.95, "status": "paid"}'),
    ('{"customer": "Bob",   "amount": 42.50, "status": "paid"}'),
    ('{"customer": "Carol", "amount": 13.25, "status": "pending"}');

Now the query is clean:

				
					SELECT avg(amount) AS avg_amount
FROM orders_jsonb_generated;

You can also index the generated column:

				
					CREATE INDEX orders_jsonb_generated_amount_idx
ON orders_jsonb_generated (amount ASC);

⚙️ Design Tip

JSONB is great for flexible attributes, but if an attribute becomes important for filtering, joining, sorting, or aggregation, consider promoting it to a typed column, generated column, or view expression.

Technique 4: Use a Range-Sharded Expression Index

If you commonly filter on a JSONB numeric attribute using range predicates, create an expression index and specify ASC or DESC.

				
					CREATE INDEX orders_jsonb_amount_expr_idx
ON orders_jsonb (((payload->>'amount')::numeric) ASC);

ANALYZE orders_jsonb;

The ASC matters in YugabyteDB. Without it, the first index key may default to HASH, which is useful for equality lookups but not ideal for range predicates like >=, <, or BETWEEN.

Now the range query can use the expression index:

				
					EXPLAIN
SELECT *
FROM orders_jsonb
WHERE (payload->>'amount')::numeric >= 995;

Example plan:

				
					.                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Index Scan using orders_jsonb_amount_expr_idx on orders_jsonb  (cost=56.82..217.52 rows=525 width=86)
   Index Cond: (((payload ->> 'amount'::text))::numeric >= '995'::numeric)
(2 rows)

MongoDB Perspective

MongoDB users are used to inserting flexible documents like this:

				
					{ "amount": 19.95 }

And then using aggregation pipelines that understand numeric BSON values.

YugabyteDB’s YSQL layer follows PostgreSQL-compatible SQL semantics. A JSONB document can contain a JSON number, but when you want SQL functions like AVG(), SUM(), comparisons, joins, or indexes to operate on that value, you should expose it as a SQL type.

That can feel like extra code at first, but it also gives you more explicit control.

You can decide where the conversion belongs.

Technique	Best For
`->`	Keeping the value as `jsonb`
`->>`	Extracting a scalar value as `text`
Explicit casts	Ad hoc queries and simple expressions
Views	Hiding repeated JSONB extraction and casting logic
Check constraints	Enforcing expected JSONB attribute types
Generated columns	Frequently queried JSONB attributes that should behave like typed columns
Expression indexes	Indexing typed values extracted from JSONB

Final Takeaway

JSONB attribute values are not always returned as text. It depends on the operator:

● Use -> when you want the result as jsonb.
● Use ->> when you want the result as text.

For SQL aggregation, filtering, sorting, and indexing, you usually want to cast JSONB scalar values into proper SQL types. To reduce repeated code, centralize that logic with views, generated columns, check constraints, or expression indexes.

For YugabyteDB specifically, remember that index sharding matters. If you create an expression index to support range predicates such as >=, <, or BETWEEN, define the indexed expression with ASC or DESC.

For example:

				
					CREATE INDEX orders_jsonb_amount_expr_idx
ON orders_jsonb (((payload->>'amount')::numeric) ASC);

Without ASC or DESC, the first index key may use hash sharding by default, which is better suited for equality lookups than range scans.

🚀 Final Thought

YugabyteDB gives you flexible JSONB storage and relational SQL power. Use JSONB for flexibility, promote important attributes into typed SQL expressions when needed, and choose the right index ordering for your access pattern. For range queries on JSONB-derived values, ASC or DESC can make the difference between a sequential scan and an efficient index scan.

Have Fun!