§ Native JSON в PostgreSQL 9.2

На днях я узнал что вышла новая версия PostgreSQL за номером 9.2 в которой появилась очень необычная но в тоже время логичная и понятная функция - вывод результата в формате JSON. Мне кажется что описывать зачем это нужно - неблагодарное занятие, применений этому мульен, особенно ликуют web-разработчики. Моя же статья расскажет о том как этим пользоваться используя в качестве языка мой любимый ruby.

Итак под рукой у меня оказалась виртуалка с подходящей версией PostgreSQL. Добавляем в ruby поддержку PostgreSQL и JSON

# gem install pg json

После чего запускаем irb (для тех кто не в курсе - это интерактивная консоль ruby) и понеслась.

require 'pg'
require 'json'

conn = PGconn.open(:host => "192.168.0.1", :user => "postgres", :dbname => "test")

Теперь когда мы получили доступ к БД (у меня в базу test выложена база моего блога, в качестве примера), можем попробовать новый функционал на вкус.

Сперва классический SELECT

conn.exec("SELECT id, published, comments FROM posts;")[0]
=> {"id"=>"1", "published"=>"t", "comments"=>"t"}

А теперь немного магии JSON

conn.exec("SELECT row_to_json(row(id, published, comments)) FROM posts;")[0]
=> {"row_to_json"=>"{\"f1\":1,\"f2\":true,\"f3\":true}"}

Ну или так

JSON.parse(conn.exec("SELECT row_to_json(row(id, published, comments)) FROM posts;")[0]['row_to_json'])
=> {"f1"=>1, "f2"=>true, "f3"=>true}

Как видите, если указывать строки - то вместо имен мы получаем f1, f2... fn. Но если выводить все колонки, то вывод получается адекватным

JSON.parse(conn.exec("SELECT row_to_json(articles) FROM articles;")[0]['row_to_json'])
=> {"id"=>1, "title"=>"Обо мне", "body"=>"Some text here", "comments"=>false, "meta_keywords"=>"keywords", "meta_description"=>"description", "tags"=>"", "created_at"=>"2013-05-07 16:13:03", "updated_at"=>"2013-05-07 16:17:29"}

Казалось бы ичо? Обычный pg выводит тоже самое - но на самом деле не совсем тоже самое. Посмотрите внимательно на значения. Если pg все значения выдает как String, то JSON отдает значения в том формате, в котором они хранятся в БД. Булиан - в виде True/False, целочисленное значение в виде Integer. На мой взгляд немаловажный момент.

Но это ещё не все, в качестве вывода PGSQL можно получить не просто набор JSON, а сразу массив,

JSON.parse(conn.exec("SELECT array_to_json(array_agg(row_to_json(articles))) FROM articles;" )[0]['array_to_json'])

Наверное для использования с ruby pg эти примеры не сильно показательны, так как модуль pg в ruby делает эту работу чуть более незаметно. Тем не менее необходимо отметить тот факт что PostgreSQL делает  JSON ощутимо быстрее многих высокоуровненых языков (судя по бенчмаркам), и использования этого функционала может существенно оптимизировать алгоритмическую сложность программы. Во-вторых ещё раз напомню про правильный формат возвращаемых значений. Ну и наконец никто не мешает получать JSON используя стандартный psql, что выглядит так же достаточно интересно

$ echo "SELECT array_to_json(array_agg(row_to_json(articles))) FROM articles;" | psql -h 192.168.200.104 -U postgres test | sed -n '3p'

И все. На выходе вы получаете самый настоящий массив JSON.

Нужно сказать что в планируемом релизе PostgreSQL 9.3 функционал работы с JSON будет существенно увеличен, вместо 2х функций появятся с десяток позволяющий превращать не только данные в JSON но и JSON в данные. Так что ждем релиза и наблюдаем...


comments powered by Disqus