Official example of `CREATE JSON DUALITY VIEW` gives syntax error

Question

According to the release note of MySQL 9.4, JSON duality views are now supported.

27.7.1 JSON Duality View Syntax shows this example:

CREATE TABLE customers (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(25),
 last_name VARCHAR(25),
 street VARCHAR(50),
 city VARCHAR(25),
 state CHAR(2),
 postcode CHAR(5),
 phone CHAR(10),
 email VARCHAR(50)
);
CREATE JSON DUALITY VIEW jdv_customers
 AS
 SELECT JSON_DUALITY_OBJECT( {
 '_id' : c.id,
 'name' : { 'fname' : c.first_name,
 'lname' : c.last_name },
 'email' : c.email,
 'area' : { 'city' : c.city,
 'state' : c.state }
 }
 )
FROM customers c;

Let me test that:

> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 9.4.0 |
+-----------+

> CREATE TABLE customers (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> first_name VARCHAR(25),
-> last_name VARCHAR(25),
-> street VARCHAR(50),
-> city VARCHAR(25),
-> state CHAR(2),
-> postcode CHAR(5),
-> phone CHAR(10),
-> email VARCHAR(50)
-> );
Query OK, 0 rows affected

> CREATE JSON DUALITY VIEW jdv_customers
-> AS
-> SELECT JSON_DUALITY_OBJECT( {
-> '_id' : c.id,
-> 'name' : { 'fname' : c.first_name,
-> 'lname' : c.last_name },
-> 'email' : c.email,
-> 'area' : { 'city' : c.city,
-> 'state' : c.state }
-> }
-> )
-> FROM customers c;
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{\n '_id' : c.id,\n 'name' : { 'fname' : c.first_' at line 3")

Why?

Is this just a documentation bug or am I missing something?

---

Edit:

Reading the documentation of JSON_DUALITY_OBJECT(), it seems

• {} of JSON_DUALITY_OBJECT( { ... } ) found in the example is incorrect. We must omit that.

• Nested object { ... } found in the example is incorrect. Instead we have to specify a subquery like this:

  'name' : (
   SELECT JSON_DUALITY_OBJECT(
   /* ... */
   )
   FROM customers AS c2
   WHERE c2.id = c.id
  ),
  

• And the documentation also says

  If the table is projected multiple times, the set of columns projected must be consistent across all instances of the table projection.

  , meaning that top-level query and all of the subqueries must repeat the same set of columns.

Following these rules gives:

CREATE JSON DUALITY VIEW jdv_customers AS
SELECT JSON_DUALITY_OBJECT(
 '_id' : c.id,
 'first_name' : c.first_name,
 'last_name' : c.last_name,
 'email' : c.email,
 'city' : c.city,
 'state' : c.state,
 'name' : (
 SELECT JSON_DUALITY_OBJECT(
 'id' : c2.id, 
 'first_name' : c2.first_name,
 'last_name' : c2.last_name,
 'email' : c2.email,
 'city' : c2.city,
 'state' : c2.state
 )
 FROM customers AS c2
 WHERE c2.id = c.id
 ),
 'area' : (
 SELECT JSON_DUALITY_OBJECT(
 'id' : c3.id,
 'first_name' : c3.first_name,
 'last_name' : c3.last_name,
 'email' : c3.email,
 'city' : c3.city,
 'state' : c3.state
 )
 FROM customers AS c3
 WHERE c3.id = c.id
 )
)
FROM customers AS c;

This actually works (no syntax error) but is far more verbose then the official example.

I even doubt if this is really an intended use-case of JSON duality view. Probably the intended use-case is not to project a single flat row to a nested JSON object but to directly map an inherently nested structure (e.g. that given by JOIN) to a JSON object without re-structuring.

• Bill Karwin: I also tried looking up the WorkLog documents referenced in the release notes. None of them are publicly viewable. I suspect this JSON duality view feature, and many other recent features, reflect Oracle's priority to drive customers to their MySQL Enterprise and Heatwave paid products.
• Bill Karwin: I suggest you file a bug at bugs.mysql.com and ask for documentation to be correct and include proper examples.
• ynn: @BillKarwin Thank you! Reading your suggestion, I tried to file a bug but finally gave up; I don't think it is reasonable that I have to create an Oracle account by entering name, job title, work phone, company name, zip code, city and address (they are all required fields) just to file a bug... I hope they will improve the docs in some future. I also found JSON duality views are not very useful without "updatable" feature, which is available only in Enterprise edition, because then we can reproduce it via JSON functions such as JSON_ARRAYAGG() with a normal view (or one-off SELECT).
• Bill Karwin: I would assume even in MySQL Enterprise, you can't make an updatable view with JSON_ARRAYAGG() because any view with aggregation conflicts with the rules for updatable views. If it can't reverse the aggregation deterministically to tell which row(s) an aggregated expression affects, then it can't update.