How to convert an array to a JSON array in PostgreSQL

- One minute read - 167 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function array_to_json (array_to_jsonb for JSONB) to create a JSON array from an existing array.

NOTE: To review the differences between JSON and JSONB check out the related article.

NOTE: more info is available in the PostgreSQL JSON functions documentation page

 

👉 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

Convert an array to a JSON array with the array_to_json function

You can convert an array to a JSON array with the array_to_json (array_to_jsonb for JSONB) function

select array_to_json(
        ARRAY['🍌', '🌶️', '🍍']);

Result

  array_to_json
-----------------
 ["🍌","🌶️","🍍"]
(1 row)

Review all the JSON PostgreSQL use-cases listed in the main page