Search Records - SurrealTools/Documentation GitHub Wiki

-- objects
UPDATE person:tobie SET my.embedded.object.field = "Test";
SELECT my.embedded.object.field AS my_field FROM person:tobie; -- returns "Test"
-- or arrays
UPDATE person:tobie SET my.embedded.tags = [], my.embedded.tags += "Golang", my.embedded.tags += "Rust";
SELECT my.embedded.tags[1] FROM person:tobie; -- returns "Rust";

SELECT * FROM person:1;
-- and not
SELECT * FROM person WHERE id = person:1;

SELECT  name, (SELECT id, name FROM $parent.friends) AS friends FROM person;

SELECT * FROM test WHERE active == false; // returns all records with active = false

SELECT <option<datetime>> date FROM appointments;

# That will only use a single extra read, which will have negligible impact on performance!
SELECT * FROM users:983fjka98434.. LIMIT 10 START 1;

to ensure that "sessions" is always an array, no matter the existance of any sessions:
SELECT ->attends[where status = "confirmed"]->session ?? [] as sessions FROM $client;

SELECT address.active = true FROM person;

-- An array filter, followed by an array index operation
SELECT tags[WHERE value CONTAINS 'gmail'][0] FROM user;

-- Selecting an object value or array index using a string as a key
SELECT language['en-gb'] AS content FROM documentation;
-- Updating an object value or array index using a string as a key
UPDATE documentation:test SET language['en-gb'] = 'my english text';

LET $lang = 'en-gb';
-- Selecting an object value or array index using a parameter as a key
SELECT language[$lang] AS content FROM documentation;
-- Updating an object value or array index using a parameter as a key
UPDATE documentation:test SET language[$lang] = 'my english text';

-- Selecting an object or array index value using the value of another document field as a key
UPDATE documentation:test SET primarylang = 'en-gb';
SELECT language[primarylang] AS content FROM documentation;

SELECT *, NONE AS field FROM posts

SELECT VALUE name FROM user

SELECT array::group(program) FROM person GROUP ALL

SELECT * FROM (SELECT *, age > 18 AS adult FROM user) WHERE adult = true;

--- $ refers to the last element in an array
SELECT *, sports[$] as sports FROM person

LET $table = "users"
SELECT * from type::table($table);

SELECT *, meta::id(id) AS id, meta::id(thing) AS thing FROM table;

-- This will return 'person'
SElECT * FROM meta::tb(person:tobie);
-- This will return 'tobie'
SElECT * FROM meta::id(person:tobie);

SELECT
  id, name, slug, description, isPrivate, categories, createdAt, updatedAt, createdBy.id, createdBy.username, <-joined<-user.id AS team.id, <-joined<-user.username AS team.username
FROM module

SELECT * FROM person:1, person:2, person:3

-- Define a table as a view which aggregates data from the reading table
DEFINE TABLE temperatures_by_month AS
  SELECT
    count() AS total,
    time::month(recorded_at) AS month,
    math::mean(temperature) AS average_temp
  FROM reading
  GROUP BY city
;

-- Add a new temperature reading with some basic attributes
CREATE reading SET
  temperature = 27.4,
  recorded_at = time::now(),
  city = 'London',
  location = (-0.118092, 51.509865)
;

-- With an array
CREATE person:['[email protected]'] SET name = 'Tobie';
-- Or with an object
CREATE person:{ email: '[email protected]' } SET name = 'Tobie';
create user_colour:['Red', user:other] content {user: user:other};
create user_colour:['Blue', user:hugh] content {user: user:hugh};

You can then do...

- With an array, like above
SELECT id[0] FROM person WHERE name = 'Tobie';
-- Or with an object, like above
SELECT id.email FROM person WHERE name = 'Tobie';

select * from user_colour:['Red', NONE]..
[{ id: user_colour:['Red', user:one], user: user:one }, { id: user_colour:['Red', user:other], user: user:other }]

CREATE data:2 CONTENT { pi: 3 };
SELECT * from data WHERE  function() { return this.pi % 2 } > 1;
SELECT *, function() { return this.pi % 2 } as mod from data:1;

-- Use the parent instance's field in a subquery
SELECT *, (SELECT * FROM events WHERE host == $parent.id) AS self_hosted FROM user;

CREATE person:1 SET test = [32, 46, 58, 31];
CREATE person:2 SET test = [55, 66];
CREATE person:3 SET test = [55, 66, 44];
SELECT id, count(test) AS total FROM person ORDER BY total;

SELECT * FROM article WHERE tags.*.value CONTAINS '';

SELECT friends[WHERE relation = 'brother'].who.name FROM person:tobie;

-- `files` is an array, so you need to use CONTAINS as `disk_id` will be an array of disk_id
SELECT id FROM invoices WHERE files.disk_id CONTAINS 'something';
-- `files` is an array, so you can use an array filter to filter the array, using $this to refer to each item
SELECT id FROM invoices WHERE files[WHERE $this.disk_id = 'something'];
-- you can also just write it with a `?` instead of a `WHERE` keyword
SELECT id FROM invoices WHERE files[? $this.disk_id = 'something'];
-- ----------
-- If you wanted to check whether any `files.disk_id` contains a particular string, and not equals, then you would HAVE to use the array filter..
SELECT id FROM invoices WHERE files[WHERE $this.disk_id CONTAINS 'something'];

CREATE temp:dataset SET data = [
    "a",
    "b",
    "c",
    "invalid",
    "invalid"
];
SELECT data[WHERE $this != "invalid"] FROM temp:dataset;

SELECT "a: " + (a || "nothing") FROM thing

SELECT *, crew.id, crew.name, crew.certifications.*.name FROM ships FETCH crew, crew.certifications;
// Without fetch
SELECT friends.*.* from users

SELECT *, path.name AS path FROM folder;

-- 1. Select more than 1 field!
-- 2. Ensure you alias the `age > 18` to a field name!
SELECT * FROM (SELECT *, age > 18 AS adult FROM user) WHERE adult = true;

SELECT * FROM person WHERE name CONTAINS "something"; -- contains
SELECT * FROM person WHERE name = /something/; -- matches regex
SELECT * FROM person WHERE email = /gmail.com$/; -- ends with gmail.com
SELECT * FROM person WHERE email ~ "GMail"; -- fuzzy matches
SELECT * FROM person WHERE emails.*.value ?= /gmail.com$/; -- any email value ends with gmail.com
SELECT * FROM person WHERE emails.*.value ?~ "GMail"; -- any email value fuzzy matches
SELECT * FROM person WHERE emails.*.value *= /gmail.com$/; -- all email values end with gmail.com
SELECT * FROM person WHERE emails.*.value *~ "GMail"; -- all email values fuzzy match

CREATE person:test SET tags = ['one', 'two', 'three', 'fourth'];
SELECT * FROM person WHERE tags CONTAINS 'one'; -- true
SELECT * FROM person WHERE tags CONTAINSNOT 'four'; -- true
SELECT * FROM person WHERE tags CONTAINSANY ['one', 'four']; -- true
SELECT * FROM person WHERE tags CONTAINSALL ['one', 'four']; -- false
SELECT * FROM person WHERE tags ?~ 'four' -- true
SELECT * FROM person WHERE tags ?~ 'for' -- true

SELECT * FROM ["one", "two", "three"] ?~ "THree"; -- true
-- But it wil also match
SELECT * FROM ["one", "two", "three"] ?~ "THr"; -- true
SELECT * FROM ["one", "two", "three"] ?= /^(?i)Three$/; -- true

SELECT * FROM user WHERE email = /(.*)@gmail.com/;
-- Using fuzzy matching
SELECT * FROM user WHERE email ~ 'gmail.com';

SELECT friends[*].who.name FROM person:tobie;
-- or
SELECT friends.*.who.name FROM person:tobie;
SELECT friends[WHERE relation = 'brother'].who.name FROM person:tobie;

SELECT email, todos[0].content, todos[0].tag.name FROM user:1;

SELECT * FROM `98d4057080a33f103b26`;
-- or
SELECT * FROM type::table('98d4057080a33f103b26');

-- Create a record with a complex ID using an array
CREATE temperature:['London', '2022-08-29T08:03:39'] SET
    location = 'London',
    date = '2022-08-29T08:03:39',
    temperature = 23.7
;
-- Create a record with a complex ID using an object
CREATE temperature:{ location: 'London', date: '2022-08-29T08:03:39' } SET
    location = 'London',
    date = '2022-08-29T08:03:39',
    temperature = 23.7
;
-- Select a specific record using a complex ID
SELECT * FROM temperature:['London', '2022-08-29T08:03:39'];

Then you can also use record ranges to query specific ranges of records...
-- Select all person records with IDs between the given range
SELECT * FROM person:1..1000;
-- Select all temperature records with IDs between the given range
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', '2022-08-29T08:09:31'];

SELECT *, NONE AS email FROM user:1;

DEFINE TABLE person_by_age AS
  SELECT math::mean(age), country FROM person GROUP BY country
;

LIVE SELECT * FROM person_by_age;

INSERT INTO post {
  author: (SELECT id FROM user WHERE name = 'Anna' LIMIT 1)
};

-- fetching multiple fields
SELECT * FROM city FETCH state, country;
-- or fetching further, from the fetched field
SELECT * FROM city FETCH state, state.districts
-- or using the select clause instead
SELECT *, state.*, country.* FROM city;
-- or fetching further in more nested records
SELECT *, state, state.districts AS districts FROM city;
-- You can specify a field on an array of objects like so
SELECT * FROM person FETCH links.*.inner_id;
-- This can also be re-written by dropping the '*', and will by default go into each item in the array
SELECT * FROM person FETCH links.inner_id;

SELECT * FROM person WHERE email !== NONE;

LET $value = [1,2,3];
SELECT * FROM $value[1];

LET $name = "Zafar"
LET $other = "sdfsdf"
UPDATE type::thing('user', $id) SET name = $name, something.else = $other;

let people = await surreal.query("SELECT * FROM article WHERE status INSIDE $status", {
  status: ["live", "draft"],
});

SELECT *, (SELECT id, username FROM $parent.createdBy) AS createdBy FROM gigs;

RETURN users:983fjka98434.*
-- Return something explicitly
RETURN person:jaime.brother.name;
RETURN person:tobie.*;
-- Return something more complex
RETURN {
    tobie: person:tobie.name,
    jaime: person:jaime.name,
};

time::round(created_at, 1d) = time::round(time::now(), 1d)

DEFINE FIELD vehicles ON TABLE person TYPE array;
DEFINE FIELD vehicles.* ON TABLE person record (car, bike);
DEFINE FIELD type ON TABLE car VALUE 'car';
DEFINE FIELD type ON TABLE bike VALUE 'bike';
SELECT vehicles[WHERE type = 'car'] FROM person;

SELECT * FROM word
  WHERE
    history.*.name -- this is an array so we need to use an operator which works for arrays of values...
    CONTAINS 'Wordie'; -- where the name field is exactly 'Wordie' case sensitive
  
  OR
  
  SELECT * FROM word
  WERE
    history.*.name -- this is an array so we need to use an operator which works for arrays of values...
    ?~ 'wordie'; -- where any name field fuzzy matches 'wordie'
  
  OR
  SELECT * FROM word
  WERE
    history.*.name -- this is an array so we need to use an operator which works for arrays of values...
    ?= /^(?i)wordie$/; -- where any name field equals a regular expression (case insensitive)
  
  SELECT *, history[WHERE name = 'Wordie'] FROM word; -- This will retrieve all words, but will filter the 'history' to only matching objects...

-- This will return 'person'
SElECT * FROM meta::tb(person:tobie);
-- This will return 'tobie'
SElECT * FROM meta::id(person:tobie);

SELECT *, string::slice(date, 1, 10) AS date FROM schedule

SELECT *, (SELECT ->posts.* FROM $parent LIMIT 3) FROM user:1;