Clojurians Log v2

LENGTH should do it?

e.g. https://play.xtdb.com/?version=2.0.0-SNAPSHOT&type=sql&txs=W3sic3lzdGVtLXRpbWUiOm51bGwsInR4cyI6IklOU0VSVCBJTlRPIHBhcnRuZXJzIChfaWQsIG5hbWUsIHJlZnMpIFZBTFVFUyAoMSwgJ2EnLCBbJ2JhciddKTtcbklOU0VSVCBJTlRPIHBhcnRuZXJzIChfaWQsIG5hbWUsIHJlZnMpIFZBTFVFUyAoMiwgJ2InLCBbJ2JhcicsICdiYXonXSk7XG5JTlNFUlQgSU5UTyBwYXJ0bmVycyAoX2lkLCBuYW1lLCByZWZzKSBWQUxVRVMgKDMsICdjJywgW10pOyJ9XQ%3D%3D&query=U0VMRUNUIG5hbWUsIExFTkdUSChyZWZzKSBhcyByZWZjb3VudApGUk9NIHBhcnRuZXJzCldIRVJFIHJlZnMgPD4gW10K

Works great, thanks!

Where can I look up this kind of thing?

I tried the Calcite docs but that’s either the wrong place, or I looked in the wrong place in those docs

there's a couple of docs - one is https://docs.xtdb.com/reference/main/sql/queries.html of everything you can do in XT2 SQL; the other is our https://docs.xtdb.com/reference/main/stdlib.html, with more details about each of the functions

but more generally, we try to keep to the SQL spec as much as possible, so if there's a standard SQL function for it, chances are we either have it already, or it's something we'll strongly consider adding

IIRC the SQL standard function is CARDINALITY(coll), which we support

I tried the Calcite docswe're actually not using Calcite at all in XT2, instead it's a bespoke planner + optimizer written in Clojure 🙂 https://github.com/xtdb/xtdb/blob/main/core/src/main/clojure/xtdb/sql/plan.clj

Aha! Makes sense. Thanks y’all!

👋 Any tips how to get this working?

select name, case cardinality(location_refs) when [] then 0 else cardinality(location_refs) end as location_count
from partners
limit 10

[
    {
        "name": "ý񕑊ïV"
    },
    {
        "name": "\u00167µZ󳨻É\u001b{Î=",
        "location_count": 8
    },
    {
        "name": "9f"
    }
]

what are you trying to do, sorry?

for each document/record/entity (😬) return the cardinality of the array location_refs or 0 if the array is empty.

I'm assuming cardinality(location_refs) returns null if it's given an empty array...?

hmm, seems to return 0

or is the issue that location_refs may be null?

I get this:

select name, cardinality(location_refs) as location_count
from partners
limit 3

[
    {
        "name": "ý񕑊ïV"
    },
    {
        "name": "\u00167µZ󳨻É\u001b{Î=",
        "location_count": 8
    },
    {
        "name": "9f"
    }
]

so I suppose I need to use coalesce maybe

aha - in which case ... yep, coalesce is your friend here 🙂

yup that works

thanks!

BTW those two links you shared above look useful, but neither seems to include the text length or cardinality — should I look in the source code for the planner to see what’s supported?

I guess maybe I need to find the docs on working with collections in SQL. I tried to find them but no luck so far.

aah, apologies, will ensure it's included - entirely possible we've missed some 🤦‍♂️

ah, no worries, makes sense, it’s a WIP. Thank you!

e.g. something like:

select name, array_count(refs) as refcount
from partners
where refs <> []
group by name