xtdb 2023-07-21 | Slack Archive

hairfire21:07:43

I'm playing around with XTDB 2.0 EA for an internal company presentation. I'm using the XTDB 1.x learning material here https://nextjournal.com/try/learn-xtdb-datalog-today/learn-xtdb-datalog-today as an outline. I've managed to get the person and movie data loaded into the Docker XTDB 2.0 EA server, and I've managed to get most of the Datalog queries translated to SQL. There's one query I'm having trouble with:

(q '{:find [name]
     :where [[p :person/name "Arnold Schwarzenegger"]
             [m :movie/cast p]
             [m :movie/director d]
             [d :person/name name]]})

How do I handle the "movie.cast" field in SQL?

hifumi12300:07:56

Is there a reason for converting to SQL? In any case, you probably want a join. :where clauses implicitly join

refset16:07:35

> How do I handle the "movie.cast" field in SQL? You can use $ in place of the edn / separator within keywords, so movie$cast should work

hairfire13:07:25

The reason for converting to SQL is to demonstrate to my coworkers that one can use SQL with XTDB.

hairfire14:07:39

What I mean by using "movie.cast" is that I've inserted both the "movie" and "person" data into XTDB 2.0 from the original XTDB 1.x example, and the "movie" data has a "cast" field that is a vector. The Datalog query seems to treat the vector as some kind of "join". I thought perhaps the XTDB 2.0 SQL engine might do the same thing as the XTDB 1.x Datalog engine. I guess it makes sense that I would need to create a many-to-many XTDB 2.0 "cast" table, and use it in a SQL "join"

refset15:07:51

Ah I see, can you share a working (or non-working!) snippet/gist? Did you insert the movies and persons into separate tables? A join table is the traditional way to do things and would give the best performance at scale, but you should still be able to get it to work with brute-force scanning of vector values using UNNEST (which I think can be used to unwrap the vectors/arrays)

hairfire15:07:02

I'll create a gist, and post the link here. 'UNNEST' is that in the docs somewhere that I've not read?

👍 2

refset15:07:37

Not in the docs yet, but it's part of the ISO SQL spec that we've implemented so far. There some examples in tests suggesting that it should be working https://github.com/xtdb/xtdb/blob/01be1e7d1d97c77702f3198f7565456b5b3b1143/src/test/clojure/xtdb/sql_test.clj#L159

👍 2

hairfire18:07:45

Here's a link to the XTDB 2.0 EA demo program in my http://github.com account: https://github.com/pwhittin/xtdb2ea-sql-demo/blob/04a0020296232937f4f401fb8f3dcb6b2d2275a4/src/rlws/xtdb2.clj#L402 As the comment says, I don't know how to write a query to answer the question using UNNEST in SQL. Any pointers?

2023-07-21

Channels