HSTORE to Erlang to JSON for PostgreSQL 9.1

hstore_erlang_json-postgresql9.1.md

PostgreSQL 9.1 does not support the JSON datatype.

I want to use the HSTORE column type for metadata which eventually will be returned as part of JSON encoded structure. Unfortunately PostgreSQL 9.1 does not have the funky JSON goodies (which are available from 9.2).

Retrieving data

{ok, Cols, Rows} = dbutils:equery("select msisdn, operator, subscribe_datetime, uuid, 
                                   created_at, updated_at,hstore_to_matrix(metadata) as metadata from sometable limit 1", []). 
{ok,[{column,<<"msisdn">>,int8,8,-1,1},
     {column,<<"operator">>,int4,4,-1,1},
     {column,<<"subscribe_datetime">>,timestamptz,8,-1,1},
     {column,<<"uuid">>,{unknown_oid,2950},16,-1,0},
     {column,<<"created_at">>,timestamptz,8,-1,1},
     {column,<<"updated_at">>,timestamptz,8,-1,1},
     {column,<<"metadata">>,textarray,-1,-1,1}],
    [{27830000000,65510,
      {{2012,8,25},{18,12,53.0}},
      <<"7471545b-bd39-3fce-1b83-93eb237173df">>,
      {{2013,7,10},{7,44,33.570597}},
      {{2013,7,12},{9,32,12.88201}},
      [[<<"a">>,<<"1">>],[<<"b">>,<<"2">>],[<<"c">>,<<"3">>]]}]}

Proplist = dbutils:transduce(Cols, Rows).
[[{<<"msisdn">>,27830000000},
  {<<"operator">>,65510},
  {<<"subscribe_datetime">>,{{2012,8,25},{18,12,53.0}}},
  {<<"uuid">>,<<"7471545b-bd39-3fce-1b83-93eb237173df">>},
  {<<"created_at">>,{{2013,7,10},{7,44,33.570597}}},
  {<<"updated_at">>,{{2013,7,12},{9,32,12.88201}}},
  {<<"metadata">>,
   [[<<"a">>,<<"1">>],[<<"b">>,<<"2">>],[<<"c">>,<<"3">>]]}]]

% Do some magic to convert this.
% Note specifically the metadata conversion:
%
% NewMetada = lists:map(fun erlang:list_to_tuple/1, Metadata)
%
P2 = [{<<"msisdn">>,27830000000},
      {<<"operator">>,65510},
      {<<"subscribe_datetime">>,<<"2012-08-25T18:12:53">>},
      {<<"uuid">>,<<"7471545bbd393fce1b8393eb237173df">>},
      {<<"created_at">>,<<"2013-07-10T07:44:33">>},
      {<<"updated_at">>,<<"2013-07-12T09:32:12">>},
      {<<"metadata">>, [{<<"a">>,<<"1">>},
                        {<<"b">>,<<"2">>},
                        {<<"c">>,<<"3">>}
                       ]}
     ],

JsonString = io_lib:format("~s~n", [mochijson2:encode(P2)]).

% Result (formatted for readability)
%
% {
%  "msisdn":27830000000,
%  "operator":65510,
%  "subscribe_datetime":"2012-08-25T18:12:53",
%  "uuid":"7471545bbd393fce1b8393eb237173df",
%  "created_at":"2013-07-10T07:44:33",
%  "updated_at":"2013-07-12T09:32:12",
%  "metadata":{"a":"1","b":"2","c":"3"}
% }
%

Inserting data

Given a proplist of type list({string(), string()}) or list({binary(),binary()}) (which is easily obtained from the QueryArgs), the following can be done:

Proplist = [{"c a", "3"}, {"d", "4 5"}].

proplist_to_hstore_sql(P) ->
    Map = fun ({K,V}) ->
        io_lib:format("['~s','~s']", [K, V])
    end,

    ["hstore(ARRAY[", string:join(lists:map(Map, P), ", "), "]::text[])"].