[2.x] Conditionally Construct List/Vector Of Id's

[bnert]

Hey there! Finally getting around to learning XTQL

I’ve gone throught the Learn XTQL Today With Clojure, and am working w/ the dataset’s (i.e. movies and persons).

As a personal challenge, I prompted myself to solve:

“Get all the peoples names involved in making ‘RoboCop’?”

And I think there is something I may be missing conceptually.

The best query I came up with is:

(xt/q node '(-> (unify
                      (from :movies [{:movie/title "RoboCop"} movie/cast movie/director])
                      (unnest {c movie/cast})
                      (from :persons [{:xt/id pid} person/name])
                      (where (or (= pid c)
                                 (= pid movie/director))))
                    (return person/name)))

However, the above yeilds:

[#:person{:name "Paul Verhoeven"}
 #:person{:name "Paul Verhoeven"}
 #:person{:name "Paul Verhoeven"}
 #:person{:name "Ronny Cox"}
 #:person{:name "Peter Weller"}
 #:person{:name "Nancy Allen"}]

And I was not expecting “Paul Verhoeven” to show up 3 times, but after looking more into “unnest” I understand the why behind the repitition (i.e. the array is “unnest”-ed, then matched/joined/unified against the persons table).

I then tried to construct a vector/list like so:

(xt/q node '(-> (unify
                      (from :movies [{:movie/title "RoboCop"} movie/cast movie/director])
                      (unnest {c (conj movie/cast movie/director)})
                      (from :persons [{:xt/id pid} person/name])
                      (where (or (= pid c)
                                 (= pid movie/director))))
                    (return person/name)))

However, that yeilds the output of:

; eval (current-form): (xt/q node '(-> (unify (from :movies [{:mo...
; (err) Execution error (IllegalArgumentException) at xtdb.error/illegal-arg (error.clj:11).
; (err) conj not applicable to types list and list

Given I was not accounting for there being a list of id’s for :movie/director.

Finally, I attempted to conditionally call conj or concat depending
on if :movie/director was a list/vector or not, to no avail, like so:

(xt/q node '(-> (unify
                    (from :movies
                          [{:movie/title "RoboCop"}
                           movie/cast
                           movie/director])
                    (unnest
                      {a (cond
                           (list? movie/director)
                             (concat movie/cast movie/director)
                           (conj movie/cast movie/director))})
                    (from :persons [{:xt/id a} person/name]))
                  (return person/name)))

and I also tried using with:

(xt/q node '(-> (unify
                    (from :movies
                          [{:movie/title "RoboCop"}
                           movie/cast
                           movie/director])
                    (with
                      {all (cond
                             (list? movie/director)
                               (concat movie/cast movie/director)
                             (conj movie/cast movie/director))})
                    (unnest {a all})
                    (from :persons [{:xt/id a} person/name]))
                  (return person/name)))

Both yeilded:

; eval (current-form): (xt/q node '(-> (unify (from :movies [{:mo...
; (err) Execution error (IllegalArgumentException) at xtdb.error/illegal-arg (error.clj:11).
; (err) list? not applicable to types list

All the above to say, I’m stumped w/ how to solve this at this point in time. I also tried multiple (from :persons ...), which yeilded an empty result.

---

Given the context of the above, what am I not considering/missing with respect to the current API? How should I go about solving the prompt at the start?

And finally, really great work to the whole team at Juxt for this peice of tech. Really excited to see v2 mature and have been enjoying diving back in and reading the dev diaries. Keep it up!

[refset]

Hey @bnert thanks for the question and for walking us through all the things you tried!

@bnert

I was not expecting “Paul Verhoeven” to show up 3 times, but after looking more into “unnest” I understand the why behind the repitition (i.e. the array is “unnest”-ed, then matched/joined/unified against the persons table)

Unfortunately XTQL currently lacks a DISTINCT equivalent (along with other set operators) which is probably is what you want to use here - the progress on that is being tracked in: XTQL set operators · Issue #2980 · xtdb/xtdb · GitHub

That aside, the general issue you are hitting here is that XTQL does not incorporate any clojure.core standard library functions or semantics (unlike v1). Therefore your examples of conj/list? simply aren’t available - instead you need to look to the XTQL (/SQL) standard library to figure out the equivalents. In some cases XTQL does include functions that correspond to clojure.core naming, but SQL semantics (3VL, bags etc.) are always followed.

@bnert

I then tried to construct a vector/list like so

XTQL can handle some of this directly via literals e.g.

(->
  (from :docs [xt/id foo])
  (unnest {:u [xt/id foo "etc"]}))

Runnable example here.

@bnert

I was not accounting for there being a list of id’s for :movie/director.

Finally, I attempted to conditionally call conj or concat depending
on if :movie/director was a list/vector or not, to no avail

(note that cond does exist in XTQL - mapping to SQL’s CASE - but it doesn’t necessarily help us here)

This was as close as I could get

(->
  (unify
    (from :docs [xt/id movie/cast movie/director])
    (left-join (->
                 (from :docs [xt/id movie/cast movie/director])
                 (unnest {:md-list-entries movie/director})
                 (aggregate {:c (count md-list-entries)
                             :id-with-md-list xt/id}))
      [{:id-with-md-list xt/id :c c}]))
  (unnest {:cast-entry movie/cast})
  (with {:involved-in (if (> c 0)
                        [movie/director
                         cast-entry
                         "TODO implement array concat/append"]
                        [movie/director
                         cast-entry])}))

Runnable example here.

As indicated by the TODO, support for concat on arrays needs to be implemented still. We have a bunch of similar requests for the standard library being processed currently so I will make sure this gets attention soon.

Hopefully you’re not too blocked from learning/progressing the meantime and thanks again for sharing your experiences!

[jarohen]

Hey @bnert

union-all is your friend for this one - as @refset says, it’s not been fully ported over to XTQL from SQL yet, but it’ll look something like this:

'(union-all (-> (unify (from :movies [{:movie/title "RoboCop"} movie/cast])
                       (unnest {c movie/cast})
                       (from :persons [{:xt/id c} person/name]))
                (return person/name))

            (-> (unify (from :movies [{:movie/title "RoboCop"} movie/director])
                       (from :persons [{:xt/id movie/director} person/name]))
                (return person/name)))

In this case, we’re taking one relation for the cast, one relation for the director (which we can test/debug independently), and combining them with union-all (which is essentially concat for relations).

HTH, and thanks for giving XTQL a spin!

James

edit: I hadn’t spotted that some movies have a list of directors . Openly, I don’t think I’d model it this way in XT2 - in practice, if some are required to be vectors I’d likely make them all vectors, and then unnest in the second union-all branch too. Otherwise, yes, XT2’s currently missing some of the the primitives you need (list? etc) to work with this data effectively. I’ll see if we can make the example data more friendly!

[bnert]

Thank you for the responses @jarohen, and @refset! They’re really helpful with shifting my mental model of XTQL in it’s current state. Doesn’t hinder me diving in deeper, and really appreciate the forum ya’ll have open here to foster communal understanding.

I’ve put responses to both your answers below in order to not have a bunch o’ comments/threads for the same content.

---

That aside, the general issue you are hitting here is that XTQL does not incorporate any clojure.core standard library functions or semantics (unlike v1). Therefore your examples of conj /list? simply aren’t available - instead you need to look to the XTQL (/SQL) standard library to figure out the equivalents. In some cases XTQL does include functions that correspond to clojure.core naming, but SQL semantics (3VL, bags etc.) are always followed.

Hit the nail on the head there, and my reasoning was that if concat was available (which I tried first), then cond/list? may be available as well. I have a vague recollection that before XTQL, but still v2 there was some support for clojure.core functions, but I may be mistaken.

A peice of feedback here, if ya’ll are up for fielding it, is that I definitely would have benefitted from having some disclaimer or information about where XTQL draws the line with respect to having some operators/predicates/expressions. Looking at the docs page, and drilling down from “What is XTQL?” to “Reference > XTQL > Queries” and further drilling down into “Reference > Standard Library > Overview” and “Reference > Standard Library Predicates”, I can peice that together given the context the responses ya’ll have given. My hunch is that I won’t be the only one to run into this hurdle, but, it is merely a hunch I could have missed this in the docs as well, so may need to spend more time in there.

---

edit: I hadn’t spotted that some movies have a list of directors . Openly, I don’t think I’d model it this way in XT2 - in practice, if some are required to be vectors I’d likely make them all vectors, and then unnest in the second union-all branch too. Otherwise, yes, XT2’s currently missing some of the the primitives you need (list? etc) to work with this data effectively. I’ll see if we can make the example data more friendly!

I’m with you there, maybe an exercise the the reader on how to model the dataset to express what they need to for the domain. On the plus side, it gave us the chance to have a little back n’ forth, and I learned along the way, so there is silver lining on my end!

[refset]

Thanks again for all the feedback - I will simplify the dataset to only use vector values OR scalar values, as it is the mixture that makes it particularly convoluted to work with. It will probably also help to have an example dataset with explicit join tables too

I definitely would have benefitted from having some disclaimer or information about where XTQL draws the line with respect to having some operators/predicates/expressions

This is a good suggestion, we will review and fix accordingly!

[bnert]

It will probably also help to have an example dataset with explicit join tables too

Agree! Adding a “nudge” toward learning that side of the query language in the “Learn XTQL Today With Clojure” would be useful for building an intuition on when to reach of join/inner-join. I’m still playing around w/ the query language and defining models to build said intuition.