Easy EdgeDB · Chapter 6

Still no escape

UpdatingFiltering On InsertJson

This chapter picks up right where the last one left off:

The women vampires are next to Jonathan and he can’t move. Suddenly, Dracula runs into the room and tells the women to leave. He yells: “You can have him later, but not tonight!” The women listen to him, and leave.

Jonathan doesn’t remember anything else about that night. He wakes up in his bed the next day and it feels like a bad dream…but he sees that somebody folded his clothes, and he knows it was not just a dream.

The castle has some visitors from Slovakia the next day, which gives Jonathan an idea. He writes two letters, one to Mina and one to his manager at work. He gives the visitors some money, asks them to send the letters, and runs back to his room. But Dracula finds the letters and is angry. He burns the letters in front of Jonathan and tells him not to do that again. Jonathan is still stuck in the castle, and Dracula knows that Jonathan tried to trick him.

There is not much new in this lesson when it comes to types, so let’s work on our schema and inserts a bit. Right now Jonathan Harker is still inserted like this:

Copy
insert NPC {
  name := 'Jonathan Harker',
  places_visited := City,
};

This was fine when we only had cities, but now we have the Place and Country types. First we’ll insert two more Country types to have some more variety:

Copy
insert Country { name := 'France' };
insert Country { name := 'Slovakia' };

(In Chapter 9 we’ll learn how to do this with just one insert!)

Then we’ll make two new types called Castle and OtherPlace. Castle will be used for castles and castle towns, and OtherPlace for any other kind of place. They are super easy to make:

Copy
type Castle extending Place;
type OtherPlace extending Place;

Put that into the schema and do a migration, and now we can insert our first Castle:

Copy
insert Castle {
  name := 'Castle Dracula'
};

We will insert some OtherPlace objects later on in the book. Now we have a good number of types from Place that aren’t of the City type.

Now let’s get back to Jonathan. In our database, he’s been to four cities, one country, and one Castle…but he hasn’t been to Slovakia or France, so we can’t just insert him with places_visited := select Place. Instead, we can filter on Place against a set with the names of the places he has visited. If we were inserting Jonathan Harker for the first time, it would look like this:

Copy
insert NPC {
  name := 'Jonathan Harker',
  places_visited := (
    select Place filter .name in {'Munich', 'Buda-Pesth', 'Bistritz', 
    'London', 'Romania', 'Castle Dracula'}
  )
};

You’ll notice that we just wrote the names in a set using {}, so we didn’t need to use an array with [] to do it. (This is called a {ref}`set constructor <docs:ref_eql_set_constructor>`, by the way.)

But we already have a Jonathan Harker in the database. We could always do a quick delete NPC filter .name = 'Jonathan Harker' before doing this insert, but that’s not ideal. Instead, we should do an update. For that we have the update and set keywords. The update keyword selects the type to start the update, and set is used to specify the parts that we want to change. We’ll also want to use filter to make sure that we are only updating the NPC object named Jonathan Harker, and not every single NPC object in the database.

So let’s update Jonathan with this code instead:

Copy
update NPC 
filter .name = 'Jonathan Harker'
set {
  places_visited := (
    select Place filter .name in 
    {'Munich', 'Buda-Pesth', 'Bistritz', 'London', 'Romania', 'Castle Dracula'}
  )
};

Now what if Jonathan ever escapes Castle Dracula and runs away to a new place? Let’s pretend that the PC named Emil Sinclair changed the flow of the story by saving Jonathan and taking him away to Slovakia. In that case, we could select the Place object and use += to add it to Jonathan’s places_visited property:

Copy
update NPC
filter .name = 'Jonathan Harker'
set {
  places_visited += (select Place filter .name = 'Slovakia')
};

And to undo this change, you can just change += to -= and run the command again. Let’s do that, because Jonathan Harker doesn’t ever actually end up visiting Slovakia.

EdgeDB will return the IDs of the objects that have been updated. In our case, it’s just one:

{default::NPC {id: ca4e21c8-014f-11ec-9658-7f88bf45dae6}}

However, this doesn’t mean that places_visited has been changed. If we had written something like filter .name = 'SLLLovakia' then the set portion of the update would have simply added an empty {} set to places_visited, because nothing matched the filter there. The returned default::NPC simply means that an NPC object was found that matched filter .name = 'Jonathan Harker, and that something was done to it. But in this case, the something was the addition of an empty set to an existing set, so nothing at all.

One quick way to ensure that places_visited is actually being updated with something is to use the assert_exists function. This function will pass on the output if it exists, but give an error if the output is an empty set. Here is the same update with the incorrect name ‘SLLLovakia’, which will now give an error:

Copy
update NPC
filter .name = 'Jonathan Harker'
set {
  places_visited += assert_exists((select Place filter .name = 'SLLovakia'))
};

Here is the output now:

edgedb error: CardinalityViolationError: assert_exists violation:
expression returned an empty set

With that we now know all three operators used after set: :=, +=, and -=.

Let’s do another update. Remember the lover link on the Person type? Let’s take a look at Jonathan and see how his love life is doing.

Copy
select Person {
  name,
  lover
} filter .name = 'Jonathan Harker';

Here’s the output:

{default::NPC {name: 'Jonathan Harker', lover: {}}}

Ah, that’s right. Mina Murray has Jonathan Harker as her lover, but Jonathan doesn’t have her as his lover because we inserted him first before Mina Murray was inserted. We can change that now.

This command seems like it will work, but it doesn’t quite. Do you remember why?

Copy
update Person filter .name = 'Jonathan Harker'
set {
  lover := assert_single(
    (select Person filter .name = 'Mina Murray')
  )
};

No error is generated, but let’s look at Jonathan Harker after the update:

Copy
select Person { name, lover } filter .name = 'Jonathan Harker';

Surprisingly, the output is {default::NPC {name: 'Jonathan Harker', lover: {}}}!

After a bit of thought, we remember that we learned the detached keyword in Chapter 4 when inserting Mina. At the time we got the following error when we trie to insert Mina without using detached:

error: QueryError: invalid reference to default::NPC: 
self-referencing INSERTs are not allowed
  ┌─ <query>:3:20
  │
3 │   lover := (select NPC filter .name = 'Jonathan Harker'),
  │                    ^^^ Use DETACHED if you meant to refer 
  to an uncorrelated default::NPC set

However, this time an error wasn’t generated because we are doing an update, not an insert. Let’s investigate exactly what happens here when detached doesn’t get used.

First we’ll do a quick select to see what’s going on. We’ll select Jonathan Harker and also add a computed lover := (select Person filter .name = 'Mina Murray') inside the shape to see what shows up:

Copy
select Person {
 name,
 lover := (select Person filter .name = 'Mina Murray')
 } filter .name = 'Jonathan Harker';

Again, the output is {default::NPC {name: 'Jonathan Harker', lover: {}}}. That in itself is a hint that something didn’t work properly. Let’s try another select. This time we will simply make lover into a (select Person {name}) to see everything that shows up before the filter. The query now looks like this:

Copy
select Person {
 name,
 lover := (select Person {name})
 } filter .name = 'Jonathan Harker';

This time, the output is very interesting: {default::NPC {name: 'Jonathan Harker', lover: default::NPC {name: 'Jonathan Harker'}}}. This output proves that the select Person inside this query effectively means to select the Person object or objects that have already been selected! And we can’t find the Person object called Mina if our set of Person objects only includes Jonathan Harker.

We can see the same behaviour if we remove the filter:

Copy
select Person {
  name,
  lover_name := (select Person.name)
 };

There it is again: each time we use select without detached we are simply selecting the Person object in question, and not a full set of all the Person objects in the database.

{
  default::MinorVampire {name: 'Vampire Woman 1', lover_name: 'Vampire Woman 1'},
  default::MinorVampire {name: 'Vampire Woman 2', lover_name: 'Vampire Woman 2'},
  default::MinorVampire {name: 'Vampire Woman 3', lover_name: 'Vampire Woman 3'},
  default::NPC {name: 'The innkeeper', lover_name: 'The innkeeper'},
  default::NPC {name: 'Mina Murray', lover_name: 'Mina Murray'},
  default::NPC {name: 'Jonathan Harker', lover_name: 'Jonathan Harker'},
  default::Vampire {name: 'Count Dracula', lover_name: 'Count Dracula'},
  default::PC {name: 'Emil Sinclair', lover_name: 'Emil Sinclair'},
}

So now let’s do the update properly with the detached keyword so that Jonathan can finally be connected to Mina. (After all, he has enough to worry about without needing to think about this too.)

Copy
update Person filter .name = 'Jonathan Harker'
set {
  lover := assert_single(
    (select detached Person filter .name = 'Mina Murray')
  )
};

And now a select query to make sure that it worked:

Copy
select Person {
  name, 
  lover: {name}
  } filter .name = 'Jonathan Harker';

The output is now as follows:

{default::NPC {name: 'Jonathan Harker', lover: default::NPC {name: 'Mina Murray'}}}

Success!

Now, if you use update without filter it will do the same change on all the types. This update below for example would give every Person type every single Place in the database under places_visited:

Copy
update Person
set {
  places_visited := Place
};

One operator we haven’t seen before is ++, which does concatenation (joining together) instead of adding.

You can do simple operations with it like: select 'My name is ' ++ 'Jonathan Harker'; which gives {'My name is Jonathan Harker'}. Or you can do more complicated concatenations as long as you continue to join strings to strings:

Copy
select 'A character from the book: ' ++ (select NPC.name) 
       ++ ', who is not ' ++ (select Vampire.name);

This prints:

{
  'A character from the book: The innkeeper, who is not Count Dracula',
  'A character from the book: Mina Murray, who is not Count Dracula',
  'A character from the book: Jonathan Harker, who is not Count Dracula',
}

The concatenation operator works on arrays too, putting them into a single array. So the output of:

Copy
select ['I', 'am'] ++ ['Jonathan', 'Harker'];

Will be:

{['I', 'am', 'Jonathan', 'Harker']}

The last type that the concatenation operator works on is bytes.

What do we do if we want to see output in JSON? It couldn’t be easier: just cast using <json>. Any type in EdgeDB can be cast to JSON this easily:

Copy
# <json> is the only difference from the select above
select <json>Vampire {
  name,
  slaves: {name}
};

This will transform the results into JSON. However, what the REPL will show by default looks more like this:

{
  Json("{\"name\": \"Count Dracula\", \"slaves\": [{\"name\": \"Vampire Woman 1\"}, {\"name\": \"Vampire Woman 2\"}, {\"name\": \"Vampire Woman 3\"}]}"),
}

Let’s go through the result together. The outer curly braces are just telling you that what’s inside is one or more results returned by the query. Then the actual result is a string containing JSON. Because the JSON part is inside a string all the " there need to be escaped, so they appear as \".

That’s a great JSON output for computers to handle, but it’s a little bit ugly for us. Fortunately we can change this: to make the REPL show JSON in a nicer format just type \set output-format json-pretty. Then the results will look more familiar:

Copy
{
  "name": "Count Dracula",
  "slaves": [{"name": "Vampire Woman 1"}, {"name": "Vampire Woman 2"}, {"name": "Vampire Woman 3"}]
}

Now to get back to the default format, we can type \set output-format default. To keep things easy to read, this book will show JSON output using this json-pretty output format.

So what about the other way around, namely JSON to an EdgeDB type? You can do this too, but remember to think about the JSON type that you are giving to cast. The EdgeDB philosophy is that casts should be symmetrical: a type cast into JSON should only be cast back into that type. For example, here is the first date in the book Dracula as a string, then cast to JSON and then into a cal::local_date:

Copy
select <cal::local_date><json>'18930503';

This is fine because <json> turns it into a JSON string, and cal::local_date can be created from a string. The result we get is {<cal::local_date>'1893-05-03'}. But if we try to turn the JSON value into an int64, it won’t work:

Copy
select <int64><json>'18930503';

The problem is that it is a conversion from a JSON string to an EdgeDB int64. It gives this error: edgedb error: InvalidValueError: expected JSON number or null; got JSON string. To keep things symmetrical, you need to cast a JSON string to an EdgeDB str and then cast into an int64:

Copy
select <int64><str><json>'18930503';

Now it works: we get {18930503} which began as an EdgeDB str, turned into a JSON string, then back into an EdgeDB str, and finally was cast into an int64.

The documentation on JSON explains which JSON types turn into which EdgeDB types, lists functions for working with JSON values, and is good to bookmark if you need to convert from JSON a lot. Here is a simplified explanation from the documentation:

  • JSON strings can be cast to a str. Casting uuid and date/time types to JSON returns a JSON string. You can cast back into those types, as long as the formatting is correct.

  • JSON numbers can be cast to any numeric type.

  • JSON booleans can be cast to a bool type.

  • JSON null is unique because it can be cast to an empty set ({}) of any type.

  • JSON arrays can be cast to any valid array type, but its items must be all the same time, the array cannot contain null, and it can’t contain another array.

One quick way to turn JSON into a more usable form is to use the json_object_unpack() function, which returns a set of tuple<str, json>.

We will learn a lot more about tuples in Chapter 10, but for now just remember that they use () parentheses, can contain different types, and that you access their items by using a dot. So this query will return {10}:

Copy
select ('Jonathan Harker', 10).1;

Now let’s look at the json_object_unpack() function with a simple query:

Copy
with json_dracula := <json>(select Vampire {*}),
  select json_object_unpack(json_dracula);

The output will look as follows:

{
  ('id', Json("\"3bc8e902-19d9-11ee-92bf-b3c5cf277bc7\"")),
  ('age', Json("800")),
  ('name', Json("\"Count Dracula\"")),
  ('is_single', Json("true")),
}

Change the splat operator from * to ** and it gets even more verbose!

{
  ('id', Json("\"3bc8e902-19d9-11ee-92bf-b3c5cf277bc7\"")),
  ('age', Json("800")),
  ('name', Json("\"Count Dracula\"")),
  ('lover', Json("null")),
  (
    'slaves',
    Json("[
      {\"id\": \"3bc8f1b8-19d9-11ee-92bf-1bd2fcb94b84\", \"name\": \"Vampire Woman 1\", \"is_single\": true}, 
      {\"id\": \"3bc8fc08-19d9-11ee-92bf-0f167ba34818\", \"name\": \"Vampire Woman 2\", \"is_single\": true}, 
      {\"id\": \"3bc8fcee-19d9-11ee-92bf-cfa75bcb2757\", \"name\": \"Vampire Woman 3\", \"is_single\": true}]"),
  ),
  ('is_single', Json("true")),
  ('places_visited', Json("[]")),
}

There are quite a few other functions related to JSON in the documentation, so do take a look if you need to work with JSON in some other way. Some of the functions include json_array_unpack(), json_get(), json_set(), json_typeof(), and json_object_pack().

Here is all our code so far up to Chapter 6.

Practice Time
  1. This select is incomplete. How would you complete it so that it says “Pleased to meet you. I’m ” and then the NPC’s name followed by a period?

    Copy
    select NPC {
      name,
      greeting := ## Put the rest here
    };
    
    Show answer
  2. How would you update Mina’s places_visited to include Romania if she went to Castle Dracula for a visit?

    Show answer
  3. With the set {'W', 'J', 'C'}, how would you display all the Person types with a name that contains any of these capital letters?

    Hint: it involves with and a bit of concatenation.

    Show answer
  4. How would you display this same query as JSON?

    Show answer
  5. How would you add ‘ the Great’ to every Person type?

    Bonus question: what’s a quick way to undo this using string indexing?

    Show answer

Up next: Jonathan climbs the castle wall to get into the Count’s room.

We use ChatGPT with additional context from our documentation to answer your questions. Not all answers will be accurate. Please join our Discord if you need more help.