PostgreSQL – JSON_POPULATE_RECORD Function

ByAdmin

The json_populate_record function is used to populate the fields of a given composite type with data from a JSON object. This function is particularly useful when you have a JSON object that matches the structure of a composite type, and you want to extract values from the JSON and populate a composite variable.

Below is the basic syntax of the json_populate_record function:

json_populate_record(base_type anyelement, from_json json) -> anyelement

where base_type is the composite type you want to populate with data.

and from_json is the JSON object containing the data you want to populate the composite type with.

Below is an example of using json_populate_record:

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 object that matches the structure of the person type:

{ "first_name": "John", "last_name": "Doe", "age": 30 }

You can use json_populate_record to populate a variable of type person using this JSON object:

DECLARE 
  json_data json = '{"first_name": "John", "last_name": "Doe", "age": 30}'; 
  person_record person; 
BEGIN 
  person_record := json_populate_record(null::person, json_data); 
  RAISE NOTICE 'First Name: %', person_record.first_name; 
  RAISE NOTICE 'Last Name: %', person_record.last_name; 
  RAISE NOTICE 'Age: %', person_record.age; 
END;

Here, the json_populate_record function takes the JSON object and populates the person_record variable with the corresponding field values.

Similar Posts

PostgreSQL – JSON_BUILD_OBJECT Function

ByAdmin

The json_build_object function is used to create a JSON object from key-value pairs. It allows you to construct a JSON object by specifying keys and their corresponding values. Each key-value pair you provide becomes a property in the resulting JSON object. Below is the basic syntax of using the json_build_object function: json_build_object(key1, value1, key2, value2,…