Question 109816 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Turning DB information into JSON with keys?

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

'((7 "vince" "[email protected]" "space" "no value" 1)
  (8 "vince" "[email protected]" "place" "no value" 1)
  (9 "Smith" "[email protected]" "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))))))
  1. 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

Tags:

4

Answers


  1. 0

    I tried to encode a list of alists, and this is how it goes:

    USER> (cl-json:encode-json 
        (list '(("a" . "0") ("b" . "1")) '(("a" . "2") ("b" . "3"))))

[{"a":"0","b":"1"},{"a":"2","b":"3"}]

    If this is what you want to have, then you need to organize your data in Lisp first, then encode the whole list as JSON instead of formatting each entry individually.

    Login or Signup to reply.
  2. 0

    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" "[email protected]" "space" "no value" 1)
              (8 "vince" "[email protected]" "place" "no value" 1)
              (9 "Smith" "[email protected]" "now" "no value" 1))))
  (cl-json:encode-json-to-string 
   (mapcar (lambda (e) `((:name . ,(second e))(:email . ,(third e))))
           data)))
    Login or Signup to reply.
  3. 0

    Here I don’t use comma, backquote, alists or plists, but simply: I create a list of hash-tables. I’m quite sure how a list and a hash table are rendered in JSON, so let’s rework our data a bit to come back in known territories.

    (loop for row in '((7 "vince" "[email protected]" "space" "no value" 1)
                   (8 "vince" "[email protected]" "place" "no value" 1)
                   (9 "Smith" "[email protected]" "now" "no value" 1))
   with result = (list)  ;; the list of hash-tables to encode.
   for ht = (make-hash-table)   ;; intermediary hash-table.
   do (setf (gethash "name" ht)
            (second row)
            (gethash "email" ht)
            (third row))
     (push ht result)
   finally (return (cl-json:encode-json-to-string result)))

;; =>
"[{"name":"Smith","email":"[email protected]"},{"name":"vince","email":"[email protected]"},{"name":"vince","email":"[email protected]"}]"

    I like Serapeum’s dict:

                  ;; replaces (for ht = (make-hash-table)) and the setf
              for ht = (dict :name (second row)
                             :email (third row))
    Login or Signup to reply.
  4. 0

    Answers were given. Just a general way to deal with alist and json:

    (ql:quickload :yason)

(defparameter *data* '((7 "vince" "[email protected]" "space" "no value" 1)
                       (8 "vince" "[email protected]" "place" "no value" 1)
                       (9 "Smith" "[email protected]" "now" "no value" 1)))
(defparameter *scheme* '(:id :name :email :meta :value :count))

(defun pairing (keys values)
  (loop for a in keys
        for b in values
        collect (cons (string-downcase (format nil "~A" a)) b)))

(defun alist-json (x &keys keys)
  (with-output-to-string (*standard-output*)
    (yason:encode-alist (pairing keys x))))

(defun list-json (l)
  (format nil "[~{~A~^, ~}]" l))

(defun values-list-keys-json (values-list keys)
  (list-json (mapcar (lambda (x) (alist-json x :keys keys)) values-list)))

(values-list-keys-json *data* *scheme*)
#|
=> "[{"id":7,"name":"vince","email":"[email protected]","meta":"space","value":"no value","count":1}, 
{"id":8,"name":"vince","email":"[email protected]","meta":"place","value":"no value","count":1}, 
{"id":9,"name":"Smith","email":"[email protected]","meta":"now","value":"no value","count":1}]"
|#

(defun second-third (l)
  (subseq l 1 3))

(values-list-keys-json (mapcar #'second-third *data*) (second-third *scheme*))
#|
=> "[{"name":"vince","email":"[email protected]"}, {"name":"vince","email":"[email protected]"}, {"name":"Smith","email":"[email protected]"}]"
|#
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search