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_POPULATE_RECORDSET Function

ByAdmin

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…

PostgreSQL – JSON_EXTRACT_PATH_TEXT Function

ByAdmin

The json_extract_path_text function is used to extract a specific value from a JSON object or array by specifying a path to the desired element. This function returns the value as text. The basic syntax of the json_extract_path_text function is as shown below: json_extract_path_text(json_object_or_array, path_element1, path_element2, …, path_elementN) Here’s an example of using the json_extract_path_text function:…