MySQL에서 JSON 데이터를 가져오는 방법
나는 다음 테이블과 그들의 관계를 가지고 있다.client_services 테이블에 JSON 데이터를 저장하고 있습니다.다음과 같이 MySQL 쿼리를 사용하여 JSON 값을 가져오는 방법입니까?
SELECT getJson("quota") as quota,
client_id
FROM client_services
WHERE service_id = 1;
또는 client_services 테이블을 더 정규화할 수 있습니까?
테이블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|
+-----+-------------------+--------------------+-----------+
많은 분들이 저에게 개인적으로 질문을 하셨기 때문에, 저는 이 답변을 2차 수정해 드리려고 합니다.SELECT, Migration 및 View Creation을 포함한 완전한 SQL과 라이브 SQL 바이올린(fiodle의 가용성은 보증되지 않음)을 포함하는 GIST를 다음에 나타냅니다.
테이블(이름:TBL_JSON)은 다음과 같습니다.
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 TBL_JSON;
그 결과:
ID CITY POPL_MALE POPL_FEMALE POPL_OTHER
-----------------------------------------------------------------
1 LONDON 2000 3000 600
2 NEW YORK 4000 5000 500
데이터 크기와 json의 복잡성에 따라서는 이 작업을 실행하는 데 많은 비용이 소요될 수 있습니다.에 사용하는 것을 추천합니다.
- 테이블을 분할 데이터베이스로 마이그레이션(요지 부록 2-B 참조)
- 적어도 뷰를 작성합니다(요지 부록 2-C 참조).
주의: 큰따옴표(문자열)로 시작하는 json:
"{"male" : 2000, "female" : 3000, "other" : 600}"
Ubuntu 및 Mac OSX Sierra에서 Mysql 5.7로 테스트 완료.
MySQL 함수 SUBSTRING_INDEX를 사용하여 JSON 문자열을 분해할 수 있습니다.
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX( service_values, 'quota', -1),
'": ', -1),
' ', 1) AS quota,
client_id
FROM client_services
WHERE service_id=1;
첫째, 위의 모델은 First Normal Form이 아닙니다.즉, 각 필드에 하나의 값만 있어야 합니다.그러나 이 정의는 응용 프로그램 조회 처리 요구 사항에 따라 달라집니다.
따라서 JSON 데이터를 여러 개 필드에 저장하고 그대로 애플리케이션에 반환하기만 하면 됩니다.전체 JSON 데이터를 반환하고 응용 프로그램이 원하는 JSON 속성을 선택하도록 할 수 있습니다.
그러나 JSON 데이터의 세부사항을 조사하는 기준이나 필드 표현식을 가진 문의가 있는 경우라면 당연히 금지입니다.질의의 복잡성과 처리의 지연은 악몽이 될 것입니다.
물론 테이블을 더욱 정규화하여 JSON 데이터 구조를 완전히 대체할 수 있습니다.다만, 애플리케이션에 유연한 스키마가 필요한 경우(NOSQL DB를 사용하는 주된 이유일 수 있지만 MySQL을 사용할 수 없는 경우)에는, 다음의 2개의 솔루션이 있습니다.
a) 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/
b) 명시적인 스키마 없이 mysql을 사용합니다.확장성의 문제가 없는 매우 뛰어난 솔루션에 대해서는, 여기를 참조해 주세요.http://backchannel.org/blog/friendfeed-schemaless-mysql
네, MySQL 쿼리(JSON_EXTract() 함수 사용)를 사용하여 JSON 값을 확실하게 가져올 수 있습니다.
있는 .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 } |
+-----+-----------+------------+-------------------------------------------------------------------------------------------+
각 JSON 필드를 선택하려면 다음 쿼리를 수행합니다.
SELECT
id, client_id, service_id,
json_extract(service_values, '$.quota') AS quota,
json_extract(service_values, '$.free_quota') AS free_quota,
json_extract(service_values, '$.total_accounts') AS total_accounts,
json_extract(service_values, '$.data_transfer') AS data_transfer
FROM client_services;
따라서 출력은 다음과 같습니다.
+-----+-----------+------------+-----------------------------------------------------+
| id | client_id | service_id | quota | free_quota | total_accounts | data_transfer|
+-----+-----------+------------+-----------------------------------------------------+
| 100 | 1000 | 1 | 100000 | 20000 | 200 | 1000000 |
| 101 | 1000 | 2 | 200 | null | null | null |
| 102 | 1000 | 3 | null | null | null | 1000000 |
| 103 | 1001 | 1 | 100000 | 2000 | 200 | 1000000 |
| 104 | 1001 | 2 | 500 | null | null | null |
| 105 | 1002 | 2 | 600 | null | null | null |
+-----+-----------+------------+-----------------------------------------------------+
이게 도움이 됐으면 좋겠네요!
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;
여러 수준의 json 문자열이 있는 경우(예: {names:{'filename': abc, 'firstname': def}}, 그래도 json_filename 함수를 사용할 수 있습니다.
json_module(field_name, '$names')을 선택합니다.성' ;
언급URL : https://stackoverflow.com/questions/15701579/how-to-retrieve-json-data-from-mysql
'programing' 카테고리의 다른 글
| 리액트 래퍼:React가 DOM 요소에서 staticContext 프로펠을 인식하지 않습니다. (0) | 2023.03.16 |
|---|---|
| Oracle SQL에서 타임스탬프를 최신 상태로 변환 (0) | 2023.03.16 |
| React-Redux 응용 프로그램의 유틸리티 기능을 어디에 배치해야 합니까? (0) | 2023.03.16 |
| Jackson을 사용하여 JSON에서 단일 필드 가져오기 (0) | 2023.03.16 |
| 어레이를 JSON 문자열로 빠르게 변환 (0) | 2023.03.16 |