1Z0-909 practice question 4 of 15
Examine this statement which executes successfully: CREATE TABLE 'fshop’ ('product' JSON DEFAULT NULL ) ENGINE=InnoDB; Now, examine a json value contained in the table: {"name" : "orange", "varieties" : [{"VarietyName":"Clementine", "Origin" : ["PA", "BU"] }, {"VarietyName": "tangerine", "Origin" : ["CH","JP"]>]> Which will cause documents to be indexed over the 'name' key?
Full set: 65 questionsTo make the server maintain an index whose contents come from the name attribute inside the JSON document, you must
1. Derive the scalar value stored in $.name from the JSON column by means of a generated column, and
2. Create a secondary index on that generated column.
Option C defines a virtual generated column name whose expression JSONEXTRACT-equivalent (product->'$.name') returns the value of the root-level key name for every row; the subsequent ADD KEY statement builds the desired index, so every document is indexed on the value of that key.
A. Extracts $.varieties[].VarietyName, not $.name; therefore indexes the wrong attribute.
B. Extracts the entire $.varieties array, not a scalar; cannot be indexed with VARCHAR.
D. Adds a plain column; no linkage to JSON data, so the index ignores the JSON documents.
E. Uses JSONUNQUOTE but path $.varieties.VarietyName, not $.name; still indexes the wrong attribute.
1. MySQL 8.0 Reference Manual, §13.1.20.8 “Generated Columns and JSON”, Example 2 – extracting $.name and indexing it (https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html).
2. MySQL 8.0 Reference Manual, §11.5 “The JSON Data Type”, description of the -> operator and its use in generated columns (https://dev.mysql.com/doc/refman/8.0/en/json.html).
3. MySQL 8.0 Reference Manual, §8.3.5 “Secondary Indexes on Generated Columns”, esp. rules for functional indexes on JSON-derived values (ibid.).
Keep practicing
Take the free 1Z0-909 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other 1Z0-909 practice questions
- Question 3Examine these commands which execute successfully: mYsql> CREATE TABLE income (acct_num I…
- Question 5Which two statements are true about aggregate functions?
- Question 6Examine this statement and output: CREATE TABLE geom (g GEOMETRY NOT NOLL, SPATIAL INDEX(…
- Question 12You are using buffered queries with PHP mysqli in a browser-based web application. Which …
