5

If my row has a varchar with the following value: "[1,2,3]", how could I select the whole row if I need the value to contain one of the numbers (a 2 or a 3)?

John Derring
  • 515
  • 1
  • 4
  • 15
  • 1
    You could use a regular expression. But the best solution would be to normalize your table design so you don't have values like that in the first place. – Barmar Nov 06 '16 at 20:43
  • Checkout my other [answer](https://stackoverflow.com/questions/37816269/how-to-get-values-from-mysql5-6-column-if-that-contains-json-document-as-strin/#answer-69127222). It will help – user3733831 Sep 10 '21 at 04:26

3 Answers3

5

As you said, you're using MySQL 5.6, so the JSON_EXTRACT() function is not available.

The only options I would recommend are:

  1. Upgrade to MySQL 5.7 or later

  2. Fetch the whole JSON document into an application and use a JSON decoding function.

You could also try to parse JSON using other string functions available in MySQL 5.6, but it'll be awkward and time-consuming to develop.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

You could create the function in the sql 

DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_c`$$

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

DELIMITER ;
BM2ilabs
  • 522
  • 6
  • 11
0

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

Community
  • 1
  • 1
Rok Furlan
  • 49
  • 4