Get structured data from PostgreSQL

Have you ever puzzled over how to return a complicated structure with a clever hierarchy from a PostgreSQL stored procedure and not to write a huge crutch for parsing a tree structure that has been developed by the developer into a flat relational table? If the answer is yes, then I ask under the cat ...


Good day!
Everyone knows that the result of a query to a relational database is a table. The tabular container, in view of its rigid structure, imposes a number of restrictions on the data presented. For example, the result of a sample that has in its composition a join is a denormalized structure that hides the original data topology, which makes it difficult for an application to handle such a result. With the increase in the number of joines, the situation only worsens
But not everything is as bad as it may seem, because the PostgreSQL developer has at its disposal flexible data structures that can encapsulate a dataset of any complexity. Speech about arrays (arrays) and structures (record, composite type).
The result of the above sample with associations, turns into an elegant, strictly hierarchical structure of the form:

(
table1_column1 int,
table1_column2 varchar,
table1_column3 numeric,
table2_columns t2_columns[]
)

')
where t2_columns is a view structure
(
table2_column1 double precision,
table2_column2 timestamp
)


Thus, by increasing the level of nesting you can transfer arbitrarily complex hierarchical structures.
In my case, the application was written in PL / pgSQL and provided stored procedures for the interface that return the most complexly organized data. Actually this article is about the approach to parsing serialized PostgreSQL datasets.

Directly from parsing the data serialized into a PostgreSQL string (the same string that is obtained by casting the record to varchar), it was decided to refuse immediately, because there is no means for such parsing anywhere except for the postgres kernel.
The idea to change the presentation of the data, which did not take long to wait, in order to use more standardized means of analysis, was developed.
Having spent some time searching for a ready-made solution, and discarding such candidates as the built-in xml and hstore types, I came to the conclusion (perhaps erroneous) that there is no suitable way to transfer data to the application in all respects.

JSON (for compactness and textual reasons) was chosen as the data representation for the future bike, and the native library for PostgreSQL (on pure C) became the implementation method. I will not go into the internal structure of the resulting instrument, especially since it is quite simple and anyone who wants can easily figure it out. Consider the library from a utilitarian point of view. A set of functions is provided as an interface.

Examples of using:

Serialize the structure:

 select to_json( row( 10, 'Some text', 12.5, row( 'text in nested record', array[ 1, 2, 3 ] )::text_and_array, array[ 'array', 'of', 'text' ] ) ) 

Query result:

 {"f1":10,"f2":"Some text","f3":12.5,"f4":{"str":"text in nested record","arr":[1,2,3]},"f5":["array","of","text"]} 

Serialize the datasets:

 select json_agg( q.*, 'json_field_name1' ), json_agg_plain( q.*, 'json_field_name2' ) from ... as q; 

Query result:

 {"json_field_name1":[{ ... },{ ... }, ...]} "json_field_name2":[{ ... },{ ... }, ...] 

Later, the library was expanded with functionality that allows you to perform the reverse operation of parsing JSON and filling in the fields of structures.

We deserialize the structures and arrays:

  select from_json( 'some_record_type', '{"field1":"some text","field2":123,"field3":["this","is","array","of","text"]}' ); select arr_from_json( 'some_type[]', '[{"this is array of"},{"records with one field"}]' ); 

Query results:
("some text",123,"{\"this\",\"is\",\"array\",\"of\",\"text\"}")

{("this is array of"),("records with one field")}


The described serializer can be used in conjunction with any runtime environment containing JSON parser (we have C ++ and PHP). Benchmarks show performance comparable to the serializer built into PostgreSQL.

Thank you for your attention, comments and constructive criticism are welcome.

Link to the library