{"id":3600,"date":"2023-04-12T15:39:37","date_gmt":"2023-04-12T15:39:37","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/"},"modified":"2026-01-15T18:07:43","modified_gmt":"2026-01-15T18:07:43","slug":"postgresql-and-jsonb","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/","title":{"rendered":"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data?"},"content":{"rendered":"<p><a href=\"https:\/\/en.wikipedia.org\/wiki\/List_of_Forensic_Files_episodes\">Forensic Files<\/a> is a documentary-style, true crime series which reveals how <em>forensic science<\/em> is used to solve violent crimes, mysterious accidents, and even outbreaks of illness. Being a fan and most likely having watched all of the episodes in the series (more than once!) I was inspired to build a database to be used as a source of information about the show.<\/p>\n<p>What I had in mind was to have the ability to assign words and\/or phrases to each episode so that I can perform searches based in terms or phrases &#8211; such as \u201canthropology\u201d, \u201cdumpster\u201d. \u201cravine\u201d or even \u201ccold case\u201d and have my query return a list of all episodes that include content related to those topics.<\/p>\n<p>The following outlines the solution I came up with.<\/p>\n<p><strong>Choosing the Technology<\/strong><\/p>\n<p>For this exercise I decided to use <a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL<\/a>, which is a powerful, open-source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. I\u2019ve used PostgreSQL before, but I wanted to make sure to use the most recent version so the first thing I did was to run the following SQL command:<\/p>\n<p><strong>SELECT<\/strong> <strong>version<\/strong>();<\/p>\n<p>Which yields the following result and yes, as of this writing, 15.2 is the latest version:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2609\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture1-300x65.png\" alt=\"\" width=\"660\" height=\"143\" \/><\/p>\n<p>My other choice was which <em>SQL management tool<\/em> to use, and I opted for <a href=\"https:\/\/dbeaver.com\/\">DBeaver<\/a>\u00a0 (my favorite). I\u2019ve written about both PostgreSQL and DBeaver in the past so I will not spend any time describing them here and just get down to work.<\/p>\n<p><strong>First Step<\/strong><\/p>\n<p>The first step (I already had a database to use) was to <strong>create a new table<\/strong> to hold the Forensic Files episode data:<\/p>\n<ul>\n<li><strong>Season number<\/strong> (which season did this episode first aired)<\/li>\n<li>The <strong>number of the episode<\/strong> <strong>within the entire series<\/strong> (all seasons) \u2013 this will be the episodes unique ID)<\/li>\n<li>The <strong>number of the episode within the season<\/strong><\/li>\n<li>The <strong>title<\/strong> of the episode<\/li>\n<li>The original <strong>air date<\/strong> of the episode<\/li>\n<li>\u201c<strong>Data<\/strong>\u201d \u2013 which I am planning to use to hold words and\/or phrases I want to associate with the episode (more on this in a bit)<\/li>\n<\/ul>\n<p>The following SQL statement was used to create my \u201cepisode\u201d table:<\/p>\n<p>DROP TABLE IF EXISTS public.Forensic_Files_Episodes;<\/p>\n<p>CREATE TABLE public.Forensic_Files_Episodes (<\/p>\n<p>season INT NOT NULL,<\/p>\n<p>number_in_series INT NOT NULL,<\/p>\n<p>number_in_season INT NOT NULL,<\/p>\n<p>title VARCHAR ( 255 ) UNIQUE NOT NULL,<\/p>\n<p>air_date DATE NOT NULL,<\/p>\n<p>popularity\u00a0 numeric,<\/p>\n<p>data jsonb<\/p>\n<p>);<\/p>\n<p>&nbsp;<\/p>\n<p>Once I had defined my table, I wanted to load it with data. In DBeaver, you can easily <a href=\"https:\/\/www.dropbase.io\/post\/5-ways-to-convert-csvs-to-databases-quickly#:~:text=For%20DBeaver%2C%20you%20can%20import,and%20choose%20%22Import%20Data%22.\">import CSV&#8217;s<\/a> \u00a0to a table that matches the columns that the comma-separated values or \u201cCSV\u201d file contains. To do this, you right click the table, and choose &#8220;<strong>Import Data<\/strong>&#8220;:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2610\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture2-300x242.png\" alt=\"\" width=\"591\" height=\"477\" \/><\/p>\n<p>My file of <em>Forensic Files Episode data<\/em> contains all of the fields I am interested in (season, the number of episode within the series (all seasons), the number of the episode within the season, the episode title and the original air date) so I can easily import the data :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2611\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture3-300x165.png\" alt=\"\" width=\"707\" height=\"389\" \/><\/p>\n<p>After the <strong>data import<\/strong> completes, I wanted to check the table\u2019s content, so I used this SQL Statement:<\/p>\n<p>select * from public.forensic_files_episodes;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2612\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture4-300x102.png\" alt=\"\" width=\"818\" height=\"278\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Adding Popularity Ratings<\/strong><\/p>\n<p>I had a <em>second<\/em> data file which contained the viewer popularity rating for each episode and wanted to add that information to my table so I again used the DBeaver <strong>Import Data<\/strong> feature and this time, indicated CREATE for the <em>target table<\/em> so I ended up with a new table named \u201cforensic_files_popularity\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2613\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture5-300x132.png\" alt=\"\" width=\"825\" height=\"363\" \/><\/p>\n<p>After <em>this<\/em> import (completes), I checked the <em>new<\/em> <em>table<\/em> with the following SL statement:<\/p>\n<p>select * from forensic_files_popularity;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2602\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture6-300x95.png\" alt=\"\" width=\"673\" height=\"213\" \/><\/p>\n<p>Now that I had loaded the episode popularity ratings, I updated the forensic_files_popularity.popularity column from the forensic_files_popularity table (matching on the \u201cnumber_in_series\u201d column) with the following SQL Statement:<\/p>\n<p>&nbsp;<\/p>\n<p>UPDATE forensic_files_episodes<\/p>\n<p>SET popularity\u00a0 = forensic_files_popularity.popularity<\/p>\n<p>FROM forensic_files_popularity<\/p>\n<p>WHERE forensic_files_episodes.number_in_series\u00a0 = forensic_files_popularity.number_in_series;<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2603\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture7-300x101.png\" alt=\"\" width=\"677\" height=\"228\" \/><\/p>\n<p>At that point I can query the list episodes by a variety of \u201cfilters\u201d such as <em>air date<\/em>, <em>season<\/em>, <em>number in series<\/em>, the <em>title<\/em> or even <strong><em>popularity<\/em><\/strong>:<\/p>\n<p>&nbsp;<\/p>\n<p>select number_in_series, title, popularity from public.Forensic_Files_Episodes<\/p>\n<p>where public.Forensic_Files_Episodes.popularity &gt; 5.0<\/p>\n<p>order by popularity DESC;<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2604\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture8-300x81.png\" alt=\"\" width=\"678\" height=\"183\" \/><\/p>\n<p><strong>Adding Tags<\/strong><\/p>\n<p><strong>JSON<\/strong> is an <em>open standard file format<\/em> that uses human-readable text to store and transmit data objects consisting of attribute\u2013value pairs and \u201carrays\u201d. PostgreSQL introduced the <strong>JSONB<\/strong> data type in version 9.4 (a while back!) and warrants a closer look from the reader.<\/p>\n<p>&nbsp;<\/p>\n<p>I initially defined a \u201cdata\u201d column of this type as part of my forensic_files_episodes table:<\/p>\n<p><strong> <img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2605\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture9-300x125.png\" alt=\"\" width=\"581\" height=\"242\" \/><\/strong><\/p>\n<p>So, this now allowed me to <em>define and associate key phrases<\/em> \u201ctagging\u201d each episode. Below are some sample SQL statements I used to assign an \u201calternate name\u201d as well as associate multiple key phrases (or tags) for each episode:<\/p>\n<p>&nbsp;<\/p>\n<p>UPDATE forensic_files_episodes set data =<\/p>\n<p>(&#8216;{&#8220;name&#8221;: &#8220;mothers diary&#8221;, &#8220;tags&#8221;: [&#8220;Haunted&#8221;, &#8220;disappearance of her mother&#8221;, &#8220;twenty years earlier&#8221;, &#8220;young woman&#8221;, &#8220;undertook an investigation&#8221;, &#8220;cold case&#8221;], &#8220;finished&#8221;: true}&#8217;)<\/p>\n<p>where forensic_files_episodes.number_in_series = 198;<\/p>\n<p>&nbsp;<\/p>\n<p>UPDATE forensic_files_episodes set data =<\/p>\n<p>(&#8216;{&#8220;name&#8221;: &#8220;Killigraphy&#8221;, &#8220;tags&#8221;: [&#8220;autopsy&#8221;,&#8221;suspicious&#8221;,&#8221;death&#8221;,&#8221;Georgia&#8221;,&#8221;mentally-unstable&#8221;,&#8221;husband&#8221;,&#8221;murder&#8221;,&#8221;medical examiner&#8221;], &#8220;finished&#8221;: true}&#8217;)<\/p>\n<p>where forensic_files_episodes.number_in_series = 61;<\/p>\n<p>&nbsp;<\/p>\n<p>UPDATE forensic_files_episodes set data =<\/p>\n<p>(&#8216;{&#8220;name&#8221;: &#8220;A Leg to Stand On&#8221;, &#8220;tags&#8221;: [&#8220;Severed leg&#8221;,&#8221;dumpster&#8221;,&#8221;investigators&#8221;,&#8221;identifying the victim&#8221;,&#8221;anthropology&#8221;,&#8221;toxicology&#8221;,&#8221;DNA&#8221;,&#8221;police&#8221;,&#8221;victim&#8221;,&#8221;follow the trail&#8221;,&#8221;evidence&#8221;], &#8220;finished&#8221;: true}&#8217;)<\/p>\n<p>where forensic_files_episodes.number_in_series = 115;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2606\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture10-300x92.png\" alt=\"\" width=\"812\" height=\"249\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2607\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture11-300x97.png\" alt=\"\" width=\"813\" height=\"263\" \/><\/p>\n<p><strong>Searching by Key Phrase<\/strong><\/p>\n<p>After adding the \u201ctags\u201d I was ready to do some searching! I can search by any phrase, such as \u201cblood splatter\u201d or \u201cDNA\u201d or how about a partial phrase like \u201cinvest\u201d:<\/p>\n<p>&nbsp;<\/p>\n<p>select t.title<\/p>\n<p>from forensic_files_episodes t<\/p>\n<p>where exists (select *<\/p>\n<p>from jsonb_array_elements_text(t.data -&gt; &#8216;tags&#8217;) as t(tagme)<\/p>\n<p>where t.tagme like &#8216;%invest%&#8217;);<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2608\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture12-300x36.png\" alt=\"\" width=\"917\" height=\"110\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>This exercise really didn\u2019t take very long to complete and demonstrates a simple solution for using JSONb data types with PostgreSQL to allow searching by tags &#8211; and now I am ready for next time I want to binge watch ONLY those Forensic Files episodes that deal with <em>square dancing doctors<\/em> from <em>New York<\/em>!<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Forensic Files is a documentary-style, true crime series which reveals how forensic science is used to solve violent crimes, mysterious accidents, and even outbreaks of illness. Being a fan and most likely having watched all of the episodes in the series (more than once!) I was inspired to build a database to be used as&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[27],"tags":[32],"class_list":["post-3600","post","type-post","status-publish","format-standard","hentry","category-quebit-value","tag-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data? - QueBIT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data? - QueBIT\" \/>\n<meta property=\"og:description\" content=\"Forensic Files is a documentary-style, true crime series which reveals how forensic science is used to solve violent crimes, mysterious accidents, and even outbreaks of illness. Being a fan and most likely having watched all of the episodes in the series (more than once!) I was inspired to build a database to be used as&hellip;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/\" \/>\n<meta property=\"og:site_name\" content=\"QueBIT\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-12T15:39:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-01-15T18:07:43+00:00\" \/>\n<meta name=\"author\" content=\"agoddard\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"agoddard\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/\"},\"author\":{\"name\":\"agoddard\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"headline\":\"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data?\",\"datePublished\":\"2023-04-12T15:39:37+00:00\",\"dateModified\":\"2026-01-15T18:07:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/\"},\"wordCount\":1069,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2023\\\/03\\\/Picture1-300x65.png\",\"keywords\":[\"SQL\"],\"articleSection\":[\"QueBIT Value\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/\",\"name\":\"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data? - QueBIT\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2023\\\/03\\\/Picture1-300x65.png\",\"datePublished\":\"2023-04-12T15:39:37+00:00\",\"dateModified\":\"2026-01-15T18:07:43+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#primaryimage\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2023\\\/03\\\/Picture1-300x65.png\",\"contentUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2023\\\/03\\\/Picture1-300x65.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/postgresql-and-jsonb\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\",\"name\":\"QueBIT\",\"description\":\"QueBIT\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\",\"name\":\"agoddard\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"caption\":\"agoddard\"},\"sameAs\":[\"https:\\\/\\\/quebit.com\\\/askquebit\"],\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/author\\\/agoddard\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data? - QueBIT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/","og_locale":"en_US","og_type":"article","og_title":"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data? - QueBIT","og_description":"Forensic Files is a documentary-style, true crime series which reveals how forensic science is used to solve violent crimes, mysterious accidents, and even outbreaks of illness. Being a fan and most likely having watched all of the episodes in the series (more than once!) I was inspired to build a database to be used as&hellip;","og_url":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/","og_site_name":"QueBIT","article_published_time":"2023-04-12T15:39:37+00:00","article_modified_time":"2026-01-15T18:07:43+00:00","author":"agoddard","twitter_card":"summary_large_image","twitter_misc":{"Written by":"agoddard","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#article","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/"},"author":{"name":"agoddard","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"headline":"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data?","datePublished":"2023-04-12T15:39:37+00:00","dateModified":"2026-01-15T18:07:43+00:00","mainEntityOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/"},"wordCount":1069,"commentCount":0,"image":{"@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture1-300x65.png","keywords":["SQL"],"articleSection":["QueBIT Value"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/","url":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/","name":"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data? - QueBIT","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/#website"},"primaryImageOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#primaryimage"},"image":{"@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture1-300x65.png","datePublished":"2023-04-12T15:39:37+00:00","dateModified":"2026-01-15T18:07:43+00:00","author":{"@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"breadcrumb":{"@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#primaryimage","url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture1-300x65.png","contentUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2023\/03\/Picture1-300x65.png"},{"@type":"BreadcrumbList","@id":"https:\/\/quebit.com\/askquebit\/postgresql-and-jsonb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/quebit.com\/askquebit\/"},{"@type":"ListItem","position":2,"name":"How Do I Use PostgreSQL\u2019s JSONB Data Type to Store and Search JSON Data?"}]},{"@type":"WebSite","@id":"https:\/\/quebit.com\/askquebit\/#website","url":"https:\/\/quebit.com\/askquebit\/","name":"QueBIT","description":"QueBIT","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/quebit.com\/askquebit\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084","name":"agoddard","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","caption":"agoddard"},"sameAs":["https:\/\/quebit.com\/askquebit"],"url":"https:\/\/quebit.com\/askquebit\/author\/agoddard\/"}]}},"_links":{"self":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3600","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/comments?post=3600"}],"version-history":[{"count":1,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3600\/revisions"}],"predecessor-version":[{"id":4909,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3600\/revisions\/4909"}],"wp:attachment":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/media?parent=3600"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/categories?post=3600"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/tags?post=3600"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}