How to query an item from an sql object (CRATE DB)

Question

I am trying to use the new crate db. link I have a table which contains an "infrastructure" and "network" objects:

CREATE TABLE servers (infrastructure object(strict) as (
                #etc...
                os_name string, 
                #etc...
                network array(object as 
                (etc.., hosting_id string, etc... ))

I need to query th os_name from "infrastructure" and hosting_id from network array. How I do it? I tried to google to find the right syntax but had no succes. I want something like this:

SELECT * FROM servers WHERE infrastructure[os_name] = "some value"

and

SELECT * FROM servers WHERE infrastructure["network"]["hosting_id"] = "some value"

Never store multiple values in a single column. Better change your table design — juergen d, Dec 27 '16 at 15:23
Is this `MySQL` ... have never seen such syntax. may be am missing some — Rahul, Dec 27 '16 at 15:24
sorry. This is not Mysql, but it uses sql language. https://crate.io/ — Vasile, Dec 27 '16 at 15:26

score 2 · Accepted Answer · answered Dec 27 '16 at 19:49

ANY() is your friend, but be aware that it does not with arrays of objects. So you need to also consider this information. The solution below works for the latest Crate version 1.0.1:

SELECT * FROM servers 
WHERE 
  os_name = 'some value' 
AND 
  'some value' = ANY(network['hosting_id']);

How to query an item from an sql object (CRATE DB)

1 Answers1