Proper use of json_table in mariadb json

Question

I want to transform a json column called words inside a database table called data_table into a table. The content of the column is:

{"123456":{"first":"hello","second":"there"},
 "78910":{"first":"All good?"}
}

I want to reach sth like this output:

ID     | word     |
-------+----------|
123456 |hello     |
-------+----------|
78910  |All good? |

I tried this:

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

But it returns a #1064 mysql syntax error. Where is the error???

COMPLETE ERROR CODE (translated from german):

Error in the SQL-Syntax. Please consult the manual for the correct syntax near '( t1.words, '$.123456.*' COLUMNS (word PATH '$.first')) AS jt LIMIT 0, 25' in line 2

I moreover noticed that the basic example of this page:

SELECT *
FROM
JSON_TABLE(
    '[ {"c1": null} ]',
    '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
) as jt;

Also returns a #1064 syntax error (translated from german):

#1064 - There's an error in your SQL-Syntax. Please consult the manual for the correct syntax near '(
    '[ {"c1": null} ]',
    '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON E...' on line 3

What's wrong??? Btw, my host says that my mysql version is 10.3-MariaDB..

UPDATE

No matter what I try here, I always get a #1064 mysql syntax error, telling me to correct my syntax near {whatever comes after JSON_TABLE( in my statement}. What is wrong??

According to this, is JSON_TABLE actually not even available for me??

Answer 1

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? |
+------------------------------------------------------------------------------+-----------+

Answer 2

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?