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