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"…

Question

Options

• AALTER TABLE fshop ADD COLUMN name VARCHAR(20) AS (product- >* S .varieties.
• BALTER TABLE fshop ADD COLUMN name VARCHAR(100) AS (product->' S - varieties' )
• CALTER TABLE fshop ADD COLUMN name VARCHAR(20) AS (product->' S - name' ) VIRTUAL,
• DALTER TABLE fshop ADD COLUMN name VARCHAR(20), ADD KEY idx_name (name) ;
• EALTER TABLE fshop ADD name VARCHAR(20) AS (JSON_ONQUOTE

• A
  3% (1)
• B
  66% (25)
• C
  11% (4)
• D
  18% (7)
• E
  3% (1)

Explanation

Option B correctly adds a generated column that extracts the name key from the JSON document using MySQL's -> (or ->>) operator with the proper JSON path syntax ($.name), and with a sufficient VARCHAR(100) length - this generated column can then be indexed, enabling efficient lookups over the name key.

Why the distractors fail:

• A targets the varieties path, not name, and the syntax is malformed - it would not extract the right field.
• C declares the column as VIRTUAL but omits creating an actual index on it, so the column exists but documents are not indexed.
• D adds a plain VARCHAR column with an index, but it is not a generated column - it has no expression to extract data from the JSON field, so it stays empty and indexes nothing meaningful.
• E is syntactically incomplete and uses a non-existent function (JSON_ONQUOTE instead of JSON_UNQUOTE), making it invalid.

Memory tip: For JSON indexing in MySQL, remember the three-part recipe - Extract → Store → Index: use ->> (double arrow = unquoted string) with a $.key path to create a generated column, size it generously, then ensure an index is added. If any one of those three parts is missing, the data isn't truly indexed.

No community discussion yet for this question.