Turning DB information into JSON with keys?

Question

I have a database that returns data as a tree like this:

'((7 "vince" "vince1@test.com" "space" "no value" 1)
  (8 "vince" "vince2@test.com" "place" "no value" 1)
  (9 "Smith" "Smith@gmail.com" "now" "no value" 1))

The second column is first name and the third column is email.

My goal is to return JSON key value pairs but im struggling

Here is what I have tried:

1. Function to get name and email from one list item

(defun get-name-&-emails-db1 (lst)
  (if (null lst)
      nil
      (let* ((name (second lst))
             (email (third lst)))
    (cl-json:encode-json-to-string `((:name . ,name)(:email . ,email))))))

2. Map over data set

(mapcar #'get-name-&-emails-db1 (return-data-tree))

This returns a list of individual json blocks. But I want it to be ONE json block with all records.

What am I missing?

(ideally, I want to know how to do this without any additional libraries)

Thanks

Answer 1

Use mapcar, get the second and third element of each entry, and then call cl-json:encode-json-to-string on the result:

(let ((data '((7 "vince" "vince1@test.com" "space" "no value" 1)
              (8 "vince" "vince2@test.com" "place" "no value" 1)
              (9 "Smith" "Smith@gmail.com" "now" "no value" 1))))
  (cl-json:encode-json-to-string 
   (mapcar (lambda (e) `((:name . ,(second e))(:email . ,(third e))))
           data)))