Cannot join 2 buckets by id ("syntax error - at WHERE")

SQL++
,
1

Hello,

I have two buckets (kernel information and a dump of LSHW ) with the same id but somehow I cannot do a simple join.

For example, I can get the kernel and host by doing the following:

SELECT kernel.id, kernel.kernel FROM `kernel` ORDER BY id

And get something like this:

[
  {
    "id": "host1.com",
    "kernel": "Linux host1.com 4.19.82-1300.XX.XXX.x86_64 #1 SMP Fri Nov 8 10:49:58 EST 2019 x86_64 x86_64 x86_64 GNU/Linux"
  },
  {
    "id": "host2.com",
    "kernel": "Linux host2.com 4.19.82-1300.XX.XXX.x86_64 #1 SMP Fri Nov 8 10:49:58 EST 2019 x86_64 x86_64 x86_64 GNU/Linux"
  },
...
]

And the other bucket, LSHW I can get ONLY the details I want like this:

SELECT id,vendor,product,serial, ARRAY child.logicalname FOR child IN children WHEN child.id LIKE 'network%%' AND child.disabled IS MISSING END AS network,  ARRAY {child.id, child.configuration.cores, child.configuration.enabledcores, child.units, child.size} FOR child IN children[0].children WHEN child.class == 'processor' END AS cpus,  ARRAY {child.id, child.units, child.size} FOR child IN children[0].children WHEN child.id == 'memory' END AS memory FROM `lshw`

And the resulting JSON:

[
  {
    "cpus": [
      {
        "cores": "8",
        "enabledcores": "8",
        "id": "cpu:0",
        "size": 3200000000,
        "units": "Hz"
      },
      {
        "cores": "8",
        "enabledcores": "8",
        "id": "cpu:1",
        "size": 3200000000,
        "units": "Hz"
      }
    ],
    "id": "host1.com",
    "memory": [
      {
        "id": "memory",
        "size": 137438953472,
        "units": "bytes"
      }
    ],
    "network": [
      "eth0",
      "bcm0",
      "eth1"
    ],
    "product": "ZZZZZ (YYYY)",
    "serial": "AAAA",
    "vendor": "MyVendor"
  },
...
]

So far so good. So I say I want to include the kernel version as part of the other LSHW selected attributes, but the following doesn’t seem to work:

SELECT
  lshw.id,
  lshw.vendor,
  lshw.product,
  lshw.serial, 
  ARRAY child.logicalname FOR child IN children WHEN child.id LIKE 'network%%' AND child.disabled IS MISSING END AS network,  
  ARRAY {child.id, child.configuration.cores, child.configuration.enabledcores, child.units, child.size} FOR child IN children[0].children WHEN child.class == 'processor' END AS cpus,  
  ARRAY {child.id, child.units, child.size} FOR child IN children[0].children WHEN child.id == 'memory' END AS memory,
  kernel.kernel
FROM 
  `lshw`
INNER JOIN
  `kernel`
WHERE
  kernel.id == lshw.id

I went through the documentation but obviously I’m missing something basic.

2

Hi @jnunez ,

SELECT
  lshw.id,
  lshw.vendor,
  lshw.product,
  lshw.serial, 
  ARRAY child.logicalname FOR child IN children WHEN child.id LIKE 'network%%' AND child.disabled IS MISSING END AS network,  
  ARRAY {child.id, child.configuration.cores, child.configuration.enabledcores, child.units, child.size} FOR child IN children[0].children WHEN child.class == 'processor' END AS cpus,  
  ARRAY {child.id, child.units, child.size} FOR child IN children[0].children WHEN child.id == 'memory' END AS memory,
  kernel.kernel
FROM `lshw`
INNER JOIN `kernel`  ON  kernel.id == lshw.id

required ON clause, As it is INNER JOIN you can change WHERE to ON

https://index-advisor.couchbase.com/indexadvisor/#1

3

Hello @vsr1 , you were faster than me. Yes that works!

I had to add an index for the kernel.id field to make it work:

CREATE INDEX ix_kernel_id ON kernel(id)

Thanks for the help.

–Jose