To Json from Postgresql/Postmodern

1 Intro

Suppose the front end of an app needs data as a json string and you need to get the data out of a database and convert it to that format. There are several ways to do that. We will look at doing it with basic sql, s-sql and a dao class. For purposes of this note, we are not looking at jsonb type columns in Postgresql.

To make things a little more interesting, we are going to have a private column which we do not want to pass to the front-end, a Postgresql point datatype column and we will have a geometry type (using postgis) to compare that to the point type. If you do not have postgis installed, you can find installation instruction here: https://postgis.net/install/ or just read the the postgis stuff without trying to run the code.

I am going to use the local-time library to deal with dates, so we need to do a little housework on that side as well.

(ql:quickload '(local-time cl-postgres+local-time))
(local-time:set-local-time-cl-postgres-readers)

2 The Basic SQL Version

Assuming you already have a database to use, let's create a couple of tables and insert some data.

(pomo:query "CREATE TABLE departments (
      department_id bigint primary key,
      name text
      )")

(pomo:query "CREATE TABLE employees (
        employee_id serial primary key,
        department_id integer references departments(department_id),
        name text,
        start_date date,
        contact text[],
        private text,
        lat_long point,
        geom geometry(point, 4326)json-from-p
        );")

(pomo:query "INSERT INTO departments
   (department_id, name)
  VALUES
   (1, 'spatial'),
   (2, 'cloud')")

(pomo:query "INSERT INTO employees
 (department_id, name, start_date, contact, private, lat_long, geom)
VALUES
 (1, 'Maja',   '2018/09/02', '{"084-767-734","071-334-8473"}', 'not allowed',
 '(59.334591, 18.063240)', 'POINT(59.334591 18.063240)'),
 (1, 'Liam', '2019/09/02', '{"084-767-734","071-334-8472"}','private',
 '(57.708870, 11.974560)','POINT(57.708870 11.974560)'),
 (2, 'Matteo',  '2019/11/01', '{"084-767-734","071-334-8476"}', 'burn before reading',
   '(58.283489,12.285821)','POINT(58.283489 12.285821)'),
 (2, 'Astrid',    '2020/10/01',  '{"084-767-734","071-334-8465"}', 'abandon all hope',
  '(57.751442, 16.628838)', 'POINT(57.751442 16.628838)');")

One difference to note is that the data for the lat_long point data type has a comma, but the geom geometry data type does not have a comma. No, I do not know why the syntax difference.

I want to flag something that can surprise people. The lat_long column is a Postgresql point datatype. That means it is an array. As you may recall, Postgresql arrays start at 1, not 0. Except here. If you wanted just the latitude for the row with the employee_id of 1, you would actually call for array 0.

(pomo:query "select lat_long[0] from employees where employee_id=1" :single)
59.334591d0

If you wanted to get the latitude and longitude in a list, it would look like:

(pomo:query "select lat_long[0], lat_long[1] from employees where employee_id=1")
((59.334591d0 18.06324d0))

If you ran a basic query to see how Postgresql was storing that geometry type, it would look something like this:

  (pomo:query "select geom from employees where employee_id=1" :single)
"0101000020E61000009A44BDE0D3AA4D408ECC237F30103240"

To actually get the separate latitude and longitude from the geom column, you need to use Postgresql functions st_x and st_y like so:

(query "select st_x(geom), st_y(geom) from employees where employee_id=1")
((59.334591d0 18.06324d0))

Now on to getting this information as json. Postgresql gives you a json generator function that takes a tuple and returns a json dictionary. So, for example:

(query "select row_to_json(employees) from employees where employee_id=1")
(("{\"employee_id\":1,
    \"department_id\":1,
    \"name\":\"Maja\",
    \"start_date\":\"2018-09-02\",
    \"contact\":[\"084-767-734\",\"071-334-8473\"],
    \"private\":\"not allowed\",
    \"lat_long\":\"(59.334591,18.06324)\",
    \"geom\":{\"type\":\"Point\",\"coordinates\":[59.334591,18.06324]}}"))

You can see that it would automatically break out the geom data. However, as written, it has the fatal flaw of also collecting the private info. That can get solved with a slight modification:

(query "select row_to_json(e)
        from
          (select employee_id, department_id, name, start_date, contact, lat_long, geom
           from employees where employee_id=1) e")
(("{\"employee_id\":1,
 \"department_id\":1,
 \"name\":\"Maja\",
 \"start_date\":\"2018-09-02\",
 \"contact\":[\"084-767-734\",\"071-334-8473\"],
 \"lat_long\":\"(59.334591,18.06324)\",
 \"geom\":{\"type\":\"Point\",\"coordinates\":[59.334591,18.06324]}}"))

You can also aggregate rows using the Postgresql json_agg function.

(query "select json_agg(e)
        from
          (select employee_id, department_id, name, start_date, contact, lat_long, geom
           from employees)
        e")

You could skip the Postgresql json function and ask Postmodern to return the query as a json object expressed as a string. One thing to note is that Postmodern will return the labels as camelCase rather than Postgresql returning them as underscores:

 (query "select employee_id, department_id, name, start_date, contact, lat_long, geom
         from employees
         where employee_id=1"
        :json-str)
"{\"employeeId\":1,\"departmentId\":1,\"name\":\"Maja\",\"startDate\":\"{2018-09-01T20:00:00.000000-04:00}\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"latLong\":[59.334591,18.06324],\"geom\":\"0101000020E61000009A44BDE0D3AA4D408ECC237F30103240\"}"

You would need to do a little more work in order to get the desired latitude and longitude out of the geom value.

(query "select employee_id, department_id, name, start_date, contact, lat_long, st_x(geom) as lat, st_y(geom) as long
             from employees where employee_id=1" :json-str)

"{\"employeeId\":1,\"departmentId\":1,\"name\":\"Maja\",\"startDate\":\"{2018-09-01T20:00:00.000000-04:00}\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"latLong\":[59.334591,18.06324],\"lat\":59.334591,\"long\":18.06324}"

Both the Postgresql function and the Postmodern return type approach can be applied to the end result of more complicated queries with joins, CTEs and other tools of the trade. Which is actually why we have the department table in this example. Instead of having the department-id in the json we are sending to the front end, let's have the department name.

First the using the Postgresql row-to-json function:

(query "select row_to_json(e)
        from  (select employee_id, departments.name as department_name, employees.name as employee_name,
                      start_date, contact, lat_long, geom
               from employees
               left join departments
               on departments.department_id = employees.department_id
               where employee_id=1) e")
(("{\"employee_id\":1,\"department_name\":\"spatial\",\"employee_name\":\"Maja\",\"start_date\":\"2018-09-02\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"lat_long\":\"(59.334591,18.06324)\",\"geom\":{\"type\":\"Point\",\"coordinates\":[59.334591,18.06324]}}"))

Now the sql using the Postmodern :json-str keyword parameter for query:

(query "select employee_id, departments.name as department_name, employees.name as employee_name,
               start_date, contact, lat_long, geom
        from employees
        left join departments
        on departments.department_id = employees.department_id
        where employee_id=1"
     :json-str))
"{\"employeeId\":1,\"departmentName\":\"spatial\",\"employeeName\":\"Maja\",\"startDate\":\"{2018-09-01T20:00:00.000000-04:00}\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"latLong\":[59.334591,18.06324],\"geom\":\"0101000020E61000009A44BDE0D3AA4D408ECC237F30103240\"}"

3 The Basic S-SQL Version

Assuming you already have a database to use, let's create a couple of tables and insert some data.

  (pomo:query (:create-table 'departments
                             ((department-id :type (or pomo:db-null bigint) :primary-key t)
                              (name :type (or pomo:db-null text)))))

  (pomo:query (:create-table employees
                             ((employee_id :type serial :primary-key t)
                              (department_id :type (or pomo:db-null integer) :references ((departments department_id)))
                              (name :type (or pomo:db-null text))
                              (start_date :type (or pomo:db-null date))
                              (contact :type (or pomo:db-null text[]))
                              (private :type (or pomo:db-null text))
                              (lat_long :type (or pomo:db-null point))
                              (geom :type (or pomo:db-null (geometry point 4326))))))

  (pomo:query (:insert-rows-into 'departments
               :columns 'deparment-id 'name
               :values '((1 "spatial") (2 "cloud"))))

(pomo:sql (:insert-rows-into 'employees
               :columns 'department-id 'name 'start-date 'contact 'private 'lat_long 'geom
               :values
         '((1 "Maja"   "2018/09/02" #("084-767-734""071-334-8473") "not allowed"
         "(59.334591, 18.063240)" "POINT(59.334591 18.063240)")
         (1 "Liam" "2019/09/02" #("084-767-734" "071-334-8472") "private"
         "(57.708870, 11.974560)" "POINT(57.708870 11.974560)")
         (2 "Matteo"  "2019/11/01" #("084-767-734""071-334-8476") "burn before reading"
            "(58.28348912.285821)" "POINT(58.283489 12.285821)")
         (2 "Astrid"    "2020/10/01"  #("084-767-734""071-334-8465") "abandon all hope"
            "(57.751442, 16.628838)" "POINT(57.751442 16.628838)"))))

One difference to note is that the data for the lat_long point data type has a comma, but the geom geometry data type does not have a comma. No, I do not know why the syntax difference in Postgresql (Postmodern needs it to properly match Postgresql's syntax here).

(pomo:query (:select (:[] 'lat_long 0) :from 'employees :where (:= 'employee_id 1)) :single)
59.334591d0

If you wanted the latitude and longitude in alist, the query would look like:

(pomo:query (:select (:[] 'lat_long 0) (:[] 'lat_long 1) :from 'employees :where (:= 'employee_id 1)))
((59.334591d0 18.06324d0))

If you ran a basic query to see how Postgresql was storing that geometry type, it would look something like this:

(pomo:query (:select 'geom :from 'employees :where (:= 'employee-id 1)) :single)
  "0101000020E61000009A44BDE0D3AA4D408ECC237F30103240"

To actually get the separate latitude and longitude from the geom column, you need to use Postgresql functions st_x and st_y like so:

(with-connection *dba-connection* (query (:select (:st-x 'geom) (:st-y 'geom) :from 'employees :where (:= 'employee_id 1))))
((59.334591d0 18.06324d0))

Now on to getting this information as json. Postgresql gives you a json generator function that takes a tuple and returns a json dictionary. So, for example:

(pomo:query (:select (:row-to-json 'employees) :from 'employees :where (:= 'employee-id 1)))
  (("{\"employee_id\":1,
      \"department_id\":1,
      \"name\":\"Maja\",
      \"start_date\":\"2018-09-02\",
      \"contact\":[\"084-767-734\",\"071-334-8473\"],
      \"private\":\"not allowed\",
      \"lat_long\":\"(59.334591,18.06324)\",
      \"geom\":{\"type\":\"Point\",\"coordinates\":[59.334591,18.06324]}}"))

You can see that it would automatically break out the geom data. However, as written, it has the fatal flaw of also collecting the private info. That can get solved with a slight modification:

(query (:select (:row-to-json 'e)
        :from (:as (:select 'employee-id 'department-id 'name 'start-date 'contact
                            'lat-long 'geom
                    :from 'employees
                    :where (:= 'employee-id 1))
                   'e)))
(("{\"employee_id\":1,
   \"department_id\":1,
   \"name\":\"Maja\",
   \"start_date\":\"2018-09-02\",
   \"contact\":[\"084-767-734\",\"071-334-8473\"],
   \"lat_long\":\"(59.334591,18.06324)\",
   \"geom\":{\"type\":\"Point\",\"coordinates\":[59.334591,18.06324]}}"))

You can also aggregate rows using the Postgresql json_agg function.

(query (:select (:json-agg 'e)
        :from (:as (:select 'employee-id 'department-id 'name 'start-date 'contact
                            'lat-long 'geom
                    :from 'employees)
                   'e)))

  (query (:select 'employee-id 'department-id 'name 'start-date 'contact 'lat-long 'geom
          :from 'employees
          :where (:= 'employee-id 1)) :json-str)

"{\"employeeId\":1,\"departmentId\":1,\"name\":\"Maja\",\"startDate\":\"{2018-09-01T20:00:00.000000-04:00}\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"latLong\":[59.334591,18.06324],\"geom\":\"0101000020E61000009A44BDE0D3AA4D408ECC237F30103240\"}"

You would need to do a little more work in order to get the desired latitude and longitude out of the geom value.

(query (:select 'employee-id 'department-id 'name 'start-date 'contact 'lat-long
                (:st-x 'geom) (:st-y 'geom)
                :from 'employees
                :where (:= 'employee-id 1))
       :json-str)
"{\"employeeId\":1,\"departmentId\":1,\"name\":\"Maja\",\"startDate\":\"{2018-09-01T20:00:00.000000-04:00}\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"latLong\":[59.334591,18.06324],\"stX\":59.334591,\"stY\":18.06324}"

First the s-sql using the Postgresql row-to-json function:

 (query (:select (:row-to-json 'e)
         :from (:as (:select 'employee-id (:as 'departments.name 'department_name)
                             (:as 'employees.name 'employee-name)
                             'start-date 'contact 'lat-long
                             (:st-x 'geom) (:st-y 'geom)
                     :from 'employees
                     :left-join 'departments
                     :on (:= 'departments.department-id 'employees.department-id)
                     :where (:= 'employee-id 1))
                'e)))

(("{\"employee_id\":1,\"department_name\":\"spatial\",\"employee_name\":\"Maja\",\"start_date\":\"2018-09-02\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"lat_long\":\"(59.334591,18.06324)\",\"st_x\":59.334591,\"st_y\":18.06324}"))

Now the s-sql using the Postmodern :json-str keyword parameter for query:

(query (:select 'employee-id (:as 'departments.name 'department-name)
                (:as 'employees.name 'employee-name)
                'start-date 'contact 'lat-long (:st-x 'geom) (:st-y 'geom)
        :from 'employees
        :left-join 'departments
        :on (:= 'departments.department-id 'employees.department-id)
        :where (:= 'employee-id 1))
       :json-str)
"{\"employeeId\":1,\"departmentName\":\"spatial\",\"employeeName\":\"Maja\",\"startDate\":\"{2018-09-01T20:00:00.000000-04:00}\",\"contact\":[\"084-767-734\",\"071-334-8473\"],\"latLong\":[59.334591,18.06324],\"stX\":59.334591,\"stY\":18.06324}"

4 The Basic Dao-class Version

Assuming you already have a database to use, let's create a couple of dao classes, their associated tables and insert some data. Assume we decide we want to keep the geom as a list of latitude and longitude in the geom slot. That means we need import and export functions.

(defclass departments ()
  ((department-id :col-type serial :initarg :department-id :accessor department-id
                  :col-primary-key t)
   (name :col-type (or text pomo:db-null) :initarg :name :accessor name))
  (:metaclass pomo:dao-class))

(pomo:execute (dao-table-definition 'departments))

(defclass employees ()
  ((employee-id :col-type serial :initarg :employee-id :accessor employee-id
                :col-primary-key t)
   (department-id :col-type integer :initarg :department-id :accessor department-id
                  :col-references ((departments department-id)))
   (name :col-type text :initarg name :accessor name)
   (start-date :col-type (or date pomo:db-null) :initarg start-date :accessor start-date)
   (contact :col-type (or pomo:db-null (array text)) :initarg contact :accessor contact)
   (private :col-type (or pomo:db-null text) :initarg private :accessor private)
   (lat-long :col-type (or pomo:db-null point) :initarg lat-long :accessor lat-long)
   (geom :col-type (or pomo:db-null (geometry point 4326)) :initarg geom :accessor geom
         :col-import geom->wkb-point))
  (:metaclass pomo:dao-class))

;; make-doa creates an instance of the dao and saves it in the database
(pomo:make-dao 'departments :department-id 1 :name "spatial")
(pomo:make-dao 'departments :department-id 2 :name "cloud")

(pomo:make-dao 'employees :department-id 1 :name "Maja" :start-date "2018/09/02"
                          :contact #("084-767-734","071-334-8473")
                          :private "not allowed" :lat-long "(59.334591, 18.063240)"
                          :geom "POINT(59.334591 18.063240)")

(pomo:make-dao 'employees :department-id 1 :name "Liam" :start-date "2019/09/02"
                          :contact #("084-767-734","071-334-8472")
                          :private "private" :lat-long "(57.708870, 11.974560)"
                          :geom "POINT((57.708870 11.974560)")

(pomo:make-dao 'employees :department-id 2 :name "Matteo" :start-date "2019/11/01"
                          :contact #("084-767-734","071-334-8476")
                          :private "burn before reading" :lat-long "(58.283489, 12.285821)"
                          :geom "POINT(58.283489 12.285821)")

(pomo:make-dao 'employees :department-id 2 :name "Astrid" :start-date "2020/10/01"
                          :contact #("084-767-734","071-334-8465")
                          :private "abandon all hope" :lat-long "(57.751442, 16.628838)"
                          :geom "POINT(57.751442 16.628838)")

One difference to note is that the data for the lat_long point data type has a comma, but the geom geometry data type does not have a comma. No, I do not know why the syntax difference.

Now the problem. If you ran a basic query to see how Postgresql was storing that geometry type, it would look something like this:

  (pomo:query "select geom from employees where employee_id=1" :single)
"0101000020E61000009A44BDE0D3AA4D408ECC237F30103240"

We need import and export functions that implement the opengis specification in order to implement the import and export functions for the geom slot. See https://www.ogc.org/standards/sfs. Fortunately J.P. Larocue created the cl-wkb package (accessed via quicklisp with quickloading the cl-ewkb system) and we can create an import function with a combination of using ironclad's hex-string-to-byte-array and cl-wkb's decode function. So let's do that.

(defun geom->wkb-point (input)
  "Takes a hexstring that represents a geometry point from postgresql and returns a cl-wkb:point class instance"
  (cl-wkb:decode (ironclad:hex-string-to-byte-array input)))

Now we can check whether we succeeded by seeing whether the x point is the latitude we expected:

  (cl-wkb:x (geom (pomo:get-dao 'employees 1)))
59.334591d0

We still need to get from the dao-class to json. You could do something like just run cl-json's =encode-json=function on a dao-object like so:

(cl-json:encode-json (pomo:get-dao 'employees 1))

{"employeeId":1,
 "departmentId":1,
 "name":"Maja",
 "startDate":{"day":6759,"sec":0,"nsec":0},
 "contact":["084-767-734","071-334-8473"],
 "private":"not allowed",
 "latLong":[59.334591,18.06324],
 "geom":{"geomtype":536870913,"srid":4326,"pointPrimitive":{"x":59.334591,"y":18.06324,"z":0.0,"m":0.0}}}

Looking at the result, we have two issues. First, the start date seems to have lost its senses. Second, it is collecting and passing on the private data to the front end, which we explicitly did not want to do.

Just checking on the date situation:

(start-date (pomo:get-dao 'employees 1)))
@2018-09-01T20:00:00.000000-04:00

That works, so it is something on the cl-json side that we will have to work around. Let's turn to the private data issue.

One solution would be to create a dao-class that is only a subset of the employees table (minus the private data) and set pomo:*ignore-unknonw-columns* to t. (If we did not set pomo:*ignore-unknonw-columns*, we would generate an error complaining that the dao was not in sync with the table.) Let's do that:

  (defclass employees-minus-private ()
            ((employee-id :col-type serial :initarg :employee-id :accessor employee-id :col-primary-key t)
             (department-id :col-type integer :initarg :department-id :accessor department-id :col-references ((departments department-id)))
             (name :col-type text :initarg name :accessor name)
             (start-date :col-type (or date pomo:db-null) :initarg start-date :accessor start-date)
             (contact :col-type (or pomo:db-null (array text)) :initarg contact :accessor contact)
             (lat-long :col-type (or pomo:db-null point) :initarg lat-long :accessor lat-long)
             (geom :col-type (or pomo:db-null (geometry point 4326)) :initarg geom :accessor geom
                   :col-import geom->wkb-point))
            (:table-name employees)
            (:metaclass pomo:dao-class))

(setf pomo:*IGNORE-UNKNOWN-COLUMNS* t)

And now cl-json generates a json string without the

(cl-json:encode-json (pomo:get-dao 'employees-minus-private 1))
{"employeeId":1,"departmentId":1,"name":"Maja","startDate":3744835200,"contact":["084-767-734","071-334-8473"],"latLong":[59.334591,18.06324],"geom":{"geomtype":536870913,"srid":4326,"pointPrimitive":{"x":59.334591,"y":18.06324,"z":0.0,"m":0.0}}}

If you are using a different CL json library, you would have to write your own functions to convert from a dao-class object to something that, e.g. jonathan or jsown could use.

Handling joins in a dao-class are more complicated - the Postmodern dao-class is intended to be simple, not recreate Hibernate or SQLAlchemy. You can see an example at https://marijnhaverbeke.nl/postmodern/dao-classes.html#multi-table-dao-class-object.