PostgreSQL – JSON_POPULATE_RECORDSET Function
The json_populate_recordset function is used to populate a set of composite records from an array of JSON objects. This is useful when you have an array of JSON objects, and each object corresponds to a record in a composite type.
Below is the basic syntax of the json_populate_recordset function:
json_populate_recordset(base_type anyelement, from_json_array json) -> SETOF anyelementwhere base_type is the composite type you want to populate with data.
and from_json_array is a JSON array containing the data you want to populate the composite type with.
Here’s an example of using the json_populate_recordset:
Assuming you have the following composite type defined:
CREATE TYPE person AS (first_name text, last_name text, age integer);And you have a JSON array containing objects that match the structure of the person type:
[ {"first_name": "John", "last_name": "Doe", "age": 30}, {"first_name": "Jane", "last_name": "Smith", "age": 25} ]You can use json_populate_recordset to populate a set of records of type person using this JSON array:
SELECT * FROM json_populate_recordset(null::person, '[{"first_name": "John", "last_name": "Doe", "age": 30}, {"first_name": "Jane", "last_name": "Smith", "age": 25}]');This query will return a result set with two rows, each representing a populated person record.
