{"id":28205,"date":"2024-03-16T09:55:42","date_gmt":"2024-03-16T09:55:42","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/"},"modified":"2024-03-22T12:53:27","modified_gmt":"2024-03-22T12:53:27","slug":"how-to-parse-json-format-fields-in-pgsql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/","title":{"rendered":"How to parse JSON format fields in pgsql?"},"content":{"rendered":"<p>In PostgreSQL, you can use the functions json_to_record or json_to_recordset to parse fields in JSON format.<\/p>\n<p>Assuming you have a JSON field named &#8220;data&#8221; that contains the following content:<\/p>\n<pre class=\"post-pre\"><code>{\r\n  \"name\": \"John\",\r\n  \"age\": 30,\r\n  \"address\": {\r\n    \"city\": \"New York\",\r\n    \"state\": \"NY\"\r\n  }\r\n}\r\n<\/code><\/pre>\n<p>You can use the following query to parse this field:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> json_to_record(data) <span class=\"hljs-keyword\">AS<\/span> (name text, age <span class=\"hljs-type\">int<\/span>, address json)\r\n<span class=\"hljs-keyword\">FROM<\/span> your_table;\r\n<\/code><\/pre>\n<p>The above query will return a result set containing parsed fields.<\/p>\n<p>If your JSON field is an array and you want to parse it into multiple rows, you can use the json_to_recordset function. For example, if you have a field named data containing the following content:<\/p>\n<pre class=\"post-pre\"><code>[\r\n  {\r\n    \"name\": \"John\",\r\n    \"age\": 30\r\n  },\r\n  {\r\n    \"name\": \"Jane\",\r\n    \"age\": 25\r\n  }\r\n]\r\n<\/code><\/pre>\n<p>You can use the following query to parse this field:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-operator\">*<\/span>\r\n<span class=\"hljs-keyword\">FROM<\/span> json_to_recordset(data) <span class=\"hljs-keyword\">AS<\/span> (name text, age <span class=\"hljs-type\">int<\/span>);\r\n<\/code><\/pre>\n<p>The above query will return two rows, each containing a field from a parsed JSON object.<\/p>\n<p>Please be aware that you need to enable support for the json or jsonb data types in PostgreSQL, as well as for related JSON functions and operators. You can ensure they are available by running the following command:<\/p>\n<pre class=\"post-pre\"><code>CREATE EXTENSION IF NOT EXISTS \"json\";\r\nCREATE EXTENSION IF NOT EXISTS \"jsonb\";\r\n<\/code><\/pre>\n<p>These commands will create the necessary extensions in your database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In PostgreSQL, you can use the functions json_to_record or json_to_recordset to parse fields in JSON format. Assuming you have a JSON field named &#8220;data&#8221; that contains the following content: { &#8220;name&#8221;: &#8220;John&#8221;, &#8220;age&#8221;: 30, &#8220;address&#8221;: { &#8220;city&#8221;: &#8220;New York&#8221;, &#8220;state&#8221;: &#8220;NY&#8221; } } You can use the following query to parse this field: SELECT json_to_record(data) [&hellip;]<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-28205","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to parse JSON format fields in pgsql? - Blog - Silicon Cloud<\/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:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to parse JSON format fields in pgsql?\" \/>\n<meta property=\"og:description\" content=\"In PostgreSQL, you can use the functions json_to_record or json_to_recordset to parse fields in JSON format. Assuming you have a JSON field named &#8220;data&#8221; that contains the following content: { &quot;name&quot;: &quot;John&quot;, &quot;age&quot;: 30, &quot;address&quot;: { &quot;city&quot;: &quot;New York&quot;, &quot;state&quot;: &quot;NY&quot; } } You can use the following query to parse this field: SELECT json_to_record(data) [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-16T09:55:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-22T12:53:27+00:00\" \/>\n<meta name=\"author\" content=\"Isabella Edwards\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Isabella Edwards\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/\"},\"author\":{\"name\":\"Isabella Edwards\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\"},\"headline\":\"How to parse JSON format fields in pgsql?\",\"datePublished\":\"2024-03-16T09:55:42+00:00\",\"dateModified\":\"2024-03-22T12:53:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/\"},\"wordCount\":173,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/\",\"name\":\"How to parse JSON format fields in pgsql? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-16T09:55:42+00:00\",\"dateModified\":\"2024-03-22T12:53:27+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to parse JSON format fields in pgsql?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\",\"name\":\"Isabella Edwards\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"caption\":\"Isabella Edwards\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to parse JSON format fields in pgsql? - Blog - Silicon Cloud","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:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/","og_locale":"en_US","og_type":"article","og_title":"How to parse JSON format fields in pgsql?","og_description":"In PostgreSQL, you can use the functions json_to_record or json_to_recordset to parse fields in JSON format. Assuming you have a JSON field named &#8220;data&#8221; that contains the following content: { \"name\": \"John\", \"age\": 30, \"address\": { \"city\": \"New York\", \"state\": \"NY\" } } You can use the following query to parse this field: SELECT json_to_record(data) [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-16T09:55:42+00:00","article_modified_time":"2024-03-22T12:53:27+00:00","author":"Isabella Edwards","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Isabella Edwards","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/"},"author":{"name":"Isabella Edwards","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd"},"headline":"How to parse JSON format fields in pgsql?","datePublished":"2024-03-16T09:55:42+00:00","dateModified":"2024-03-22T12:53:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/"},"wordCount":173,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/","name":"How to parse JSON format fields in pgsql? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-16T09:55:42+00:00","dateModified":"2024-03-22T12:53:27+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-parse-json-format-fields-in-pgsql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to parse JSON format fields in pgsql?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd","name":"Isabella Edwards","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","caption":"Isabella Edwards"},"url":"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/28205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=28205"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/28205\/revisions"}],"predecessor-version":[{"id":62487,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/28205\/revisions\/62487"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=28205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=28205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=28205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}