This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2024-01-04
Channels
- # announcements (1)
- # babashka (1)
- # beginners (84)
- # biff (22)
- # calva (9)
- # cider (8)
- # clerk (5)
- # clj-kondo (10)
- # clojure (105)
- # clojure-europe (13)
- # clojure-nl (1)
- # clojure-norway (44)
- # clojure-spec (4)
- # clojure-uk (6)
- # clojuredesign-podcast (36)
- # cursive (13)
- # datomic (24)
- # dev-tooling (8)
- # emacs (8)
- # hyperfiddle (4)
- # jobs (1)
- # leiningen (2)
- # london-clojurians (1)
- # lsp (5)
- # malli (6)
- # membrane (11)
- # nyc (1)
- # off-topic (14)
- # other-languages (8)
- # pathom (25)
- # pedestal (2)
- # re-frame (4)
- # releases (1)
- # remote-jobs (1)
- # shadow-cljs (98)
- # sql (5)
- # squint (1)
- # tools-deps (38)
- # vim (8)
- # xtdb (11)
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"}]}]}
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!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?
For your original data structure?
Yes for the original structure.
I think your query is filtering correctly. Is it that you want to have the whole document returned instead of just the ids?
Oh wait no, if I change it to "Assistant 1" then it's wrong :thinking_face:
If I am changing it to “Assistant 3” then it returning both of them for some reason.
Actually, I can't replicate that. Changing it to 3 or 4 works fine for me :thinking_face:
Could you post the exact query you're running for "Assistant 3"? Just in case it's different