json_populate_recordset

hljs.initHighlightingOnLoad();

What is JSON_POPULATE_RECORDSET?#

JSON_POPULATE_RECORDSET is a PostgreSQL function that allows you to convert a JSON array to a set of rows with defined columns. This can be useful when you want to insert JSON data into a table or manipulate it in some other way.

How to use JSON_POPULATE_RECORDSET?#

Here is the basic syntax:

JSON_POPULATE_RECORDSET(base anyelement, from_json text) 
RETURNS SETOF anyelement

The first argument is the base type of the resulting rows. The second argument is the JSON array text. You can also use the optional third argument to specify the columns:

JSON_POPULATE_RECORDSET(base anyelement, from_json text, columns text[]) 
RETURNS SETOF anyelement

The columns argument should be an array of column names in the order they appear in the JSON array.

Example:#

Let's say we have a JSON array of people:

[
  {"name": "John", "age": 25},
  {"name": "Jane", "age": 30},
  {"name": "Bob", "age": 40}
]

We can use JSON_POPULATE_RECORDSET to insert this data into a table:

CREATE TABLE people (name varchar(50), age int);
INSERT INTO people SELECT * FROM JSON_POPULATE_RECORDSET(null::people, '[{"name": "John", "age": 25}, {"name": "Jane", "age": 30}, {"name": "Bob", "age": 40}]');

This will create a table called "people" with two columns: "name" and "age". It will then insert the data from the JSON array into the table.

Alternative:#

Another way to achieve the same thing is by using the json_array_elements function:

CREATE TABLE people (name varchar(50), age int);
INSERT INTO people
SELECT (elem->>'name')::varchar(50), (elem->>'age')::int FROM json_array_elements('[{"name": "John", "age": 25}, {"name": "Jane", "age": 30}, {"name": "Bob", "age": 40}]') AS elem;

This will also create a table called "people" with two columns: "name" and "age". It will then insert the data from the JSON array into the table. However, this method requires you to manually extract each column from the JSON object.