DEFINE-ing an Array of Objects

[KeatonTech]

For Schema-full databases, I have a pretty good idea of how to define schemas for a table. Eg. for an object like this:

struct MyStruct {
  thing: string,
  value: number,
}

I would call:

DEFINE TABLE MyTable SCHEMAFULL;
DEFINE FIELD MyTable.thing ON MyTable TYPE String;
DEFINE FIELD MyTable.value ON MyTable TYPE Number;

But, what if I wanted each row in the table to have an array of objects. Eg, a table for a struct like:

struct MyOuterStruct {
  values: Vec<MyStruct>
}

I understand that this is sort of an odd way of using the database. A better idea would be to have one table for MyStruct and another table for MyOuterStruct, and connect them with relations or with an array of references. Still, surrealdb supports this feature – at least in SCHEMALESS mode. Is there a syntax to accomplish the same thing in SCHEMAFULL mode?

[marcbinz]

Hey @KeatonTech 👋

I had the same issue when it comes to defining array fields. I could not find any reference in the docs or anywhere else on the internet, only your discussion here. So in the end I basically "solved" (if it is the correct way) it by trial and error. Here is what I figured out so far:

All approaches like array(string) (similar to how record links are defined), array<string> and similar ways do not work. What I did in the end was to first define a field just as array and then define all its values via my_field.* notation. Example (based on yours):

DEFINE TABLE MyTable SCHEMAFULL;
DEFINE FIELD thing ON MyTable TYPE array;
DEFINE FIELD thing.* ON MyTable TYPE string;

This restricts the field thing to only be an array with string values. Other values are automatically converted.

So in the end I do not know whether this is the "correct" or intended way of defining it. What I know is that for now this approach works for me. I hope this is of any help to you or anybody else trying to solve the same problem. If anyone knows whether this is the intended way or if there is a "better" way to handle it, please let me know! 🙂

[player8472]

Since this didn't really help me at all - I will add an actual answer as to how you can define nested objects:
Here an array of well defined objects:

1. Define a field of type array
2. Define field.* to be type object
3. Now you can define the fields of the individual objects

define field overviews on table Companies type array;
define field overviews.* on table Companies type object;
define field overviews..language on table Companies type string;
define field overviews..text on table Companies type string;

[ebauger]

@KeatonTech

I experienced this same kind of experience for defining an array<float, 1536> for a field.

Here the solution for my use case:

--DEFINE FIELD embedding ON email TYPE array<float, 1536>; -- always empty array []
DEFINE FIELD embedding ON email TYPE array ASSERT (array::len($value) = 1536) || (array::len($value) = 0); 
DEFINE FIELD embedding.* ON email TYPE float;

Note :
The documentations where I don't understand well when using array<> or .*
https://surrealdb.com/docs/surrealql/datamodel/overview
https://surrealdb.com/docs/surrealql/statements/define/field

[SamuelEarl]

I ran into this same issue, so I will post my solution with the hope that it helps someone else. The documentation for this solution is found here: https://surrealdb.com/learn/fundamentals/schemafull/define-fields

Let's say that you want to model a table for a user's contact info with columns like this:

{
  firstName: "John",
  lastName: "Doe",
  email: "[email protected]",
  addresses: [
    {
      street: "123 Main",
      city: "Anytown",
      state: "AB",
      zip: "12345",
      residence: {
        type: "primary",
        yearsOwned: 5
      }
    },
    {
      street: "987 Broadway",
      city: "Sometown",
      state: "ZY",
      zip: "09876",
      residence: {
        type: "vacation",
        yearsOwned: 2
      }
    }
  ]
}

This is how you would define the table and the fields:

-- -----------------------------
-- TABLE: ContactInfo
-- -----------------------------
DEFINE TABLE ContactInfo SCHEMAFULL;

DEFINE FIELD firstName ON TABLE ContactInfo TYPE string;
DEFINE FIELD lastName ON TABLE ContactInfo TYPE string;
DEFINE FIELD email ON TABLE ContactInfo TYPE string;
-- Define the `addresses` field as `TYPE array<object>`.
DEFINE FIELD addresses ON TABLE ContactInfo TYPE array<object>;
-- Define each field within an `address` object using the `addresses.*.objectFieldName` syntax.
DEFINE FIELD addresses.*.street ON TABLE ContactInfo TYPE string;
DEFINE FIELD addresses.*.city ON TABLE ContactInfo TYPE string;
DEFINE FIELD addresses.*.state ON TABLE ContactInfo TYPE string;
DEFINE FIELD addresses.*.zip ON TABLE ContactInfo TYPE string;
-- Define nested `residence` objects as `TYPE object` before you define the fields of a `residence` object.
DEFINE FIELD addresses.*.residence ON TABLE ContactInfo TYPE object;
-- Define the fields of a nested `residence` object using the dot operator.
DEFINE FIELD addresses.*.residence.type ON TABLE ContactInfo TYPE string;
DEFINE FIELD addresses.*.residence.yearsOwned ON TABLE ContactInfo TYPE int;