[#MDEV-12995] JSON_EXTRACT returning string for integer

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.

[MDEV-12995] JSON_EXTRACT returning string for integer Created: 2017-06-05 Updated: 2017-06-05 Resolved: 2017-06-05
Status:	Closed
Project:	MariaDB Server
Component/s:	JSON
Affects Version/s:	10.2.6
Fix Version/s:	N/A

[MDEV-12995] JSON_EXTRACT returning string for integer Created: 2017-06-05 Updated: 2017-06-05 Resolved: 2017-06-05