Search code examples
sqlcrate

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

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"
Solution

  • 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']);