#20 ROW_TO_JSON: Postgresql, How to build JSON using row_to_json function from two related tables.
The source code above creates two temporary tables named "territory_group" and "territories" in PostgreSQL database.
The "territory_group" table has two columns named "group_id" and "group_name", with "group_id" being the primary key.
The "territories" table has three columns named "group_id", "territory_id", and "territory_name". The table's primary key is a composite key formed by "group_id" and "territory_id", and it has a foreign key constraint referencing the "group_id" column of the "territory_group" table.
After creating these tables, the source code inserts some data into them. The first insertion statement inserts a row into the "territory_group" table with values 1 and 'North' for "group_id" and "group_name" columns, respectively.
The second insertion statement inserts two rows into the "territories" table, with values (1, 1, 'North Dakota Territory') and (1, 2, 'South Dakota Territory') for "group_id", "territory_id", and "territory_name" columns, respectively.
Finally, the source code performs a select statement that retrieves data from both tables using a join operation, groups the results by "group_id", and aggregates the "territory_id" and "territory_name" columns of the "territories" table into a JSON array using the "json_agg" and "json_build_object" functions. The "row_to_json" function is then used to convert the resulting rows into JSON format.
@SoftwareNuggets #softwarenuggets ,@short, #postgresqlShorts,
On this page of the site you can watch the video online #20 ROW_TO_JSON: Postgresql, How to build JSON using row_to_json function from two related tables. with a duration of hours minute second in good quality, which was uploaded by the user Software Nuggets 16 April 2023, share the link with friends and acquaintances, this video has already been watched 295 times on youtube and it was liked by 5 viewers. Enjoy your viewing!
![File Explorer Is Slow to Open in Windows 11/10 [FIXED]](https://image.4k-video.cc/id-video/jUDyiY4zR-M)
