Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2.6
-
None
-
MacOS
Description
I am trying to understand the correct way to use the JSON_EXTRACT function. It seems that JSON_EXTRACT is returning a string for a json integer. When I execute a SQL query from java (Hibernate 4) the result of JSON_EXTRACT is returned as a java String.
mysql -u root --column-type-info
|
set @json = json_object('status',24);
|
Query OK, 0 rows affected (0.01 sec)
|
|
select json_extract(@json,'$.status');
|
|
Field 1: `json_extract(@json,'$.status')`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: MEDIUM_BLOB
|
Collation: binary (63)
|
Length: 16777215
|
Max_length: 2
|
Decimals: 39
|
Flags: BINARY
|
+--------------------------------+
|
| json_extract(@json,'$.status') |
|
+--------------------------------+
|
| 24 |
|
+--------------------------------+
|
1 row in set (0.00 sec)
|
JSON_TYPE correctly returns INTEGER as json type
select json_type(json_extract(@json,'$.status'));
|
|
Field 1: `json_type(json_extract(@json,'$.status'))`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: VAR_STRING
|
Collation: utf8_general_ci (33)
|
Length: 12
|
Max_length: 7
|
Decimals: 39
|
Flags:
|
+-------------------------------------------+
|
| json_type(json_extract(@json,'$.status')) |
|
+-------------------------------------------+
|
| INTEGER |
|
+-------------------------------------------+
|
1 row in set (0.00 sec)
|
I tried casting the result of JSON_EXTRACT as an unsigned, and that works;
MariaDB [(none)]> select cast(json_extract(@json,'$.status') as unsigned) as status;
|
Field 1: `status`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: LONGLONG
|
Collation: binary (63)
|
Length: 21
|
Max_length: 2
|
Decimals: 0
|
Flags: UNSIGNED BINARY NUM
|
+--------+
|
| status |
|
+--------+
|
| 24 |
|
+--------+
|
1 row in set (0.00 sec)
|
but when I tried a simple case statement to cast the result of JSON_EXTRACT based on JSON_TYPE, the cast doesn't work and I'm getting the wrong result;
select case json_type(json_extract(@json,'$.status')) when 'INTEGER' then cast(json_extract(@json,'$.status') as unsigned) when 'STRING' then json_extract(@json, '$.status') end as status;
|
Field 1: `status`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: MEDIUM_BLOB
|
Collation: binary (63)
|
Length: 16777215
|
Max_length: 2
|
Decimals: 39
|
Flags: UNSIGNED BINARY
|
+--------+
|
| status |
|
+--------+
|
| 24 |
|
+--------+
|
1 row in set (0.00 sec)
|
For some reason this works;
select case json_type(json_extract(@json,'$.status')) when 'INTEGER' then cast(json_extract(@json,'$.status') as unsigned) end as status;
|
Field 1: `status`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: LONGLONG
|
Collation: binary (63)
|
Length: 21
|
Max_length: 2
|
Decimals: 0
|
Flags: UNSIGNED BINARY NUM
|
+--------+
|
| status |
|
+--------+
|
| 24 |
|
+--------+
|
1 row in set (0.00 sec)
|
Any help would be greatly appreciated, thanks.