--- title: "2: Improving the existing data | SurrealDB University" description: "Improving the existing data. A step in the SurrealDB movie database tutorial, with queries you can run." url: https://surrealdb.com/learn/movies/page-02 --- ![Course content preview](https://surrealdb.com/assets/static/header.C1-dPXT9.avif) [Back to Courses](https://surrealdb.com/learn) Course chapters [Movie database tutorial](https://surrealdb.com/learn/movies) [1: Getting started](https://surrealdb.com/learn/movies/page-01) [2: Improving the existing data](https://surrealdb.com/learn/movies/page-02) [3: SurrealQL data types](https://surrealdb.com/learn/movies/page-03) [4: Adding a schema](https://surrealdb.com/learn/movies/page-04) [5: Adding and querying relations](https://surrealdb.com/learn/movies/page-05) [6: Defining users and permissions](https://surrealdb.com/learn/movies/page-06) [All the queries from the tutorial](https://surrealdb.com/learn/movies/page-07) # 2: Improving the existing data Let's look at the other fields inside the `naive_movie` data and think about how they can be improved. ## `Actors`, `Writer`, `Director` These fields sometimes have one person, but usually you will see values such as `"Arnold Schwarzenegger, Linda Hamilton, Edward Furlong"`. ```surql { Actors: 'Arnold Schwarzenegger, Linda Hamilton, Michael Biehn', Director: 'James Cameron', Writer: 'James Cameron, Gale Anne Hurd, William Wisher', } ``` Each of these names can be used to make a separate `person` record, and each `person` should be able to be an actor, writer, director, or all three. With this, we will be able to look up someone like James Cameron and see which movies he wrote, directed, and starred in. To start though, we will call the [`string::split()`](https://surrealdb.com/docs/reference/query-language/functions/database-functions/string#stringsplit) function followed by `', '` when inserting this data, which will split the string each time it encounters a comma followed by a space to produce an array of strings. ```surql 'Arnold Schwarzenegger, Linda Hamilton, Michael Biehn'.split(', '); ``` Output ```surql ['Arnold Schwarzenegger', 'Linda Hamilton', 'Michael Biehn'] ``` ## `Released` The `Released` field currently looks like this. ```surql { Released: '03 Jul 1991' } ``` That string is readable for humans, but we can turn it into a [`datetime`](https://surrealdb.com/docs/reference/query-language/language-primitives/data-types/datetimes) instead. Datetimes are made by adding a `d` prefix in front of a string in [RFC 3339](https://datatracker.ietf.org/doc/html/rfc3339) format. ```surql d'1991-07-03'; -- YYYY-MM-DD works d'1991-07-03T00:00:00Z' -- Also the full YYYY-MM-DDTHH:MM:SSZ format; ``` Or you can cast with the type name inside angle brackets like this. ```surql '1991-07-03'; '1991-07-03T00:00:00Z' ``` The difference between the two is that a prefix like `d` tells the parser to treat the input as that type, while a cast tells the database to try to convert the value on the right to that type. So if you send in this bad input with a `` cast, a string will be sent to the server which it will try to convert to a datetime. It will fail with an error. ```surql '1991-07777-03'; -- Output: "Expected a datetime but cannot convert '1991-07777-03' into a datetime" ``` But if you use the prefix, the parser won't let the query happen in the first place. ```surql d'1991-07777-03'; -- Parser error: "There was a problem with the database: Parse error: Unexpected character `7` expected `-` --> [1:10] | 1 | d'1991-07777-03' | ^ "; ``` With a datetime you get the convenience of being able to add or subtract them (or durations) from each other. Let's see how much time was left between the original date of Terminator 2 and Judgment Day, which was supposed to happen in 29 August 1997 and thankfully never did. ```surql d'1997-08-29' - d'1991-07-03'; ``` Output ```surql 6y8w3d ``` ## `Plot` The `Plot` field can remain as a string, but we can more effectively search through it using a [full-text search](https://surrealdb.com/docs/learn/data-models/full-text-search/overview) index. Full-text search is another one of SurrealDB's built-in models that you often need to install plugins to make work in other databases. ```surql { Plot: 'A cyborg, identical to the one who failed to kill Sarah Connor, must now protect her ten-year-old son John from a more advanced and powerful cyborg.' } ``` ## Ratings The `Ratings` field is an array of objects which is a bit tricky because IMDB, Rotten Tomatoes, and Metacritic all use a different format. We will have to do this somewhat manually. [Defining a function](https://surrealdb.com/docs/reference/query-language/statements/define/function) for each will at least keep the code clean though. ```surql { Ratings: [ { Score: '8.6/10', Source: 'Internet Movie Database' }, { Score: '93%', Source: 'Rotten Tomatoes' }, { Score: '75/100', Source: 'Metacritic' } ] } ``` ## `BoxOffice` The `BoxOffice` field should be an integer. Removing the `$` and `,` from these strings will be easy enough. ```surql { BoxOffice: '$205,881,154' } ``` We will end up taking the data from the `naive_movie` records to create new `movie` records that will have all the benefits of strong typing. The actual records will be created in this sort of fashion, by using a `FOR` loop inside which each naive movie will get the parameter name `$movie` (parameters in SurrealQL start with a `$` symbol). For each field we will transform the string data into something more useful. ```surql FOR $movie IN SELECT * FROM naive_movie { CREATE movie CONTENT { runtime: // Modify $data.Runtime to fit our needs plot: // Will have an analyzer and search index defined so just pass in a string rt_rating: // Do something with $movie.Ratings to pull out the Rotten Tomatoes score and so on with the others // ... and so on ... } } ``` Those are some of the tasks before us to start tackling in the next page. Previous 1: Getting started [Previous](https://surrealdb.com/learn/movies/page-01) Next lesson 3: SurrealQL data types [Next lesson](https://surrealdb.com/learn/movies/page-03) ```json {"@context":"https://schema.org","@type":"Course","name":"Movie database tutorial","description":"Movie database tutorial","url":"https://surrealdb.com/learn/movies","inLanguage":"en","isAccessibleForFree":true,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"Movie database tutorial","url":"https://surrealdb.com/learn/movies"},{"@type":"LearningResource","name":"1: Getting started","url":"https://surrealdb.com/learn/movies/page-01"},{"@type":"LearningResource","name":"2: Improving the existing data","url":"https://surrealdb.com/learn/movies/page-02"},{"@type":"LearningResource","name":"3: SurrealQL data types","url":"https://surrealdb.com/learn/movies/page-03"},{"@type":"LearningResource","name":"4: Adding a schema","url":"https://surrealdb.com/learn/movies/page-04"},{"@type":"LearningResource","name":"5: Adding and querying relations","url":"https://surrealdb.com/learn/movies/page-05"},{"@type":"LearningResource","name":"6: Defining users and permissions","url":"https://surrealdb.com/learn/movies/page-06"},{"@type":"LearningResource","name":"All the queries from the tutorial","url":"https://surrealdb.com/learn/movies/page-07"}]} ``` ```json {"@context":"https://schema.org","@type":"LearningResource","name":"2: Improving the existing data","description":"Improving the existing data","url":"https://surrealdb.com/learn/movies/page-02","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"Movie database tutorial","url":"https://surrealdb.com/learn/movies"},"position":3} ``` ```json {"@context":"https://schema.org","@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the unified data layer for AI. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]} ``` ```json {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Page 02","item":"https://surrealdb.com/learn/movies/page-02"}]} ```