Proper use of json_table in mariadb json

It actually seems that MariaDB systems do NOT support the JSON_TABLE function supported in MySQL Servers...

Still, my host claims his "MySQL Version" to be "10.3-MariaDB", so I'm somewhat confused; I was told they have the same functionalities, and looks like I was misinformed? From what I've read online, MariaDB seems to be a re-implementation of MySQL, which improves a lot of MySQL Server's features, but lacks some of them, as for example the json_table function. Is that correct, or am I misunderstanding anything here, and I should indeed be able to use the json_table function?

The JSON_TABLE feature was added in MariaDB 10.6.0.

The error reported for your SQL is due to the missing type for the value. The field definition should be:

COLUMNS (word TEXT PATH '$.first')

Additionally, if you want to inspect only a single column, you should use the parent object instead of matching each child object in the path expression:

SELECT * FROM data_table t1, JSON_TABLE(
  t1.words, '$.123456' COLUMNS (word TEXT PATH '$.first')
) AS jt;

+------------------------------------------------------------------------------+-------+
| words                                                                        | word  |
+------------------------------------------------------------------------------+-------+
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | hello |
+------------------------------------------------------------------------------+-------+

If you want to select all members and filter based on the content in the WHERE clause, you can use the following form:

SELECT * FROM data_table t1, JSON_TABLE(
  t1.words, '$.123456.*' COLUMNS (word TEXT PATH '$')
) AS jt;

+------------------------------------------------------------------------------+-------+
| words                                                                        | word  |
+------------------------------------------------------------------------------+-------+
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | hello |
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | there |
+------------------------------------------------------------------------------+-------+

The $ path expression refers to the value itself and it is a useful for passing the JSON input as the output in JSON form.

If you want to extract the values for all objects that have the field first, use $.*.first as the path expression:

SELECT * FROM data_table t1, JSON_TABLE(
  t1.words, '$.*.first' COLUMNS (word TEXT PATH '$')
) AS jt;

+------------------------------------------------------------------------------+-----------+
| words                                                                        | word      |
+------------------------------------------------------------------------------+-----------+
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | hello     |
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | All good? |
+------------------------------------------------------------------------------+-----------+

As seen on your case, MariaDB has no support for JSON_TABLE() function. A workaround might be defined through generating rows along with number of keys within the json data by using JSON_KEYS() and JSON_LENGTH() functions recursively within a query having an auxiliary table such as from information_schema such as information_schema.tables, and use JSON_QUERY() with indexes(CONCAT('$.',ID)) to determine the values for the elements first, then JSON_EXTRACT() in order to extract the desired part, and lastly JSON_UNQUOTE() to get rid of double-quotes :

WITH t1 AS
(
SELECT @i := @i + 1 AS rn, 
       JSON_UNQUOTE( JSON_EXTRACT( JSON_KEYS(jsdata),CONCAT('$[',@i,']')) ) AS ID,
       jsdata
  FROM information_schema.tables AS t1 
 CROSS JOIN (SELECT @i := -1, t.* FROM t) AS t2 -- t is your original table
 WHERE @i < JSON_LENGTH( JSON_KEYS(jsdata) ) - 1
) 
SELECT t1.ID, 
       JSON_UNQUOTE( JSON_EXTRACT( JSON_QUERY(jsdata, CONCAT('$.',ID)) ,"$.first") ) AS word
  FROM t1

Demo

JSON_TABLE was introduced in MariaDB 10.6.

See documentation and examples at https://mariadb.com/kb/en/json_table/