[SQL] 어떻게 MySQL의에서 JSON 데이터를 검색하려면?

어떻게 MySQL의에서 JSON 데이터를 검색하려면?

나는 다음과 같은 테이블과 관계를 가지고있다. 나는 client_services 테이블에서 JSON 데이터를 저장하고있다. 다음과 같이 MySQL의 쿼리를 사용하여 JSON 값을 검색하는 그들의 방법이 있습니다 :

SELECT getJson("quota") as quota,
       client_id
FROM client_services
WHERE service_id = 1;     

아니면 내가 더 client_services 테이블을 정상화 할 수 있습니까?

표 서비스 :

+----+-----------------------+--------------------------------------------------------+
| id | name                  | description                                            |
+----+-----------------------+--------------------------------------------------------+
|  1 | MailBox               |                                                        |
|  2 | SMS                   |                                                        |
|  3 | FTP                   |                                                        |
+----+-----------------------+--------------------------------------------------------+

표 service_features :

+----+------------+----------------------------------+------------------------+
| id | service_id | name                             | description            |
+----+------------+----------------------------------+------------------------+
| 10 |          1 | Forwarding                       | Forward Mail           |
| 11 |          1 | Archive                          | Archive Mail           |
| 12 |          1 | WebMail                          | NULL                   |
| 13 |          1 | IMAP                             | NULL                   |
| 14 |          2 | Web SMS                          | NULL                   |
+----+------------+----------------------------------+------------------------+

표 client_services :

+-----+-----------+------------+-------------------------------------------------------------------------------------------+
| id  | client_id | service_id | service_values                                                                            |
+-----+-----------+------------+-------------------------------------------------------------------------------------------+
| 100 |      1000 |          1 |{ "quota": 100000,"free_quota":20000,"total_accounts":200,"data_transfer":1000000}         |
| 101 |      1000 |          2 |{ "quota": 200 }                                                                           |
| 102 |      1000 |          3 |{ "data_transfer":1000000}                                                                 |
| 103 |      1001 |          1 |{ "quota": 1000000,"free_quota":2000,"total_accounts":200,"data_transfer":1000000}         |
| 104 |      1001 |          2 |{ "quota": 500 }                                                                           |
| 105 |      1002 |          2 |{ "quota": 600 }                                                                           |
+-----+-----------+------------+-------------------------------------------------------------------------------------------+

표 client_feature_mappers :

+-----+-------------------+--------------------+-----------+
| id  | client_service_id | service_feature_id | client_id |
+-----+-------------------+--------------------+-----------+
|10000|                100|                 10 |       1000|
|10001|                100|                 11 |       1000|
|10002|                100|                 12 |       1000|
|10003|                100|                 13 |       1000|
|10004|                101|                 14 |       1000|
|10005|                103|                 10 |       1001|
|10006|                101|                 11 |       1001|
|10007|                101|                 12 |       1001|
|10008|                101|                 13 |       1001|
|10009|                105|                 14 |       1002|
+-----+-------------------+--------------------+-----------+

해결법

1. ==============================

   1.많은 사람들이 개인적으로 나에게이 질문을했기 때문에, 나는이 대답을 두 번째 개정을 줄 것이라고 생각했다. 다음은 SELECT, 마이그레이션 및 뷰 생성 및 라이브 SQL 바이올린 (바이올린에 대한 보장되지 가용성)로 전체 SQL을 가지고 요지이다.

   많은 사람들이 개인적으로 나에게이 질문을했기 때문에, 나는이 대답을 두 번째 개정을 줄 것이라고 생각했다. 다음은 SELECT, 마이그레이션 및 뷰 생성 및 라이브 SQL 바이올린 (바이올린에 대한 보장되지 가용성)로 전체 SQL을 가지고 요지이다.

   이 같은 :하자 당신이 테이블 (JSON_TABLE 이름)가 말한다 :

    ID   CITY        POPULATION_JSON_DATA
   -----------------------------------------------------------------------
    1    LONDON      {"male" : 2000, "female" : 3000, "other" : 600}
    2    NEW YORK    {"male" : 4000, "female" : 5000, "other" : 500}
   

   각 JSON 필드를 선택, 당신은 할 수 있습니다 :

   SELECT 
       ID, CITY,
       json_extract(POPULATION_JSON_DATA, '$.male') AS POPL_MALE,
       json_extract(POPULATION_JSON_DATA, '$.female') AS POPL_FEMALE,
       json_extract(POPULATION_JSON_DATA, '$.other') AS POPL_OTHER
   FROM JSON_TABLE;
   

   어떤 결과 :

   ID  CITY      POPL_MALE  POPL_FEMALE   POPL_OTHER 
   -----------------------------------------------------------------
   1   LONDON    2000       3000          600
   2   NEW YORK  4000       5000          500
   

   이것은 데이터의 크기와 JSON 복잡성에 따라 실행하는 데 비용이 많이 드는 작업 일 수 있습니다. 나는 그것을 사용하는 것이 좋습니다

   조심 : 당신은 큰 따옴표 (도 캐릭터 라인)로 시작하는 JSON있을 수 있습니다 :

   "{"male" : 2000, "female" : 3000, "other" : 600}"
   

   우분투에서 MySQL 5.7 및 Mac OSX 시에라와 함께 테스트.

2. ==============================

   2.당신은 JSON 문자열을 분해하는 MySQL의 기능 SUBSTRING_INDEX를 사용할 수 있습니다 :

   당신은 JSON 문자열을 분해하는 MySQL의 기능 SUBSTRING_INDEX를 사용할 수 있습니다 :

   SELECT 
   SUBSTRING_INDEX(
    SUBSTRING_INDEX(
     SUBSTRING_INDEX( service_values, 'quota', -1),
     '": ', -1),
    ' ', 1) AS quota,
   client_id
   FROM client_services
   WHERE service_id=1;
   

3. ==============================

   3.첫째로 당신은 당신의 모델이 위의 각 분야에서 단 하나의 값이 있어야 의미, 첫 번째 정규 양식에없는 것을 알고 있어야합니다. 그러나이 정의는 응용 프로그램 쿼리 처리 요구 사항에 따라 달라집니다.

   첫째로 당신은 당신의 모델이 위의 각 분야에서 단 하나의 값이 있어야 의미, 첫 번째 정규 양식에없는 것을 알고 있어야합니다. 그러나이 정의는 응용 프로그램 쿼리 처리 요구 사항에 따라 달라집니다.

   그래서, 당신이 원하는 모든 필드에 JSON 데이터를 잔뜩 넣어 다음, 그것의 미세 그대로 응용 프로그램에 반환하는 경우. 당신은 전체 JSON 데이터를 반환하고 응용 프로그램이 JSON이 원하는 속성을 선택 할 수 있습니다.

   당신은 JSON 데이터의 세부 사항을보고 귀하의 경우와 같은 쿼리 갖는 기준 또는 필드 표현이있는 경우 그러나, 그것은 확실히 더 - 이동 없습니다. 이 쿼리 복잡성과 느린 처리의 악몽이 될 것입니다.

   당신은 물론 더 정상화 완전히 당신의 테이블은 JSON 데이터 구조를 대체 할 수 있습니다. 응용 프로그램이 유연한 스키마, NOSQL 데시벨을 사용 아마도 주된 이유를 필요로하지만 당신은 MySQL과 붙어있는 경우, 두 가지 해결책이 있습니다 :

   가) 사용 MySQL은 5.6 (또는 MariaDB V. ??) NoSQL의 지원을 위해 (내가 자세히 못 봤어) http://www.computerworld.com/s/article/9236511/MySQL_5.6_tackles_NoSQL_competitors MariaDB의 동적 열 : https://kb.askmonty.org/en/dynamic-columns/

   http://backchannel.org/blog/friendfeed-schemaless-mysql : b)는 명시 적 스키마없이 사용 MySQL은, 더 확장 성 문제가없는 아주 잘 솔루션, 여기를 참조

4. ==============================

   4.

   SET @a = '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]' ;
   
   SELECT *
   FROM
   JSON_TABLE(
   @a,
   "$[*]" COLUMNS(
   xval VARCHAR(100) PATH "$.x",
   yval VARCHAR(100) PATH "$.y"
   )) AS  jt1;
   

from https://stackoverflow.com/questions/15701579/how-to-retrieve-json-data-from-mysql by cc-by-sa and MIT license