Skip to main content

Postgres

Open-source database. I prefer Postgres for my "only" SQL work as it is very powerful and can be amended a lot. Knowing databases is a super skill.

I think Postgres is way above the rest as it is efficient, open-source and performant. It is a super nice skill to have and work on this.

JSON and JSONB in Postgres

PostgreSQL offers native support for JSON data types. This feature allows you to store, query, and manipulate JSON data directly in PostgreSQL.

  • JSON: Stores data as text; parsing is done every time you fetch the data.
  • JSONB: Stores data in a binary format; parsing is done during insert, making reads faster.

Inserting JSON Data

INSERT INTO my_table (id, json_data)
VALUES (1, '{"key": "value", "array": [1, 2, 3]}');

Querying JSON Data

SELECT json_data->>'key' FROM my_table WHERE id = 1;

This query will return 'value'.

Advanced Queries You can also perform complex queries on JSON data.

SELECT json_data->'array'->>1 FROM my_table WHERE id = 1;

This query will return 2 because it accesses the second element in the "array" field. PostgreSQL provides a variety of JSON functions like json_array_length, json_each, and more.

SELECT json_array_length(json_data->'array') FROM my_table WHERE id = 1;

This query will return 3, the length of the array.

You can create indexes on JSONB columns for faster query performance.

CREATE INDEX idx_json_data ON my_table USING gin (json_data);

This feature is particularly useful for applications that need to store and query JSON data efficiently, without having to rely on external data stores or serialization/deserialization of JSON data.

Links

Thoughts

  • Open source is probably the best way of approaching databases, makes it easier to migrate later.