Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
Description
Basic example:
drop table if exists t; |
create table t (id int, j text); |
insert into t values (1,'{"a":"b"}'),(2,'{"c":"d"}'); |
select * from t, json_table(t.j,'$' columns (ord for ordinality)) as jt; |
Aside ordinality values starting from a wrong value in MariaDB, note them growing in MariaDB and staying the same in MySQL:
|
bb-10.5-hf 6dfb3fab2 |
+------+-----------+------+ |
| id | j | ord |
|
+------+-----------+------+ |
| 1 | {"a":"b"} | 0 | |
| 2 | {"c":"d"} | 1 | |
+------+-----------+------+ |
2 rows in set (0.001 sec) |
|
MySQL 8.0.19 |
MySQL [test]> select * from t, json_table(t.j,'$' columns (ord for ordinality)) as jt; |
+------+-----------+------+ |
| id | j | ord |
|
+------+-----------+------+ |
| 1 | {"a":"b"} | 1 | |
| 2 | {"c":"d"} | 1 | |
+------+-----------+------+ |
2 rows in set (0.002 sec) |
Here is a more meaningful example taken from stackoverflow, with minimal changes to make it applicable to MySQL/MariaDB syntax. See the discussion there.
DROP TABLE IF EXISTS test1; |
CREATE TABLE test1 (json_data TEXT); |
INSERT INTO test1 VALUES ('[ |
{
|
"debitOverturn": "939.34",
|
"table": [
|
{
|
"debit": "",
|
"credit": "939.34"
|
},
|
{
|
"debit": "939.34",
|
"credit": ""
|
}
|
]
|
},
|
{
|
"debitOverturn": "939.34",
|
"table": [
|
{
|
"debit": "",
|
"credit": "939.34"
|
},
|
{
|
"debit": "939.34",
|
"credit": ""
|
}
|
]
|
}
|
]'); |
|
|
SELECT jt.u_lvl, |
jt.debitOverturn,
|
jt.l_lvl,
|
jt.debit,
|
jt.credit
|
FROM test1 s, |
JSON_TABLE(s.json_data,'$[*]' COLUMNS( |
u_lvl FOR ORDINALITY, |
debitOverturn VARCHAR(20) PATH '$.debitOverturn', |
NESTED PATH '$.table[*]' |
COLUMNS (
|
l_lvl FOR ORDINALITY, |
debit VARCHAR(38) PATH '$.debit', |
credit VARCHAR(38) PATH '$.credit'))) AS jt |
;
|
Note u_lvl and l_lvl columns:
|
bb-10.5-hf 6dfb3fab2 |
+-------+---------------+-------+--------+--------+ |
| u_lvl | debitOverturn | l_lvl | debit | credit |
|
+-------+---------------+-------+--------+--------+ |
| 0 | 939.3 | 0 | | 939.34 |
|
| 1 | 939.3 | 1 | 939.34 | |
|
| 2 | 939.3 | 2 | | 939.34 |
|
| 3 | 939.3 | 3 | 939.34 | |
|
+-------+---------------+-------+--------+--------+ |
4 rows in set (0.002 sec) |
Result on MySQL 8.0 matches the expected result from stackoverflow, where the expectation was confirmed to be correct:
|
MySQL 8.0.19 |
+-------+---------------+-------+--------+--------+ |
| u_lvl | debitOverturn | l_lvl | debit | credit |
|
+-------+---------------+-------+--------+--------+ |
| 1 | 939.34 | 1 | | 939.34 |
|
| 1 | 939.34 | 2 | 939.34 | |
|
| 2 | 939.34 | 1 | | 939.34 |
|
| 2 | 939.34 | 2 | 939.34 | |
|
+-------+---------------+-------+--------+--------+ |
4 rows in set (0.002 sec) |
Attachments
Issue Links
- relates to
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-
- links to
Dunno. The standard doesn't say much how this all is supposed to work. I decided to do it in simplest possible way.
Though i changed it to behave in Oracle's way.
Now it produces results like this:
MariaDB [test]> select * from t1, JSON_TABLE(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt;+------+-----------------------------------------------------------------------------+-------+------+---------+------+| id | json | js_id | a | l_js_id | b |+------+-----------------------------------------------------------------------------+-------+------+---------+------+| j1 | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] | 1 | 1 | 1 | 11 || j1 | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] | 1 | 1 | 2 | 111 || j1 | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] | 2 | 2 | 1 | 22 || j1 | [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] | 2 | 2 | 2 | 222 || j2 | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] | 1 | 3 | 1 | 11 || j2 | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] | 1 | 3 | 2 | 111 || j2 | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] | 2 | 4 | 1 | 22 || j2 | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] | 2 | 4 | 2 | 222 || j2 | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] | 3 | 5 | 1 | 22 || j2 | [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] | 3 | 5 | 2 | 222 |+------+-----------------------------------------------------------------------------+-------+------+---------+------+