{"id":3863,"date":"2023-04-05T07:01:27","date_gmt":"2023-04-05T07:01:27","guid":{"rendered":"https:\/\/test.opensource-db.in\/wp1\/?p=3863"},"modified":"2023-04-05T07:31:30","modified_gmt":"2023-04-05T07:31:30","slug":"nested-composite-data-structures-in-postgres","status":"publish","type":"post","link":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/","title":{"rendered":"Nested Composite Data Structures in Postgres"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"538\" src=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png\" alt=\"\" class=\"wp-image-3874\" srcset=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png 1024w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-300x158.png 300w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-768x403.png 768w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1536x806.png 1536w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-2048x1075.png 2048w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-189x99.png 189w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-152x80.png 152w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-394x207.png 394w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-915x480.png 915w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1240x651.png 1240w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1920x1008.png 1920w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Data nesting is a way of organizing data in a hierarchical or nested structure, where one piece of data is related to another in a parent-child or one-to-many relationship. This approach can be particularly useful for storing complex data structures in databases and is commonly used in NoSQL databases such as MongoDB.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In PostgreSQL, nested tables refer to tables that are organized in a hierarchical or nested structure. This means that one table can have one or more sub-tables or child tables that are associated with it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Nested tables are often used to represent complex data structures such as arrays, JSON, and XML data. For example, you could have a table that stores information about customers and a child table that stores information about their orders. This allows you to retrieve and manage related data in a more organized and efficient way.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Overall, nested tables in PostgreSQL provide a flexible and powerful way to store and manage complex data structures. Composite data types can be a useful approach when designing a data model in PostgreSQL. Here are factors to consider before implementing them:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Data complexity:<\/strong> If the data is relatively simple, there may be no need to use composite data types. However, if the data contains multiple related attributes or sub-objects, then composite data types can simplify data management.<\/li><li><strong>Data redundancy:<\/strong> If data needs to be repeated frequently in a table, composite data types can reduce redundancy and simplify data entry.<\/li><li><strong>Data normalization:<\/strong> If the data is normalized, composite data types can help maintain data integrity by grouping related data together.<\/li><li><strong>Data access patterns:<\/strong> Consider how the data will be accessed and queried. If a single attribute is commonly queried independently of the others, then it may be more efficient to store it separately rather than in a composite type.<\/li><li><strong>Database performance:<\/strong> Composite data types can impact database performance, particularly when they are used in large tables or frequently updated. Therefore, it&#8217;s important to test and analyze the performance impact of composite data types on the database.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">By considering these factors, you can determine whether composite data types are appropriate for your data model and how to use them effectively.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Fast forward to the JSONs and XML Types<\/h2>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Nested Tables with JSON Data<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Another common use case for nested tables in PostgreSQL is with JSON data. Here&#8217;s an example of how you can create a table with nested JSON data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE products (\n   id serial PRIMARY KEY,\n   name varchar(50),\n   details json\n);\n\nINSERT INTO products (name, details) VALUES (\n   'Product 1',\n   '{\"price\": 10.99, \"colors\": &#91;\"red\", \"blue\", \"green\"]}'\n);\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, the &#8220;details&#8221; column is a JSON data type that contains nested data. You can query the nested data using the JSON functions in PostgreSQL, like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name, details-&gt;'price' AS price, details-&gt;'colors' AS colors FROM products;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"419\" height=\"180\" src=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-25-52.png\" alt=\"\" class=\"wp-image-3875\" srcset=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-25-52.png 419w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-25-52-300x129.png 300w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-25-52-230x99.png 230w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-25-52-186x80.png 186w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-25-52-394x169.png 394w\" sizes=\"(max-width: 419px) 100vw, 419px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">As you see, it returns the name of the product, the price, and the colors from the nested JSON data.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Nested Tables with XML Data<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">You can also use nested tables in PostgreSQL with XML data. Here&#8217;s an example of how you can create a table with nested XML data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE books (\n   id serial PRIMARY KEY,\n   title varchar(50),\n   content xml\n);\n\nINSERT INTO books (title, content) VALUES (\n   'Book 1',\n   '&lt;book&gt;\n      &lt;chapter&gt;\n         &lt;title&gt;Chapter 1&lt;\/title&gt;\n         &lt;paragraph&gt;Paragraph 1&lt;\/paragraph&gt;\n         &lt;paragraph&gt;Paragraph 2&lt;\/paragraph&gt;\n      &lt;\/chapter&gt;\n      &lt;chapter&gt;\n         &lt;title&gt;Chapter 2&lt;\/title&gt;\n         &lt;paragraph&gt;Paragraph 1&lt;\/paragraph&gt;\n         &lt;paragraph&gt;Paragraph 2&lt;\/paragraph&gt;\n      &lt;\/chapter&gt;\n   &lt;\/book&gt;'\n);\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, the &#8220;content&#8221; column is an XML data type that contains nested data. You can query the nested data using the XML functions in PostgreSQL, like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT title, (xpath('\/\/chapter\/title\/text()', content))&#91;1] AS chapter_title FROM books;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"685\" height=\"164\" src=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-27-19.png\" alt=\"\" class=\"wp-image-3876\" srcset=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-27-19.png 685w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-27-19-300x72.png 300w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-27-19-414x99.png 414w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-27-19-334x80.png 334w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-27-19-394x94.png 394w\" sizes=\"(max-width: 685px) 100vw, 685px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">It returned the title of the book and the title of each chapter in the nested XML data.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">DMLs with Nested tables having Composite Data types<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Have you ever encountered the challenge of managing a table within a table that contains composite data types in Postgres? If so, you&#8217;re not alone. Fortunately, the following part of the post will guide you through the process of handling nested tables using composite types.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To illustrate, let&#8217;s consider the scenario of a school student and their report card. The goal is to create a table with one row for each student, where each row contains a nested table with two columns: the subject name and the corresponding grade earned by the student. Consider the case of a student and their academic performance record. Our objective is to establish a table that holds a distinct row for every student, and each of these rows comprises a nested table with two columns representing the subject name and the student&#8217;s corresponding grade.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TYPE subjects_enum AS enum ('ENGLISH', 'MATHS', 'SCIENCE', 'SOCIAL',\n'MORALSCIENCE' );\n\nCREATE TYPE grades_enum AS enum ('DISTINCTION', 'FIRST_DIVISION',\n'SECOND_DIVISION', 'THIRD_DIVISION' );\n\nCREATE TYPE subject_grades_tbl AS (subjects subjects_enum, grades grades_enum );\n\nCREATE TABLE student_grades\n  (\n     id               SERIAL NOT NULL PRIMARY KEY,\n     name             TEXT NOT NULL,\n     subject_grades_t SUBJECT_GRADES_TBL&#91;]\n  ); \n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>#Inserting sample data:<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO student_grades\n            (NAME,\n             subject_grades_t)\nVALUES     ('Iron Man',\n'{\"(ENGLISH,FIRST_DIVISION)\",\"(MATHS,FIRST_DIVISION)\",\"(SCIENCE,SECOND_DIVISION)\",\"(SOCIAL,THIRD_DIVISION)\",\"(MORALSCIENCE,DISTINCTION)\"}'\n);\n\nINSERT INTO student_grades\n            (NAME,\n             subject_grades_t)\nVALUES     ('Dr Strange',\n'{\"(ENGLISH,SECOND_DIVISION)\",\"(MATHS,THIRD_DIVISION)\",\"(SCIENCE,FIRST_DIVISION)\",\"(SOCIAL,THIRD_DIVISION)\",\"(MORALSCIENCE,DISTINCTION)\"}'\n);\n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><strong># Updating Data in various ways<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE student_grades\nSET    subject_grades_t = array&#91;('ENGLISH','FIRST_DIVISION')::subject_grades_tbl]\nWHERE  id = 1;\n\nUPDATE student_grades\nSET    subject_grades_t = array&#91;('ENGLISH','FIRST_DIVISION')::subject_grades_tbl , ('MATHS','SECOND_DIVISION')::subject_grades_tbl]\nWHERE  id = 1;\n\nUPDATE student_grades\nSET    subject_grades_t = '{\"(ENGLISH,THIRD_DIVISION)\",\"(MATHS,SECOND_DIVISION)\",\"(SCIENCE,FIRST_DIVISION)\",\"(SOCIAL,THIRD_DIVISION)\",\"(MORALSCIENCE,DISTINCTION)\"}'\nWHERE  id = 1;\n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><strong># Updating Data programmatically using a simple piece of the code block<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>DO $$ \n&lt;&lt;first_block&gt;&gt;\nDECLARE l_subject_grades_t TEXT := '{\"(ENGLISH,THIRD_DIVISION)\",\"(MATHS,SECOND_DIVISION)\"}';BEGIN\n  UPDATE student_grades\n  SET    subject_grades_t = l_subject_grades_t::subject_grades_tbl&#91;]\n  WHERE  id = 1;\nEND first_block $$;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Fetching &amp; Filtering Data<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT   * FROM     (\n                SELECT id,\n                       NAME ,\n                       (Unnest(subject_grades_t)::subject_grades_tbl).subjects ,\n                       (unnest(subject_grades_t)::subject_grades_tbl).grades\n                FROM   student_grades) z\nORDER BY id, subjects;\n\nSELECT   * FROM     (\n                SELECT id,\n                       NAME ,\n                       (Unnest(subject_grades_t)::subject_grades_tbl).subjects ,\n                       (unnest(subject_grades_t)::subject_grades_tbl).grades\n                FROM   student_grades) z\nWHERE    subjects = 'ENGLISH'\nORDER BY id, subjects;\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"793\" height=\"599\" src=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-30-22.png\" alt=\"\" class=\"wp-image-3877\" srcset=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-30-22.png 793w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-30-22-300x227.png 300w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-30-22-768x580.png 768w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-30-22-131x99.png 131w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-30-22-106x80.png 106w, https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/Screenshot-from-2023-04-05-12-30-22-394x298.png 394w\" sizes=\"(max-width: 793px) 100vw, 793px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Overall, data nesting in Postgres using the JSONB data type, XML, and Composite data types can be a powerful way of storing and querying complex data structures, and can be particularly useful in situations where data needs to be stored and queried efficiently, or where there are complex relationships between different pieces of data exist.<\/p>\n\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\"><\/div>\n\n\n\n<div class=\"wp-block-group alignfull has-background is-layout-flow wp-block-group-is-layout-flow\" id=\"contactus\" style=\"background-color:#ffffff\">\n<div style=\"height:64px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-text-align-center has-medium-font-size wp-block-paragraph\" style=\"line-height:.9\">Get in touch with us!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/test.opensource-db.in\/wp1\/\" target=\"_blank\" rel=\"noreferrer noopener\">OpenSource DB<\/a> offers comprehensive services that cover Anything &amp; Everything with PostgreSQL \u2013 From Database Development, Database Migration, HA Setup,\u00a0Major upgrades, and many more. Our Scope is not limited to the services listed; it can be extended depending on your specific setup and requirements.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let us help build your application on the world\u2019s leading object-relational database system and create the best solution for your business. contact us today.<\/p>\n\n\n\n<div id=\"https:\/\/test.opensource-db.in\/wp1\/contactus\/\" class=\"wp-block-buttons is-horizontal is-content-justification-center is-layout-flex wp-container-core-buttons-is-layout-cbcdc57d wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button has-custom-width wp-block-button__width-25 is-style-outline is-style-outline--1\"><a class=\"wp-block-button__link has-vivid-cyan-blue-background-color has-text-color has-background\" href=\"https:\/\/test.opensource-db.in\/wp1\/contactus\/\" style=\"border-radius:50px;color:#ffffff\" target=\"_blank\" rel=\"noreferrer noopener\">Contact us<\/a><\/div>\n<\/div>\n\n\n\n<div style=\"height:64px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Data nesting is a way of organizing data in a hierarchical or nested structure, where one piece of data is [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[23,42],"tags":[60,57,58,59],"class_list":["post-3863","post","type-post","status-publish","format-standard","hentry","category-postgresql-14","category-postgresql-15","tag-array","tag-composite-type","tag-nested-table","tag-table-with-array"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Nested Composite Data Structures in Postgres - OpenSource DB<\/title>\n<meta name=\"robots\" content=\"noindex, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Nested Composite Data Structures in Postgres - OpenSource DB\" \/>\n<meta property=\"og:description\" content=\"Data nesting is a way of organizing data in a hierarchical or nested structure, where one piece of data is [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/\" \/>\n<meta property=\"og:site_name\" content=\"OpenSource DB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-05T07:01:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-05T07:31:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png\" \/>\n<meta name=\"author\" content=\"Hari Kiran\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@opensource_db\" \/>\n<meta name=\"twitter:site\" content=\"@opensource_db\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Hari Kiran\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/\"},\"author\":{\"name\":\"Hari Kiran\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/2d16bf6a94c0502f1c5b69e4a36e9a09\"},\"headline\":\"Nested Composite Data Structures in Postgres\",\"datePublished\":\"2023-04-05T07:01:27+00:00\",\"dateModified\":\"2023-04-05T07:31:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/\"},\"wordCount\":840,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#organization\"},\"image\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png\",\"keywords\":[\"Array\",\"Composite Type\",\"Nested Table\",\"Table with Array\"],\"articleSection\":[\"PostgreSQL 14\",\"PostgreSQL 15\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/\",\"name\":\"Nested Composite Data Structures in Postgres - OpenSource DB\",\"isPartOf\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png\",\"datePublished\":\"2023-04-05T07:01:27+00:00\",\"dateModified\":\"2023-04-05T07:31:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1.png\",\"contentUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1.png\",\"width\":2400,\"height\":1260},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/test.opensource-db.in\/wp1\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Nested Composite Data Structures in Postgres\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#website\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/\",\"name\":\"OpenSource DB\",\"description\":\"Your Trusted OpenSource Databases partner\",\"publisher\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/test.opensource-db.in\/wp1\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#organization\",\"name\":\"OPENSOURCE DB PRIVATE LIMITED\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png\",\"contentUrl\":\"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png\",\"width\":368,\"height\":120,\"caption\":\"OPENSOURCE DB PRIVATE LIMITED\"},\"image\":{\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/\",\"https:\/\/x.com\/opensource_db\",\"https:\/\/www.youtube.com\/channel\/UCmTI5h\",\"https:\/\/www.linkedin.com\/company\/opensource-db\",\"https:\/\/www.instagram.com\/opensource_db\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/2d16bf6a94c0502f1c5b69e4a36e9a09\",\"name\":\"Hari Kiran\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c9afef742ba871448bf401518655463f5e87b02f9fbd0096e88f857c51896822?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c9afef742ba871448bf401518655463f5e87b02f9fbd0096e88f857c51896822?s=96&d=mm&r=g\",\"caption\":\"Hari Kiran\"},\"description\":\"Hari Kiran has over 15 years of extensive experience in areas of operations and managing multiple database technologies. A proven leader with excellent skills in building relationships, positive team dynamics, and decision-making. Hari also has strong Open Source expertise in Professional Services. He has the experience to help you refine your strategy and drive execution.\",\"url\":\"https:\/\/test.opensource-db.in\/wp1\/author\/harikiranopensource-db-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Nested Composite Data Structures in Postgres - OpenSource DB","robots":{"index":"noindex","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"og_locale":"en_US","og_type":"article","og_title":"Nested Composite Data Structures in Postgres - OpenSource DB","og_description":"Data nesting is a way of organizing data in a hierarchical or nested structure, where one piece of data is [&hellip;]","og_url":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/","og_site_name":"OpenSource DB","article_publisher":"https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/","article_published_time":"2023-04-05T07:01:27+00:00","article_modified_time":"2023-04-05T07:31:30+00:00","og_image":[{"url":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png","type":"","width":"","height":""}],"author":"Hari Kiran","twitter_card":"summary_large_image","twitter_creator":"@opensource_db","twitter_site":"@opensource_db","twitter_misc":{"Written by":"Hari Kiran","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#article","isPartOf":{"@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/"},"author":{"name":"Hari Kiran","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/2d16bf6a94c0502f1c5b69e4a36e9a09"},"headline":"Nested Composite Data Structures in Postgres","datePublished":"2023-04-05T07:01:27+00:00","dateModified":"2023-04-05T07:31:30+00:00","mainEntityOfPage":{"@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/"},"wordCount":840,"commentCount":0,"publisher":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#organization"},"image":{"@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage"},"thumbnailUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png","keywords":["Array","Composite Type","Nested Table","Table with Array"],"articleSection":["PostgreSQL 14","PostgreSQL 15"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/","url":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/","name":"Nested Composite Data Structures in Postgres - OpenSource DB","isPartOf":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#website"},"primaryImageOfPage":{"@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage"},"image":{"@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage"},"thumbnailUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1-1024x538.png","datePublished":"2023-04-05T07:01:27+00:00","dateModified":"2023-04-05T07:31:30+00:00","breadcrumb":{"@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#primaryimage","url":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1.png","contentUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2023\/04\/blog-image1.png","width":2400,"height":1260},{"@type":"BreadcrumbList","@id":"https:\/\/test.opensource-db.in\/wp1\/nested-composite-data-structures-in-postgres\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/test.opensource-db.in\/wp1\/"},{"@type":"ListItem","position":2,"name":"Nested Composite Data Structures in Postgres"}]},{"@type":"WebSite","@id":"https:\/\/test.opensource-db.in\/wp1\/#website","url":"https:\/\/test.opensource-db.in\/wp1\/","name":"OpenSource DB","description":"Your Trusted OpenSource Databases partner","publisher":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/test.opensource-db.in\/wp1\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/test.opensource-db.in\/wp1\/#organization","name":"OPENSOURCE DB PRIVATE LIMITED","url":"https:\/\/test.opensource-db.in\/wp1\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/","url":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png","contentUrl":"https:\/\/test.opensource-db.in\/wp1\/wp-content\/uploads\/2021\/10\/osdb-logo-tm-2.png","width":368,"height":120,"caption":"OPENSOURCE DB PRIVATE LIMITED"},"image":{"@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/people\/OpenSource-DB\/100072970755470\/","https:\/\/x.com\/opensource_db","https:\/\/www.youtube.com\/channel\/UCmTI5h","https:\/\/www.linkedin.com\/company\/opensource-db","https:\/\/www.instagram.com\/opensource_db\/"]},{"@type":"Person","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/2d16bf6a94c0502f1c5b69e4a36e9a09","name":"Hari Kiran","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.opensource-db.in\/wp1\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c9afef742ba871448bf401518655463f5e87b02f9fbd0096e88f857c51896822?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c9afef742ba871448bf401518655463f5e87b02f9fbd0096e88f857c51896822?s=96&d=mm&r=g","caption":"Hari Kiran"},"description":"Hari Kiran has over 15 years of extensive experience in areas of operations and managing multiple database technologies. A proven leader with excellent skills in building relationships, positive team dynamics, and decision-making. Hari also has strong Open Source expertise in Professional Services. He has the experience to help you refine your strategy and drive execution.","url":"https:\/\/test.opensource-db.in\/wp1\/author\/harikiranopensource-db-com\/"}]}},"rttpg_featured_image_url":null,"rttpg_author":{"display_name":"Hari Kiran","author_link":"https:\/\/test.opensource-db.in\/wp1\/author\/harikiranopensource-db-com\/"},"rttpg_comment":0,"rttpg_category":"<a href=\"https:\/\/test.opensource-db.in\/wp1\/category\/postgres\/postgresql-14\/\" rel=\"category tag\">PostgreSQL 14<\/a> <a href=\"https:\/\/test.opensource-db.in\/wp1\/category\/postgres\/postgresql-15\/\" rel=\"category tag\">PostgreSQL 15<\/a>","rttpg_excerpt":"Data nesting is a way of organizing data in a hierarchical or nested structure, where one piece of data is [&hellip;]","_links":{"self":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts\/3863","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/comments?post=3863"}],"version-history":[{"count":17,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts\/3863\/revisions"}],"predecessor-version":[{"id":3885,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/posts\/3863\/revisions\/3885"}],"wp:attachment":[{"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/media?parent=3863"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/categories?post=3863"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/test.opensource-db.in\/wp1\/wp-json\/wp\/v2\/tags?post=3863"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}