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 questions
Answer options
A: ALTER TABLE fshop ADD COLUMN name VARCHAR(20) AS (product- >* S .varieties. VarietyName ' ) VIRTUAL, ADD KEY idx_name (name.) ;
B: ALTER TABLE fshop ADD COLUMN name VARCHAR(100) AS (product->' S - varieties' ) VIRTUAL, ADD KEY idx_name (name) ;
C: ALTER TABLE fshop ADD COLUMN name VARCHAR(20) AS (product->' S - name' ) VIRTUAL, ADD KEY idx_name (name) ; Correct answer
D: ALTER TABLE fshop ADD COLUMN name VARCHAR(20), ADD KEY idx_name (name) ;
E: ALTER TABLE fshop ADD name VARCHAR(20) AS (JSON_ONQUOTE (product- >"S.varieties.VarietyName")), ADD INDEX (name);
Correct Answer:
C. ALTER TABLE fshop ADD COLUMN name VARCHAR(20) AS (product->' S - name' ) VIRTUAL, ADD KEY idx_name (name) ;
Explanation

To 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.

Why Incorrect

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.

References

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.).

Scroll to Top