Can it use ->> for jsonb?

[ZacksTsang]

json_extract_path_text is to long...

[r0man]

@ZacksTsang Is ->> really the same as json_extract_path_text? I'm not too familiar with PostgreSQL JSON features. If something isn't available in compiler.clj you can always extend some of it's multimethods yourself.

(defmethod compile-fn :->> [db node]
  (let [[_ & args] (:children node)]
    (concat-sql "json_extract_path_text("
                (compile-sql-join db ", " args) ")")))

(select (db :postgresql) [`(->> "{\"f2\":{\"f3\":1},\"f4\":{\"f5\":99,\"f6\":\"foo\"}}" "f4" "f6")])
;=> ["SELECT json_extract_path_text(?, ?, ?)" "{\"f2\":{\"f3\":1},\"f4\":{\"f5\":99,\"f6\":\"foo\"}}" "f4" "f6"]

[r0man]

Test this for a while, and if it's good we can add it ..

[ZacksTsang]

perfect, i will try it

[whench18]

Hello, fir this problem I suggest you should try the long path tool. This will help you solve all long path issues.

[ZacksTsang]

@whench18, what's the long path tool?

[whench18]

https://longpathtool.com/Downloads.html

try this

[AlJohri]

@r0man, I'm not sure it's exactly the same. For example, I think the GIN index can only be used if querying via the operator not the function.

I'm currently trying to do a query like this:

SELECT a.id as article_id, a.original->'meta'->'headline' AS headline

Is this possible using sqlingvo?

[AlJohri]

@r0man I was trying to implement this via compile-fn but I can't figure out any way to use single quotes for meta in the example above.

I thought something as simple as:

(defarity compile-2-ary
  "->" "->>")

might accomplish it but I can't get around the quote issue.

[AlJohri]

I made a little bit of progress but the function is super hacky right now:

defintion

(defmethod compile-fn :-> [db node]
  (let [[name & args] (:children node)]
    (assert (< 1 (count args)) "More than 1 arg needed.")
    (->> (map (fn [[arg-1 & arg-rest]]
                (concat-sql "("
                            (apply str (interpose (clojure.core/name (:val name)) (concat (compile-expr db arg-1) (->> arg-rest (map :val) (map clojure.core/name) (map #(str "'" % "'"))))))
                            ")" ))
              (partition (count args) 1 args)))))

usage

  (println (sql/select my-db [`(~(keyword "->") :original :meta :headline)]
             (sql/from :articles)
             (sql/limit 1)))

output

[SELECT (("original"->'meta'->'headline')) FROM "articles" LIMIT 1]

psql

              ?column?
-------------------------------------
 "China’s fever, everyone’s disease"
(1 row)

Unfortunately when I try to use the sql/as everything screws up.

Any thoughts on how to get this working?

[AlJohri]

I turned to honeysql for now since it supports a sql/raw function. Does sqlingvo have a similar raw function?

My use case was to grab all of the fields from my jsonb column named original:

(sql/format (-> (apply select (->> fields
                                   (map clojure.core/name)
                                   (map #(vector (sql/raw (str "original->'" % "'")) %))))
                (from :articles)
                (limit 10)
                (offset 10)))

[r0man]

@ZacksTsang and @AlJohri Would this branch solve your needs? https://github.com/r0man/sqlingvo/pull/76/files

[AlJohri]

@r0man thanks a bunch! works for me!

Here is how I was able to use it for future reference:

(defn get-articles-querymap [lim off fields]
  (select my-db [(as `(-> :original "_id") :_id)
                 (as `(-> :original "date") :date)
                 (as `(-> :original "analyzed-date") :analyzed-date)
                 (as `(-> :original "meta") :meta)
                 (as `(-> :original "meta" "headline") :headline)]
    (from (as :articles :a))
    (limit 1)))

Two other concerns/issues:

1) I'm running into an issue I described in #77 where I want to be able to pass in a string "analyzed-date" for the as function. If I pass it in as a keyword, it gets underscorized. I only want that behavior on the way "in" to postgres so like column names. For something like "as" its defining how the data will come "out" of postgres.

2) Is there a way to not use reader macros here?

Here is an implementation where I'm taking an arbitrary vector of keywords representing the fields.

(defn get-articles-querymap [lim off fields]
  (select my-db (map #(as `(-> :original ~(clojure.core/name %)) %) fields)
    (from (as :articles :a))
    (limit 1)))

As you can see, I have to use both ` and ~ which I'd prefer not to. Is there an alternative style you can think of? Thanks!

[AlJohri]

EDIT on issue 2, I realize now that it's pretty core to how the library works. I'd still be curious if you knew of an alternative style.

For example, could I just supply := instead and then quote it as list instead of using the backtick?

(defn films-by-kind [db kind]
  (select db [:id :name]
    (from :films)
    (where `(= :kind ~kind))))

[r0man]

@AlJohri You can do this:

(defn films-by-kind [db kind]
  (select db [:id :name]
    (from :films)
    (where (list '= :kind kind))))

(films-by-kind (db :postgresql) :drama)

But that's a bit more verbose, hence the backquote style.

[r0man]

@ZacksTsang & @AlJohri I release 0.9.4 with a fix for this issue.

[ZacksTsang]

@r0man Thanks.