BlueXIII's Blog

热爱技术,持续学习

0%

Posted on

MySQL5.6JSON解析

新建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

DELIMITER $$
DROP function if exists json_extract_c$$
CREATE FUNCTION json_extract_c(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET utf8mb4
DETERMINISTIC
NO SQL
BEGIN
  DECLARE search_term, val TEXT;
  DECLARE pos INT signed DEFAULT 1;

  -- Remove '{' and '}'
  SET details = SUBSTRING_INDEX(details, "{", -1);
  SET details = SUBSTRING_INDEX(details, "}", 1);
  -- Transform '$.xx' to be '"xx"'
  SET search_term = CONCAT('"', SUBSTRING_INDEX(required_field,'$.', - 1), '"');

  searching: LOOP
    SET pos = LOCATE(search_term, details);
    -- Keep searching if the field contains escape chars
    WHILE pos > 0 AND RIGHT(LEFT(details, pos-1), 1) = '\\'
    DO
      SET details = SUBSTR(details, pos+LENGTH(search_term));
      SET pos = LOCATE(search_term, details);
    END WHILE;
    -- Return NULL if not found
    IF pos <= 0 THEN
      RETURN NULL;
    END IF;

    SET pos = LENGTH(search_term)+pos;
    SET details = SUBSTR(details, pos);
    SET val = TRIM(details);

    -- see if we reach the value that is a leading colon ':'
    IF LEFT(val, 1) = ':' THEN
      RETURN TRIM(
        TRAILING ',' FROM 
        TRIM(
          SUBSTRING_INDEX(
            TRIM(
              BOTH '"' FROM TRIM(
                SUBSTR(
                  val
                , 2
                )
              )
            )
          , '"', 1
          )
        )
      );
    ELSE
      ITERATE searching;
    END IF;
  END LOOP;
END$$
DELIMITER ;

查询

1
2
3
4
5
6
SELECT offer_id
     , terms
     , json_extract_c(terms, 'qty') as offer_qty  
     , json_extract_c(terms, 'discount') as offer_discount
     , json_extract_c(terms, 'total') as offer_total
FROM offer;