{"id":1670,"date":"2014-11-10T11:35:16","date_gmt":"2014-11-10T19:35:16","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=1670"},"modified":"2014-11-15T23:01:52","modified_gmt":"2014-11-16T07:01:52","slug":"jsonb-first-looks","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=1670","title":{"rendered":"JSONb First Looks"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2012\/06\/PostgreSQL_logo.3colors.120x120.png\" alt=\"PostgreSQL_logo.3colors.120x120\" width=\"120\" height=\"120\" class=\"alignright size-full wp-image-575\" \/><br \/>\n<strong>PostgreSQL 9.4 beta 3 on Linux <\/strong><\/p>\n<p><code><\/p>\n<pre>\r\n-- Simple JSON\/JSONb compare, by Oleg\r\n-- json: text storage, as is\r\n-- jsonb: whitespace dissolved, no duplicate keys (last in wins), keys sorted\r\nSELECT \r\n  '{\"c\":0,   \"a\":2, \"a\":1}'::json,\r\n  '{\"c\":0,   \"a\":2, \"a\":1}'::jsonb;\r\n\r\n          json           |      jsonb       \r\n-------------------------+------------------\r\n {\"c\":0,   \"a\":2, \"a\":1} | {\"a\": 1, \"c\": 0}\r\n(1 row)\r\n\r\n\r\n-- emit JSON text from Census corpus\r\n--\r\nSELECT json_agg(row_to_json(p)) from \r\n(  \r\n  select gid,fullname,'feat' as ftype from tiger_data.ca_featnames \r\n  where fullname ~ '^Az' ) as p;\r\n\r\n          json_agg  (formatting added) \r\n-------------------------------------------\r\n[ \r\n  {\"gid\":5048,\"fullname\":\"Aztec Way\",\"ftype\":\"feat\"},\r\n  {\"gid\":9682,\"fullname\":\"Azalea Ct\",\"ftype\":\"feat\"},\r\n    ...\r\n  {\"gid\":4504601,\"fullname\":\"Azure Pl\",\"ftype\":\"feat\"}\r\n]\r\n\r\n##--  return a dict with metadata fields, and an array of dict\r\nselect row_to_json(a.*) from \r\n(select \r\n  'census_acs_2013' as origin,\r\n  'ca' as state,\r\n  'ca_featnames' as table,\r\n  (\r\n    SELECT json_agg(row_to_json(p)) from (  \r\n      select gid,fullname,'feat' as ftype from tiger_data.ca_featnames \r\n      where fullname ~ '^Az' ) as p\r\n  ) as rows\r\n) a;\r\n\r\n          row_to_json  (formatting added) \r\n---------------------------------------------------------\r\n{   \"origin\":\"census_acs_2013\",\r\n    \"state\":\"ca\",\r\n    \"table\":\"ca_featnames\",\r\n    \"rows\": [\r\n      {\"gid\":5048,\"fullname\":\"Aztec Way\",\"ftype\":\"feat\"},\r\n      ...\r\n      {\"gid\":4519032,\"fullname\":\"Azalea Way\",\"ftype\":\"feat\"}\r\n  ]\r\n}\r\n<\/pre>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL 9.4 beta 3 on Linux &#8212; Simple JSON\/JSONb compare, by Oleg &#8212; json: text storage, as is &#8212; jsonb: whitespace dissolved, no duplicate keys (last in wins), keys sorted SELECT &#8216;{&#8220;c&#8221;:0, &#8220;a&#8221;:2, &#8220;a&#8221;:1}&#8217;::json, &#8216;{&#8220;c&#8221;:0, &#8220;a&#8221;:2, &#8220;a&#8221;:1}&#8217;::jsonb; json | jsonb &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; {&#8220;c&#8221;:0, &#8220;a&#8221;:2, &#8220;a&#8221;:1} | {&#8220;a&#8221;: 1, &#8220;c&#8221;: 0} (1 row) &#8212; emit JSON text [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1670"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1670"}],"version-history":[{"count":6,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1670\/revisions"}],"predecessor-version":[{"id":1676,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1670\/revisions\/1676"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1670"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}