Comparing JSON with Other Types: ARRAY, TEXT, HSTORE

In PostgreSQL, a lot of devs run into the question: which data structure should you use to store your info? Should you go with arrays (ARRAY) for simpler data? Or text (TEXT) columns for strings? Maybe HSTORE for storing key-value pairs? And of course, the big one: when is it actually better to use JSON or JSONB?

To help you figure this out, let's talk about the pros and cons of each approach, plus some usage examples.

When to Use Arrays (ARRAY) vs. JSONB?

Arrays (ARRAY) are awesome for data that's just a list of values of the same type. For example, if you have a list of student grades or tags for a record, arrays are perfect.

Array example:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    grades INTEGER[] -- array of grades
);

INSERT INTO students (name, grades)
VALUES ('Alice', ARRAY[90, 85, 88]),
       ('Bob', ARRAY[70, 75, 78]);

On the other hand, JSONB is great for more complex, nested structures. If you want to store extra info for each record, like a description for each grade, JSONB is the way to go.

JSONB example:

CREATE TABLE students_json (
    id SERIAL PRIMARY KEY,
    name TEXT,
    grades JSONB -- object with grade data
);

INSERT INTO students_json (name, grades)
VALUES ('Alice', '{"Math": 90, "Science": 85, "English": 88}'),
       ('Bob', '{"Math": 70, "Science": 75, "English": 78}');

Main Differences

Examples of Converting Between Array and JSONB

Let's see how you can convert data between arrays and JSONB:

Array → JSONB

SELECT to_jsonb(grades) AS grades_jsonb
FROM students;

-- Result:
-- [{"90","85","88"}]

JSONB → Array

SELECT array_agg(value::INTEGER) AS grades_array
FROM jsonb_array_elements_text('["90", "85", "88"]');
-- Result:
-- {90,85,88}

Comparing JSONB and Text Data (TEXT)

Text columns are perfect if you just need to store strings or small unstructured data. If your task is searching for string matches, like in a product name or description, TEXT is your go-to.

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
);

INSERT INTO books (title, description)
VALUES ('SQL Basics', 'A concise introduction to SQL'),
       ('Advanced PostgreSQL', 'An in-depth guide to PostgreSQL performance');

When Is JSONB Better?

If your string turns into info with a nested structure (like a description with a nested category and a list of tags), JSONB is a better pick.

CREATE TABLE books_json (
    id SERIAL PRIMARY KEY,
    info JSONB
);

INSERT INTO books_json (info)
VALUES ('{"title": "SQL Basics", "tags": ["beginner", "database"]}'),
       ('{"title": "Advanced PostgreSQL", "tags": ["performance", "optimization"]}');

Main Differences

Comparing JSONB and HSTORE

HSTORE is like the older sibling of JSONB, letting you store key-value pairs. For simple data structures (no nesting or arrays needed), HSTORE is lighter and faster.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    attributes HSTORE
);

INSERT INTO products (attributes)
VALUES ('"color"=>"red", "size"=>"M"'),
       ('"color"=>"blue", "size"=>"L"');

Why Did JSONB Replace HSTORE?

Even though HSTORE is handy for key-value pairs, it doesn't support nesting or arrays, which makes JSONB way more flexible. If you've outgrown simple objects, JSONB is the natural next step.

Main Differences

How to Pick the Right Data Type?

If you have:

• A simple structure — lists or homogeneous data, use arrays (ARRAY).
• Simple strings or descriptions, use text columns (TEXT).
• Key-value pairs with no nesting, go for HSTORE.
• Nested objects and arrays, complex data structure — you want JSONB.

Examples of Converting Between Formats

TEXT → JSONB

SELECT to_jsonb('Simple text example') AS jsonb_form;
-- Result: "Simple text example"

JSONB → TEXT

SELECT info::TEXT AS text_form
FROM books_json;
-- Result: {"title": "SQL Basics", "tags": ["beginner", "database"]}

HSTORE → JSONB

SELECT hstore_to_jsonb(attributes) AS jsonb_form
FROM products;
-- Result: {"color": "red", "size": "M"}

JSONB → HSTORE

SELECT jsonb_to_hstore('{"color": "red", "size": "M"}') AS hstore_form;
-- Result: "color"=>"red", "size"=>"M"

What Should You Watch Out For?

If you need max flexibility and support for complex structures, go with JSONB. But if your task is simple data structures like arrays, text, or key-value pairs, use the matching data types (ARRAY, TEXT, HSTORE).

Don't forget, picking the right data structure will save you a ton of headaches later on and make your queries run faster.