Passing array of json to Postgresql Function / Procedure and access it

As a backend developer I prefer to develop database functions or procedures rather than using any ORMs(performance issues).

So sometimes I have requirements to insert more than one row at a time to table and getting array of object in API's request body.Generally people prefers to loop over array and makes repetitive Insert call to database.

In this article, I will show the way to Insert multiple entries into database from json array and without making Database calls for every array objects. I am using Postgresql Database language to solve this scenario

First of all we will create one Postgresql Procedure or Postgresql Function Which takes array of objects as argument and process it.

we are expecting argument of type json in this function.
In the declare section of the postgresql function we have assigned this argument to over function's declared variable and have declared two others variable. "json_object" to hold one json object and "user_name" to hold key of json object.

JSON_ARRAY_ELEMENT :- is a postgresql function to process json, this function expands the top-level of JSON array into a set of JSON values.

So in our case JSON_ARRAY_ELEMENT will return JSON objects and We are looping through this JSON array and storing this json object into "json_object" variable.

While iterating through array we are getting each JSON object in "json_object" variable. after that we are extracting over required or useful key from the json object and inserting it into our database table.

user_name := json_object ->> 'user_name';
this code will extract "user_name" key from our json object.

To Test this function in your postgresql environment.
Create one table.

Then execute above function to your postgresql environment
And call the procedure with below mentioned call.

Hope you find this informative and in case have any doubts or queries, Feel Free to reach on below mentioned Details:
EmailId: [e-mail]