Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2024-01-04

Channels

#xtdb
<
2024-01-04
>
Namit Shah06:01:51

I have the following 2 documents stored 

{:xt/id :movie-11
 :movie/id 11
 :movie/title "Top Gun"
 :movie/actors [{:name "Tom Cruise"
                 :age 57
                 :assistants [{:name "Assistant 1"
                               :skill "Stunt Man"}
                              {:name "Assistant 2"
                               :skill "Makeup"}]}
                {:name "Kelly McGillis"
                 :age 62
                 :assistants [{:name "Assistant 3"
                               :skill "Makeup"}]}
                {:name "Val Kilmer"
                 :age 60
                 :assistants [{:name "Assistant 2"
                               :skill "Makeup"}]}]}


{:xt/id :movie-12
 :movie/id 12
 :movie/title "Mission Impossible"
 :movie/actors [{:name "Tom Cruise"
                 :age 57
                 :assistants [{:name "Assistant 1"
                               :skill "Stunt Man"}
                              {:name "Assistant 2"
                               :skill "Makeup"}]}
                {:name "Rebecca Ferguson"
                 :age 37
                 :assistants [{:name "Assistant 4"
                               :skill "Stunt Woman"}
                              {:name "Assistant 5"
                               :skill "Makeup"}]}
                {:name "Simon Pegg"
                 :age 50
                 :assistants [{:name "Assistant 6"
                               :skill "Stunt Man"}
                              {:name "Assistant 7"
                               :skill "Makeup"}]}]}
Now I want to filter the documents based on the following criteria, • The Assistant’s name should be “Assistant 2” The query I have written is as follows, 
{:find [?movie]
 :where [[?movie :movie/actors ?actor]
         [(get ?actor :assistants) ?assistants]
         [(mapv :name ?assistants) ?aname]
         [(some #{"Assistant 2"} ?aname)]]}
Is there something wrong with the query? Can someone please help me with this? Apart from this, is there any way to also filter the actors inside the movie documents where the age of the actor is greater than 50? Expected output for the movie with ID 12 looks like, 
{:xt/id :movie-12
 :movie/id 12
 :movie/title "Mission Impossible"
 :movie/actors [{:name "Tom Cruise"
                 :age 57
                 :assistants [{:name "Assistant 1"
                               :skill "Stunt Man"}
                              {:name "Assistant 2"
                               :skill "Makeup"}]}]}

Oliver Marshall10:01:20

Hi @U04DDKZJERF, I think that's going to be quite difficult with a nested data structure like that. XTDB v1 works best on data nested at the top level (that's what it indexes). Instead you could break up the document into lots of smaller documents like this (assuming assistants are related to actors, not movies): 

(xt/await-tx xtdb-node
  (xt/submit-tx xtdb-node
    [;; Movies
     [::xt/put {:xt/id :movie-11
                :movie/id 11
                :movie/title "Top Gun"
                :movie/actors #{:tom-cruise
                                :kelly-mcgillis
                                :val-kilmer}}]
     [::xt/put {:xt/id :movie-12
                :movie/id 12
                :movie/title "Mission Impossible"
                :movie/actors #{:tom-cruise
                                :rebecca-ferguson
                                :simon-pegg}}]

     ;; Actors
     [::xt/put {:xt/id :tom-cruise
                :actor/name "Tom Cruise"
                :actor/age 57
                :actor/assistants #{:assistant-1
                                    :assistant-2}}]
     [::xt/put {:xt/id :kelly-mcgillis
                :actor/name "Kelly McGillis"
                :actor/age 62
                :actor/assistants #{:assistant-3}}]
     [::xt/put {:xt/id :val-kilmer
                :actor/name "Val Kilmer"
                :actor/age 60
                :actor/assistants #{:assistant-2}}]
     [::xt/put {:xt/id :rebecca-ferguson
                :actor/name "Rebecca Ferguson"
                :actor/age 37
                :actor/assistants #{:assistant-4
                                    :assistant-5}}]
     [::xt/put {:xt/id :simon-pegg
                :actor/name "Simon Pegg"
                :actor/age 50
                :actor/assistants #{:assistant-6
                                    :assistant-7}}]

     ;; Assistants
     [::xt/put {:xt/id :assistant-1
                :assistant/name "Assistant 1"
                :assistant/skill "Stunt Man"}]
     [::xt/put {:xt/id :assistant-2
                :assistant/name "Assistant 2"
                :assistant/skill "Makeup"}]
     [::xt/put {:xt/id :assistant-3
                :assistant/name "Assistant 3"
                :assistant/skill "Makeup"}]
     [::xt/put {:xt/id :assistant-4
                :assistant/name "Assistant 4"
                :assistant/skill "Stunt Woman"}]
     [::xt/put {:xt/id :assistant-5
                :assistant/name "Assistant 5"
                :assistant/skill "Makeup"}]
     [::xt/put {:xt/id :assistant-6
                :assistant/name "Assistant 6"
                :assistant/skill "Stunt Man"}]
     [::xt/put {:xt/id :assistant-7
                :assistant/name "Assistant 7"
                :assistant/skill "Makeup"}]]))

;; First query for the movies who "Assistant 2" has worked on
(xt/q (xt/db xtdb-node)
  '{:find [?movie]
    :where [[?movie :movie/actors ?actor]
            [?actor :actor/assistants ?assistant]
            [?assistant :assistant/name "Assistant 2"]]})
;; => #{[:movie-12] [:movie-11]}

;; Then use `pull` to enrich the movie
(xt/q (xt/db xtdb-node)
  '{:find [(pull ?movie [;; Get everything
                         *
                         ;; Join on actors
                         {:movie/actors [*
                                         ;; Join on assistants
                                         {:actor/assistants [*]}]}])]
    :where [[?movie :movie/actors ?actor]
            [?actor :actor/assistants ?assistant]
            [?assistant :assistant/name "Assistant 2"]]})
Afaik you can't filter inside the pull :thinking_face:, so I can't immediately think of a way to get actors greater than 50 outside of filtering in the code or doing two queries. If someone else can, please correct me!

Namit Shah10:01:38

Thanks for the suggestion @U5W78K2UF. Previously I had separate documents for them but while performing the joins, the query got slowed down. Wanted to experiment with a denormalised document but then came across this problem of filtering. I think the second part regarding the age filtering is probably not possible in the same query. Is there some way the first part can be achieved? The query which I am trying to run, shouldn’t it work?

Oliver Marshall10:01:07

For your original data structure?

Namit Shah10:01:22

Yes for the original structure.

Oliver Marshall10:01:00

I think your query is filtering correctly. Is it that you want to have the whole document returned instead of just the ids?

Oliver Marshall10:01:32

Oh wait no, if I change it to "Assistant 1" then it's wrong :thinking_face:

Namit Shah10:01:32

If I am changing it to “Assistant 3” then it returning both of them for some reason.

1
Oliver Marshall10:01:03

Actually, I can't replicate that. Changing it to 3 or 4 works fine for me :thinking_face:

Oliver Marshall10:01:58

Could you post the exact query you're running for "Assistant 3"? Just in case it's different