Gajus Kuizinas
Back when I originally published Slonik recommended
However, today I've found a better way: jsonb_to_recordset
jsonb_to_recordset expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause.
Put it simply, jsonb_to_recordset allows to convert arbitrary JSON into a recordset which we can use to query and insert records from, just like with unnest. However, unlike unnest, since we are simply passing a JSON to PostgreSQL, the resulting format is a lot more expressive and powerful.
Let's demonstrate how you would use it to insert data.
Inserting data using json_to_recordset
Let's say you need to insert a list of persons into the database, and each person has an array of attributes associated with them.
You might be tempted to simply iterate through the array and fire a separate INSERT query for each record, e.g.
When dealing with a few records, this is even preferable, because it is easy to read and understand. However, if you are like me and you find yourself debugging an INSERT query that is called 2M+ times per day, then chances are that batching inserts is preferable.
You could also attempt to insert using unnest pattern and it would look something like this:
The problem here is that you are effective responsible for converting arrays into PostgreSQL array string representation and passing them as text parameters, which is ugly. You also need to iterate the array to construct slices that represent values for each column, which is also quite ugly.
However, thanks to jsonb_to_recordset, we can achieve the same result with:
In contrast to the unnest approach, using jsonb_to_recordset we can easily insert complex nested data structures, and we can pass the original JSON document to the query without needing to manipulate it.
In terms of performance they are also exactly the same. As such, my current recommendation is to prefer jsonb_to_recordset whenever inserting lots of rows or nested data structures.
2022