How to create a JSON object from keys and values arrays in PostgreSQL®?

- One minute read - 172 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function json_object (jsonb_object for JSONB) to create a JSON object from two arrays of keys and values.

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 AI Database Optimizer!
Powered by EverSQL, it provides you index and SQL rewrite recommendations to take your database performance to the next level

Create a JSON object from two arrays of keys and values with the json_object function

You can create a JSON object from two arrays (one of keys and one of values) with the json_object (jsonb_object for JSONB) function

select 
    jsonb_object(
        ARRAY['name', 'pizza'], 
        ARRAY['Francesco', 'Margherita']);

Result

                 jsonb_object
----------------------------------------------
 {"name": "Francesco", "pizza": "Margherita"}
(1 row)

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