CODEWITHSUNDEEP
mysqljson
Dhananjay V.
Dhananjay V.

Reputation: 25

custom json_extract function for mysql 5.6 not working as expected

I am on mysql 5.6 so using custom function to extraxt json field. It's working fine if there is single value for each json field but failing if there are multiple.

Table extract
user_id,    cb_contactgroup
289,    [{"cb_mobile":"777777777","cb_phonefixedline":"666666666"}]
290,    [{"cb_mobile":"999999999","cb_phonefixedline":"888888888"}]
291,    [{"cb_mobile":"1111111111","cb_phonefixedline":"2222222222"},{"cb_mobile":"3333333333","cb_phonefixedline":"4444444444"}]

For user_id 291 there should be two lines but only one line showing as below.

user_id, mobile,    phonefixedline
289,    777777777,  666666666
290,    999999999,  888888888
291,    3333333333, 4444444444

I am using following function. I am not a technical, could someone help me to correct following function:

ELIMITER $$

DROP FUNCTION IF EXISTS `json_extract_c`$$

CREATE DEFINER=`root`@`%` FUNCTION `json_extract_c`(
  details TEXT,
  required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
  RETURN TRIM(
    BOTH '"' FROM SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        SUBSTRING_INDEX(
          details,
          CONCAT(
            '"',
            SUBSTRING_INDEX(required_field,'$.', - 1),
            '"'
          ),
          - 1
        ),
        '",',
        1
      ),
      ':',
      - 1
    )
  ) ;
END$$

DELIMITER ;

Post from where the query has been picked How to get values from MySQL(5.6) column if that contains json document as string - @user3631341

Upvotes: 2

Views: 2902

Answers (1)

Rok Furlan
Rok Furlan

Reputation: 49

Because i needed a JSON_EXTRACT on mysql 5.6 i wrote it by myself a copy of original function that can extract values like the native function in mysql 5.7

Link to source code

Upvotes: 1

Related Questions