Beginner datalog query in XTQL v2 w/ Clojure

Users
,
1

During innovation days at work im trying out XTDB on one of our services, however wrapping my head around XTQL deemed harder then expected. I have the following query:

(defn os->vps
  [& ss]
  (let [args (mapv (fn [s] {:o-id s}) ss)]
    (xt/q node '(-> (unify (rel $opids [o-id])
                           (from :ts [oIds tId])
                           (unnest {o-id oIds})
                           (from :vps [vpid tIds])
                           ;; this is the part I dont get
                           (where all tId in tIds) ; example what I want to express
                           ;; give me all vpid where all matching tId is contained in the tIds list
                           ;; tIds can contain more ids but it must atlaest contain all matched
                           )
                    (return vpid))
          {:args {:opids args}})))

First I want to find tid that has a reference to the oid I send in args. Then given the resulting matching tid, I want to find the vps where the column tIds contains all the matced tid. What do I need to change to make such a query and what is wrong in my thinking preventing me from figuring this out? I think the key part im missing is how use all. unnest find any but I want at-least some subset.

2

Hey @favetelinguis thanks for giving v2 a try!

Are you able to share examples of the structure of the documents in :ts and :vps? From your description and query it sounds like you may be looking for (where (= tId tIds)). However if tIds is a vector value, then you would want a second unnest instead: (unnest {tId tIds}} (re-using the same variable name will create the join implicitly)

Hope that helps,

Jeremy

3

Hi @refset thanks a ton for taking your time, I find it somewhat hard to explain what I want but I dont think your suggestions works in my case. Please see the below data to better understand what im after. Please feel free to ask more details if I am not including the correct details.

; this is :ts
[{:tId "t1" :oIds ["o1" "o2"]}
 {:tId "t2" :oIds ["o2" "o3"]}
 {:tId "t3" :oIds ["o4" "o4"]}
 {:tId "t4" :oIds ["o1" "o5"]}
 {:tId "t5" :oIds ["o9" "o8"]}
]
; (unnest {o-id oIds})
; the first unnest does what I expect "give me all tIds that contains ANY of the oIds"
[{:tId "t1"} {:tId "t2"}] ; This is the result of the first unnest which is correct.

; this is :vps
[{:vpid "v1" :tIds ["t1" "t4"]}
 {:vpid "v2" :tIds ["t1" "t2" "t3"]}
 {:vpid "v3" :tIds ["t1" "t3"]}
 {:vpid "v4" :tIds ["t1" "t4" "t2"]}
 ]
; now I want the vpid where all the tIds needs to be included in the tIds array, another
; level of unnest will not give
; The result of the query should be [{:vpid "v2"} {:vpid "v4"}]
4

Hey again, thanks for the example data - that’s much easier to reason about :slightly_smiling_face:

This is as close as I could get:

(->
  (unify
    (join (->
            (unify
              (rel [{:o-id "o2"} {:o-id "o3"}] [o-id])
              (rel [{:tId "t1" :oIds ["o1" "o2"]}
                    {:tId "t2" :oIds ["o2" "o3"]}
                    {:tId "t3" :oIds ["o4" "o4"]}
                    {:tId "t4" :oIds ["o1" "o5"]}
                    {:tId "t5" :oIds ["o9" "o8"]}]
                [tId oIds])
              (unnest {o-id oIds}))
            (aggregate {:matched-tids (array-agg tId)}))
      [matched-tids])
    (rel [{:vpid "v1" :tIds ["t1" "t4"]}
          {:vpid "v2" :tIds ["t1" "t2" "t3"]}
          {:vpid "v3" :tIds ["t1" "t3"]}
          {:vpid "v4" :tIds ["t1" "t4" "t2"]}]
      [vpid tIds]))
  #_(return vpid))

Which returns:

{:vpid "v1", :t-ids ["t1" "t4"], :matched-tids ["t1" "t2" "t2"]}
{:vpid "v2", :t-ids ["t1" "t2" "t3"], :matched-tids ["t1" "t2" "t2"]}
{:vpid "v3", :t-ids ["t1" "t3"], :matched-tids ["t1" "t2" "t2"]}
{:vpid "v4", :t-ids ["t1" "t4" "t2"], :matched-tids ["t1" "t2" "t2"]}

Unfortunately XTQL is currently lacking some useful set-related functionality, particularly distinct and an equivalent to Postgres’ <@ array function - we’ll review where these are on the backlog.

Use of XT’s SQL may be an option instead, if you need a workaround in the meantime.

Jeremy

1 Like
5

Thanks for the support. Looking forward to keep using Xtdb.

1 Like
6

Okay correction - I spoke too soon(!) - thanks to help from @jarohen I can present the “correlated not exists” workaround:

(->
  (unify
    (join (->
            (unify
              (rel [{:o-id "o2"} {:o-id "o3"}] [o-id])
              (from :ts [{:xt/id t-id} o-ids])
              (unnest {o-id o-ids}))
            (aggregate {:matched-t-ids (array-agg t-id)}))
      [matched-t-ids])
    (from :vs [{:xt/id vpid} {:t-ids vp-t-ids}])
    (where (not (exists? (->
                           (rel [{}] [])
                           (unnest {:matched-t-id $matched-t-ids})
                           (where (not (exists? (->
                                                  (rel [{}] [])
                                                  (unnest {:vp-t-id $vp-t-ids})
                                                  (where (= $matched-t-id vp-t-id)))
                                         {:args [matched-t-id {:vp-t-ids $vp-t-ids}]}))))
                  {:args [matched-t-ids vp-t-ids]}))))
  (return vpid))

Given the data:

[:put-docs :ts
 {:xt/id "t1" :o-ids ["o1" "o2"]}
 {:xt/id "t2" :o-ids ["o2" "o3"]}
 {:xt/id "t3" :o-ids ["o4" "o4"]}
 {:xt/id "t4" :o-ids ["o1" "o5"]}
 {:xt/id "t5" :o-ids ["o9" "o8"]}]

[:put-docs :vs
 {:xt/id "v1" :t-ids ["t1" "t4"]}
 {:xt/id "v2" :t-ids ["t1" "t2" "t3"]}
 {:xt/id "v3" :t-ids ["t1" "t3"]}
 {:xt/id "v4" :t-ids ["t1" "t4" "t2"]}]

Produces:

{:vpid "v2"}
{:vpid "v4"}

You can test it out on the highly experimental XT fiddle: https://fiddle.xtdb.com/

<@ or equivalent would undoubtedly be nicer, but hopefully that workaround at least demonstrates the compositional powers of XTQL.