How to JSON in PostgreSQL®

This series covers how to solve common problems on JSON datasets with PostgreSQL® and it includes (links will appear once the target pages are up):

Common JSON questions

• What are the differences between JSON or JSONB in PostgreSQL?
• How to load JSON data in PostgreSQL?

Parse JSON

• How to extract a field from a JSON object in PostgreSQL?
• How to get the JSON field types in PostgreSQL?
• How to check if JSON contains in PostgreSQL?
• How does JSON path work in PostgreSQL?
• How to parse JSON arrays in PostgreSQL?
• How to parse JSON keys in PostgreSQL?

Index JSON

• How to use GIN indexes over JSON in PostgreSQL?

Tabulate JSON to record or recordset

• How to tabulate a JSON to a record in PostgreSQL?
• How to tabulate a JSON to a recordset in PostgreSQL?

Convert to JSON

• How to convert a row to JSON in PostgreSQL?
• How to create a JSON object from keys and values arrays in PostgreSQL?
• How to create a JSON object from array of key/value pairs in PostgreSQL?
• How to build a JSON array from a list of elements in PostgreSQL?
• How to convert an array to a JSON array in PostgreSQL?

Prettify JSON output

• How to prettify the JSON output in PostgreSQL?

Edit JSON

• How to concatenate two JSON documents in PostgreSQL?
• How to remove fields from a JSON document in PostgreSQL?
• How to remove an item from a JSON array in PostgreSQL?
• How to remove nulls JSON object in PostgreSQL?
• How to edit a JSON document in PostgreSQL?

👉 Want a FREE PostgreSQL database?👈
🦀 Check Aiven’s FREE plans! 🦀

⚡️ Want to optimize your SQL query with AI? ⚡️
Check Aiven SQL Query Optimizer!

⚡️ Want a Fully PostgreSQL optimized Database? ⚡️
Check Aiven AI Database Optimizer!
Powered by EverSQL, it provides you index and SQL rewrite recommendations to take your database performance to the next level