Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12995

JSON_EXTRACT returning string for integer

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2.6
    • N/A
    • JSON
    • 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.

      Attachments

        Activity

          People

            serg Sergei Golubchik
            rhheisterberg Richard Heisterberg
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.