json-table-for-ordinality.sql

create table bookclub (id int, jcol text);
insert into bookclub 
values (111,
'{
   "Name" : "John Smith",
   "address" : { "streetAddress": "21 2nd Street", 
                 "city": "New York",
                 "state" : "NY",
                 "postalCode" : 10021 
                },
   "phoneNumber" : [ 
     { "type" : "home", "number" : "212 555-1234" },
     { "type" : "fax", "number" : "646 555-4567" } 
   ],
   "books" : [ 
      { "title" : "The Talisman",
        "authorList" : [ "Stephen King", "Peter Straub" ],
        "category" : [ "SciFi", "Novel" ] 
      },
      { "title" : "Far from the Madding Crowd",
        "authorList" : [ "Thomas Hardy" ],
        "category" : [ "Novel" ]
      }
   ]
}');


insert into bookclub values(222,
'{
  "Name" : "Peter Walker",
  "address" : { "streetAddress": "111 Main Street",
                "city": "San Jose",
                "state" : "CA",
                "postalCode" : 95111 },
  "phoneNumber" : [
     { "type" : "home", "number" : "408 555-9876" },
     { "type" : "office", "number" : "650 555-2468" } 
  ],
  "books" : [ 
    { "title":"Good Omens",
      "authorList" : [ "Neil Gaiman", "Terry Pratchett" ],
      "category" : [ "Fantasy", "Novel" ] 
    },
    { "title" : "Smoke and Mirrors",
      "authorList" : [ "Neil Gaiman" ],
      "category" : ["Novel"] 
    } 
  ] 
}');

insert into bookclub values(333,
'{ "Name" : "James Lee" }');


MariaDB:
SELECT jt.rowseq, jt.name, jt.zip
FROM bookclub,
JSON_TABLE(bookclub.jcol, "lax $"
COLUMNS ( rowSeq FOR ORDINALITY,
name VARCHAR(30) PATH 'lax $.Name',
zip CHAR(5) PATH 'lax $.address.postalCode'
)
) AS jt

+--------+---------+------+
| rowseq | name    | zip  |
+--------+---------+------+
|      0 | John Sm | 1    |
|      1 | Peter W | 9    |
|      2 | James L | NULL |
+--------+---------+------+
3 rows in set (5.67 sec)



MySQL-8:

SELECT 
  jt.rowseq, jt.name, jt.zip 
FROM 
  bookclub, 
  JSON_TABLE(bookclub.jcol, "$" COLUMNS ( rowSeq FOR ORDINALITY, 
                                          name VARCHAR(30) PATH '$.Name', 
                                          zip CHAR(5) PATH '$.address.postalCode' )
            ) AS jt;

+--------+--------------+-------+
| rowseq | name         | zip   |
+--------+--------------+-------+
|      1 | John Smith   | 10021 |
|      1 | Peter Walker | 95111 |
|      1 | James Lee    | NULL  |
+--------+--------------+-------+