What data type to choose json or jsonb or text

Assuming you’re talking about actual, strict JSON (without quirks such as unquoted keys)…

json isn’t too different from text. It doesn’t do much apart from validating JSON.

jsonb is a different beast compared to the these two: it’s a full-fledged data structure with its own internal format that has much more operations available in searches. For instance json has no applicable = (equality operator). jsonb has. (text has too, even though it’s semantically different.)

It’s much more sensible to index, but it has to be transformed back and forth during reads and writes.

Given that, jsonb doesn’t look like a sound choice here.

… So there is only one decision left to make:

Do you want to ensure that your database contains only valid JSON values in your column? On the database level? Or do you trust every client of that database (server apps, typically) to only supply valid data?

json is a relatively safe choice either way. Using text could theoretically improve performance by a negligible margin due to absence of validation, but you’ll only get specific numbers by benchmarking. But it wouln’t have that safeguard against non-JSON values, and an accidental bug in the client could go unnoticed. Test responsibly!

Leave a Comment