You now become the one responsible for data integrity. When you opt-out of the strict schema provided by a relational database, it means that the onus of enforcement falls upon the developer. With great flexibility comes great responsibility. Which means, we'll get back all 4 rows if we change payload->'$.value' to payload->'$.value', which is a short-hand syntax for the JSON_UNQUOTE(JSON_EXTRACT()) combination. value property as a String.ĪSIDE: As we saw in the previous section, MySQL will fall-back to coercing values on-the-fly if we "unwrap" the JSON values. We're missing a row - the one in which someone accidentally stored the. value property is 1, we get the following COUNT(*): However, when we go to query for the rows in which the. SELECT JSON_OBJECT( 'value', 1 ) AS payloadĪs you can see, we have 4 rows that contain a. SELECT JSON_OBJECT( 'value', '1' ) AS payload UNION ALL - OOPS!!!! SELECT JSON_OBJECT( 'value', 1 ) AS payload UNION ALL To see what I mean, let's look at this SQL which accidentally commingles a String value amongst a bunch of Numbers: Any mess-up and it means you're not going to get the expected query results. In other words, it's up to you and your application layer to make sure all inputs to a JSON structure are consistent. Beyond ensuring that said payload is, in fact, valid JSON (JavaScript Object Notation), the database makes no judgments about what is actually in said JSON payload. A JSON data structure - in MySQL 5.7.32 - does not make any such guarantees. Which means, if you have an INT column, your input is going to be stored as a number regardless of how you provide said value ( 1 vs "1"). One of the splendiferous features of a relational database table is that is enforces a schema at the storage level. However, what we can see is that when we "unwrap" the JSON value using JSON_UNQUOTE(), MySQL will fall-back to coercing strings and numbers on-the-fly. Meaning, in the context of a JSON object, 1 and "1" are not the equal. When comparing an input to a JSON value, MySQL does not coerce the operands. Then, we can just run a number of comparisons against String and Number values:Īs you can see, everything came back as TRUE except these two comparisons: To test, we can create an in-memory JSON value using JSON_OBJECT(). Before digging into value comparisons, it wasn't clear to me how all of this would come together. The latter of these two methods will remove the quotes ( "") from an extracted String, which may or may not change the actual data-type of the resolved value. When it comes to the JSON data structures in MySQL 5.7, you can "extract" a value using JSON_EXTRACT() (or ->) and then, you can "unquote" an extracted value using JSON_UNQUOTE() (or ->). The most critical of which is that type coercion will bypass index selection during query planning which may result in a full-table scan. This got me thinking about the new JSON support in MySQL 5.7: will simple values get coerced when comparing an input to a JSON path?ĬAUTION: While MySQL will happily coerce values on-the-fly during a comparison, note that this does have implications. However, document databases like MongoDB are much less lenient and will not cast values on-the-fly. Meaning, from a SQL execution standpoint, 1 and "1" are equal because the SQL engine will coerce the values as needed (much like ColdFusion). Generally speaking, SQL is pretty lenient when it comes to simple types and value comparisons.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |