Use :result in query

72 views
Skip to first unread message

johan....@gmail.com

unread,
Oct 19, 2017, 1:59:57 PM10/19/17
to SlamData-User
Hi, 

I have a preview table that looks like this:

























I'd like to create a new query based on these event ids. What I've tried is this:

SELECT *  
FROM `/test-database/test` AS t
where t.uid IN :results

but when I try to visualize the query result in a Preview card it just says that "The selected source is empty". 

Is there anyway I can use the values in ":result" to find entities like I've tried to do above?

/Johan 

johan....@gmail.com

unread,
Oct 23, 2017, 12:06:20 AM10/23/17
to SlamData-User
I've also tried various variants of this to no avail: 

SELECT p.data 
from `/test-database/test` as t
join :results as r
    on t.uid = r.eventId[0]

/Johan

do...@slamdata.com

unread,
Oct 23, 2017, 12:47:15 PM10/23/17
to SlamData-User
Johan, :results is a tabular representation of the results from the prior steps in the workspace. In your example this holds several rows of a single value of eventId. This eventId appears to be a string, yet you're trying to access it as an array.

Can you explain what results you're expecting to get. Are you hoping to join all results from :results to the corresponding values in test? Are you just trying to join to the first returned result? Just seeing queries, but not knowing what, if anything, is being returned and not knowing what you're expecting makes it hard to provide potential solutions.

Johan Haleby

unread,
Oct 23, 2017, 1:10:11 PM10/23/17
to do...@slamdata.com, SlamData-User
Thanks for your reply. 



On Mon, Oct 23, 2017 at 6:47 PM, <do...@slamdata.com> wrote:
Johan, :results is a tabular representation of the results from the prior steps in the workspace. In your example this holds several rows of a single value of eventId. This eventId appears to be a string, yet you're trying to access it as an array. 

Can you explain what results you're expecting to get. Are you hoping to join all results from :results to the corresponding values in test? Are you just trying to join to the first returned result? Just seeing queries, but not knowing what, if anything, is being returned and not knowing what you're expecting makes it hard to provide potential solutions.

Yes, eventId's are all stings. What I want to do is to create a new query that looks up all documents with a field (uid) that match ANY of the eventId's shown in the preview card (that I included in the previous mail above). But I don't know how such a query should be constructed. This is what I want to do (in psuedo SQL code):

SELECT *  
FROM `/test-database/test` AS t
where t.uid IN <any of the eventId's shown in the preview card, i.e. bce71024-adSc-4dee-9aff-e22609d53b32, 691cf330-a1c6-4582-afo7-adf828f3f574 and so on>

I.e. I want to return all documents in `/test-database/test` that has a field called uid equal to any of the eventId's that were shown in the preview card.
 
Hope this makes sense.

Regards,
/Johan




On Sunday, October 22, 2017 at 10:06:20 PM UTC-6, johan....@gmail.com wrote:
I've also tried various variants of this to no avail: 

SELECT p.data 
from `/test-database/test` as t
join :results as r
    on t.uid = r.eventId[0]

/Johan

On Thursday, October 19, 2017 at 7:59:57 PM UTC+2, johan....@gmail.com wrote:
Hi, 

I have a preview table that looks like this:

























I'd like to create a new query based on these event ids. What I've tried is this:

SELECT *  
FROM `/test-database/test` AS t
where t.uid IN :results

but when I try to visualize the query result in a Preview card it just says that "The selected source is empty". 

Is there anyway I can use the values in ":result" to find entities like I've tried to do above?

/Johan 

--
You received this message because you are subscribed to a topic in the Google Groups "SlamData-User" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/slamdata-user/83dJGCkULQE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to slamdata-user+unsubscribe@googlegroups.com.
Visit this group at https://groups.google.com/group/slamdata-user.
 To view this discussion on the web visit https://groups.google.com/d/msgid/slamdata-user/d4f8e961-bd67-4b17-b70e-38c5f79085e9%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

do...@slamdata.com

unread,
Oct 23, 2017, 3:40:14 PM10/23/17
to SlamData-User
Have you tried:

SELECT p.data 
from `/test-database/test` as t
join :results as r
    on t.uid = r.eventId

This would JOIN test and :restults on together on the fields mentioned in the ON portion.
Thanks for your reply. 



To unsubscribe from this group and all its topics, send an email to slamdata-use...@googlegroups.com.

John A. De Goes

unread,
Oct 23, 2017, 5:28:15 PM10/23/17
to do...@slamdata.com, SlamData-User

The types of `uid` and `eventId` must be the same (in your example, string). If one of them is a OID, then the join will fail for that reason.

John



You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-use...@googlegroups.com.

Johan Haleby

unread,
Oct 23, 2017, 11:55:07 PM10/23/17
to do...@slamdata.com, SlamData-User
Yes I've tried that as well but then I get the following error:


An error occured when counting the preview data.

Failed to execute SQL^2 query.: MongoDB Error: Command failed with error 13548: 'BufBuilder attempted to grow() to 134217728 bytes, past the 64MB limit.' on server test-database:27017. The full response is { "ok" : 0.0, "errmsg" : "BufBuilder attempted to grow() to 134217728 bytes, past the 64MB limit.", "code" : 13548, "codeName" : "Location13548" }


The test collection contains 1301263 documents.

Regards,
/Johan

To unsubscribe from this group and all its topics, send an email to slamdata-user+unsubscribe@googlegroups.com.

Visit this group at https://groups.google.com/group/slamdata-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/slamdata-user/40dc1bd8-9ba2-485b-8fad-ddadca95da0b%40googlegroups.com.

Johan Haleby

unread,
Oct 23, 2017, 11:55:41 PM10/23/17
to John A. De Goes, do...@slamdata.com, SlamData-User
They are both strings, but thanks for the tip.

To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-user+unsubscribe@googlegroups.com.

--
You received this message because you are subscribed to a topic in the Google Groups "SlamData-User" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/slamdata-user/83dJGCkULQE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to slamdata-user+unsubscribe@googlegroups.com.

Visit this group at https://groups.google.com/group/slamdata-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/slamdata-user/99BB3495-848C-4156-8A14-5D4576B353DD%40degoes.net.

Jeff Carr

unread,
Oct 24, 2017, 12:06:13 AM10/24/17
to Johan Haleby, Douglas Duncan, SlamData-User
What version of Mongo?


--
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-user+unsubscribe@googlegroups.com.

Visit this group at https://groups.google.com/group/slamdata-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/slamdata-user/CAK3tncL%2BD6GhfnBLBpH%3Dndr5uC2q242370nt%2BpVL3y2kjjV9Bg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.



--
Jeff A. Carr
Co-Founder & CEO


Johan Haleby

unread,
Oct 24, 2017, 12:08:25 AM10/24/17
to Jeff Carr, Douglas Duncan, SlamData-User
We're using 3.4.9

Johan Haleby

unread,
Oct 24, 2017, 5:52:08 AM10/24/17
to Jeff Carr, Douglas Duncan, SlamData-User
FYI when I try to add a cache card before the query I run into this error instead:

An error occured when counting the preview data.

Failed to execute SQL^2 query.: MongoDB Error: Command failed with error 17260: 'Converting from JavaScript to BSON failed: Object size 16867336 exceeds limit of 16793600 bytes.' on server test-database:27017. The full response is { "ok" : 0.0, "errmsg" : "Converting from JavaScript to BSON failed: Object size 16867336 exceeds limit of 16793600 bytes.", "code" : 17260, "codeName" : "Location17260" }



John A. De Goes

unread,
Oct 24, 2017, 10:45:37 AM10/24/17
to johan....@gmail.com, Jeff Carr, Douglas Duncan, SlamData-User

This is a sign the join is being done via map/reduce and not lookup. Try adding an index to one or both fields.

John




To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-use...@googlegroups.com.

Johan Haleby

unread,
Oct 24, 2017, 10:52:38 AM10/24/17
to John A. De Goes, Douglas Duncan, Jeff Carr, SlamData-User
Both fields are (to the best of my knowledge) indexed already. 

We're using 3.4.9

What version of Mongo?



-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-use...@googlegroups.com.



-- 
Jeff A. Carr
Co-Founder & CEO



-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-use...@googlegroups.com.
Visit this group at https://groups.google.com/group/slamdata-user.
--
Sent from my phone

John A. De Goes

unread,
Oct 24, 2017, 10:57:03 AM10/24/17
to Johan Haleby, Douglas Duncan, Jeff Carr, SlamData-User

Try one more thing: `SELECT p.data AS data …`

Single columns without aliases are treated equivalent to SELECT VALUE, which creates problems for MongoDB that end in a map/reduce.

Damon LaCaille

unread,
Oct 24, 2017, 10:57:58 AM10/24/17
to SlamData-User
You can find out more about the requirements for $lookup here: http://docs.slamdata.com/en/latest/sql-squared-reference.html#joins-on-mongodb

  • Must be running MongoDB 3.2 or newer.
  • One collection must use an indexed field.
  • That collection must not be sharded.
  • Both collections must be in the same database.
  • Match must be an equijoin, based on equality only (a.field = b.field is ok, a.field < b.fieldis not).
We're using 3.4.9

What version of Mongo?


To unsubscribe from this group and all its topics, send an email to slamdata-user+unsubscribe@googlegroups.com.

-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-user+unsubscribe@googlegroups.com.



-- 
Jeff A. Carr
Co-Founder & CEO



-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-user+unsubscribe@googlegroups.com.

Johan Haleby

unread,
Oct 25, 2017, 12:36:01 AM10/25/17
to John A. De Goes, Douglas Duncan, Jeff Carr, SlamData-User
tis 24 okt. 2017 kl. 16:57 skrev John A. De Goes <jo...@degoes.net>:

Try one more thing: `SELECT p.data AS data …`

Single columns without aliases are treated equivalent to SELECT VALUE, which creates problems for MongoDB that end in a map/reduce.

John

Thanks for the suggestion but this gives me:


Johan Haleby

unread,
Oct 25, 2017, 12:38:23 AM10/25/17
to SlamData-User, da...@slamdata.com
tis 24 okt. 2017 kl. 16:57 skrev Damon LaCaille <da...@slamdata.com>:
You can find out more about the requirements for $lookup here: http://docs.slamdata.com/en/latest/sql-squared-reference.html#joins-on-mongodb

  • Must be running MongoDB 3.2 or newer.
  • One collection must use an indexed field.
  • That collection must not be sharded.
  • Both collections must be in the same database.
  • Match must be an equijoin, based on equality only (a.field = b.field is ok, a.field < b.fieldis not).

Afaik all of the above criteria should be fulfilled.  
We're using 3.4.9

What version of Mongo?



-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-use...@googlegroups.com.



-- 
Jeff A. Carr
Co-Founder & CEO



-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-use...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.
--
Sent from my phone

--
You received this message because you are subscribed to a topic in the Google Groups "SlamData-User" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/slamdata-user/83dJGCkULQE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to slamdata-use...@googlegroups.com.
Visit this group at https://groups.google.com/group/slamdata-user.

For more options, visit https://groups.google.com/d/optout.

John A. De Goes

unread,
Oct 25, 2017, 7:42:53 AM10/25/17
to Johan Haleby, Douglas Duncan, Jeff Carr, SlamData-User
Sounds like a bug. Please file on Github.

Sent from my iPhone

On Oct 24, 2017, at 10:35 PM, Johan Haleby <johan....@gmail.com> wrote:

tis 24 okt. 2017 kl. 16:57 skrev John A. De Goes <jo...@degoes.net>:

Try one more thing: `SELECT p.data AS data …`

Single columns without aliases are treated equivalent to SELECT VALUE, which creates problems for MongoDB that end in a map/reduce.

John

Thanks for the suggestion but this gives me:

<AA4024C6-6599-42DF-8A0B-54EB81D53297.png>

Johan Haleby

unread,
Oct 27, 2017, 4:51:51 AM10/27/17
to John A. De Goes, Douglas Duncan, Jeff Carr, SlamData-User
Which project? Quasar?

John A. De Goes

unread,
Oct 27, 2017, 7:30:42 AM10/27/17
to Johan Haleby, Douglas Duncan, Jeff Carr, SlamData-User
I think so. You could confirm in the REPL. 

Sent from my iPhone

Jeff Carr

unread,
Oct 27, 2017, 8:57:29 AM10/27/17
to Johan Haleby, John A. De Goes, Douglas Duncan, SlamData-User
correct, Quasar.

JC

Which project? Quasar?

We're using 3.4.9

What version of Mongo?


To unsubscribe from this group and all its topics, send an email to slamdata-user+unsubscribe@googlegroups.com.

-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-user+unsubscribe@googlegroups.com.



-- 
Jeff A. Carr
Co-Founder & CEO



-- 
You received this message because you are subscribed to the Google Groups "SlamData-User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to slamdata-user+unsubscribe@googlegroups.com.
--
Sent from my phone
--
Sent from my phone
--
Sent from my phone
Reply all
Reply to author
Forward
0 new messages