Saving JSON data to DB in Zotonic

Question

Im trying to write a small app that retrieves a JSON file (it contains a list of items, which all have some properties), saves its contents to the DB and then displays some of it later on. I have Zotonic up and running, and generating some HTML is no problem.
ATM i'm stuck trying to figure out how to define a custom resource and how to get the data from the JSON in the DB. When the data is there I should be fine, that part seems covered ok by the documentation.
I wrote some standalone erlang scripts that fetch the data and I noticed that Zotonic has a library for decoding JSON so that part should be fine. Any tips on where to put which code or where to look further?

Answer 1

The z_db module allows for creating custom tables by using:

z_db:create_table(Table, Cols, Context).

The Table variable is your table name which can be either an atom or a list containing a single atom.
The Cols is a list of column definitions, which are defined by records. Currently the record definition (you can find this in include/zotonic.hrl) is:

-record(column_def, {name, type, length, is_nullable=true, default, primary_key}).

See Erlang docs on records for more info on records

Example code which I put in users/sites/[sitename]/models/m_[sitename].erl:

init(Context) ->
        case z_db:table_exists(?table,Context) of
        false ->
                z_db:create_table(tablename,
                [
                #column_def{name=id, type="serial"},
                #column_def{name=gid, type="integer", is_nullable=false},
                #column_def{name=magnitude, type="real"},
                #column_def{name=depth, type="real"},
                #column_def{name=location, type="character varying"},
                #column_def{name=time, type="integer"},
                #column_def{name=date, type="integer"}
                ], Context);
        true -> ok
        end,
        ok.

Pay attention to what options of the record you specify. Most of the errors I got were e.g. from specifying a length on the integer fields.

The models/m_sitename:init/1 does not get called on site start. The sitename:init/1 does get called so I call the init function there to ensure the table exists. Example:

init(Context) ->
        m_sitename:init(Context).

It is called by Zotonic with the Context variable of the site automatically. You can get this variable manually as well with z:c(sitename).. So if you call the m_sitename:init(Context). from somewhere else you would do:

m_sitename:init(z:c(sitename)).  

Next, insertion in the DB can be done with:

z_db:insert(Table, PropList, Context).

Where Table is again an atom or a list containing a single atom representing the table name. Context is the same as above.
PropList is a property list which is a list containing tuples consisting of two elements where the first is an atom and the second is its associated value/property. Example:

PropList = [
            {row, Value},
            {anotherrow, AnotherValue}
           ].

Table = tablename.
Context = z:c(sitename).
z_db:insert(Table, PropList, Context).

See Erlang docs on Property Lists for more info on property lists.

=== The dependencies have been updated so if you build from source the step directly below is no longer needed ===

The JSON part is bit more tricky. Included with Zotonic are mochijson2 and as a secondary dependency also jiffy. The latest version of jiffy contains jiffy:decode/2 which allows you to specify maps as a return type. Much more readable than the standard {struct, {struct, <<"">>}} monster. To update to the latest version edit the line in deps/twerl/rebar.config that says

{jiffy, ".*", {git, "https://github.com/davisp/jiffy.git", {tag, "0.8.3"}}},

to

{jiffy, ".*", {git, "https://github.com/davisp/jiffy.git", {tag, "0.14.3"}}},

Now run z:m(). in the Zotonic shell. (you must do this after every change in your code).
Now check in the Zotonic shell if there is a jiffy:decode/2 available by typing jiffy: <tab>, it will show a list of available functions and their arity.

To retrieve a JSON file from the internet run:

{ok, {{_, 200, _}, _, Body}} = httpc:request(get, {"url-to-JSON-here", []}, [], [])

Which will yield the variable Body with the contents. See Erlang docs on http client for more info on this call.

Next convert the contents of Body to Erlang terms with:

JsonData = jiffy:decode(Body, [return_maps]).

What you have to do next depends a lot on the structure of your JSON resource. Keep in mind that everything is now in binary UTF-8 encoded strings! If you print JsonData to screen (just enter JsonData. in your Zotonic/Erlang shell) you will see a lot of #map{<<"key"", <<"Value">>} this.
My data was nested so I had to extract the needed data like this:

[{_,ItemList}|_] = ListData.

This gave me a list of maps, and in order to deal with them as individual items I used the following function:

 get_maps([]) ->
     done; 
 get_maps([First|Rest]) ->
     Map = maps:get(<<"properties">>, First),
     case is_map(Map) of
             true ->
                     map_to_proplist(Map),
                     get_maps(Rest);
             false -> done
             end,
     done; 
get_maps(_) ->
     done.

As you might remember, the z_db:insert/3 function needs a property list to populate rows, so that what the call to map_to_proplist/1 is for. How this function looks is completely dependent on how your data looks but as an example here is what worked for me:

map_to_proplist(Map) ->
        case is_map(Map) of
                true ->
                        {Value1,_}         = string:to_integer(binary_to_list(maps:get(<<"key1">>, Map))),
                        {Value2,_}   = string:to_float(binary_to_list(maps:get(<<"key2">>, Map))),
                        {Value3,_}       = string:to_float(binary_to_list(maps:get(<<"key3">>, Map))),
                        Value4        = binary_to_list(maps:get(<<"key4">>, Map)),
                        {Value5,_}        = string:to_integer(binary_to_list(maps:get(<<"key5">>, Map))),
                        {Value6,_}        = string:to_integer(binary_to_list(maps:get(<<"key6">>, Map))),
                        PropList = [{rowname1, Value1}, {rowname2, Value2}, {rowname3, Value3}, {rowname4, Value4}, {rowname5, Value5}, {rowname6, Value6}],
                        m_sitename:insert_items(PropList,z:c(sitename)),
                        ok;
                false ->
                        ok
                end.

See the documentation on string:to_list/1 as to why the tuples are needed when casting. The call to m_sitename:insert_items(PropList,z:c(sitename)) calls the z_db:insert/3 in models/m_sitename.erl but wrapped in a catch:

insert_items(PropList,Context) ->
        (catch z_db:insert(?table, PropList, Context)).

Ok, quite a long post but this should get you up and running if you were looking for this answer.

The above was done with Zotonic 0.13.2 on Erlang/OTP 18.
A repost (except the JSON part) of my post in the Zotonic Developers group.