MariaDB/MySQL의 다단계 JSON에서 특정 값을 쿼리하는 중

MariaDB/MySQL의 다단계 JSON에서 특정 값을 쿼리하는 중

JSON 값이 다음과 같이 저장된 MariaDB 테이블을 사용하고 있습니다.

{"nextValue":4,"1":{"text":"Item1","textDisplay":"","value":1,"isActive":0},"2":{"text":"Item2","textDisplay":"","value":2,"isActive":1},"3":{"text":"Item3","textDisplay":"","value":3,"isActive":1}}

여기서 하려고 하는 것은 항목으로서 "Item2"라는 컬럼과 다음 컬럼에 해당 JSON의 키 "value" 값이 있는 컬럼을 만들 수 있는 쿼리를 작성하는 것입니다.따라서 "Item2"가 반환되면 옆에 있는 열에 "2"가 표시됩니다.

MariaDB에서 사용할 수 있는 많은 JSON 옵션을 시도해 보았지만 어떻게 해야 할지 모르겠습니다.

MySQL 8.0에서 다음을 테스트했습니다.문서상으로는 MariaDB에 있는 것 같은 함수를 사용하고 있습니다만, 테스트할 MariaDB 인스턴스는 없습니다.

SELECT * FROM mytable;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, "2": {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, "3": {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}, "nextValue": 4} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(data, REPLACE(JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'Item2')), '.text', '')), '$.value')) AS value FROM mytable;
+-------+
| value |
+-------+
| 2     |
+-------+

이는 JSON 경로에서 문자열 치환을 수행하고 이후 JSON 기능에서 이러한 경로를 사용하는 데 의존하기 때문에 매우 취약합니다.이렇게 복잡한 SQL 스테이트먼트를 개발하고 유지하려면 고용주가 많은 엔지니어 시간을 들여야 합니다.

다른 방법으로는 MySQL 8.0으로 업그레이드하여 JSON_TABLE() 함수를 사용하여 JSON 문서를 파생 테이블에 매핑한 다음 WHERE 절의 조건을 사용할 수 있습니다.

SELECT j.* FROM mytable2, 
JSON_TABLE(mytable2.data, '$[*]' 
  COLUMNS (
    rowid FOR ORDINALITY,
    `text` VARCHAR(20) PATH '$.text',
    textDisplay TEXT PATH '$.textDisplay',
    value INT PATH '$.value',
    isActive BOOL PATH '$.isActive'
  )
) AS j

+-------+-------+-------------+-------+----------+
| rowid | text  | textDisplay | value | isActive |
+-------+-------+-------------+-------+----------+
|     1 | Item1 |             |     1 |        0 |
|     2 | Item2 |             |     2 |        1 |
|     3 | Item3 |             |     3 |        1 |
+-------+-------+-------------+-------+----------+

그러나 JSON 데이터가 JSON 개체로 포맷된 반면 JSON이 어레이인 경우에만 기능이 작동하기 때문에 JSON 데이터에서는 이 기능이 작동하지 않습니다.JSON 데이터 형식을 배열로 변경한 경우에만 위의 예를 만들었습니다.

select * from mytable2;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

개발자가 설계한 JSON 포맷은 데이터를 쉽게 삽입할 수 있도록 설계되어 있지만, JSON을 전혀 불필요하게 사용하기 때문에 데이터에 대한 문의가 불필요하게 복잡해집니다.이는 내부 플랫폼 효과의 예입니다.

내부 플랫폼 효과는 소프트웨어 설계자가 사용 중인 소프트웨어 개발 플랫폼의 복제품이 될 정도로 맞춤 가능한 시스템을 만드는 경향입니다.이는 일반적으로 비효율적이며 이러한 시스템은 안티패턴의 예로 간주되는 경우가 많습니다.

위에서 코멘트한 바와 같이 일반 SQL 테이블로 설계합니다.

CREATE TABLE Items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `text` VARCHAR(20), 
  textDisplay TEXT, 
  value INT, 
  isActive BOOL
);

는 각 컬럼에 값을 입력합니다.

INSERT INTO Items 
VALUES (1, 'Item1', '', 1, 0),
       (2, 'Item2', '', 2, 1),
       (3, 'Item3', '', 3, 1);

그런 다음 매우 간단하게 쿼리할 수 있습니다.

SELECT value FROM Items WHERE `text` = 'Item2';
+-------+
| value |
+-------+
|     2 |
+-------+

고용주는 데이터를 쿼리할 때 시간과 비용을 크게 절약할 수 있기 때문에 일반적인 방식으로 데이터를 저장하는 단순함을 선호해야 합니다.

언급URL : https://stackoverflow.com/questions/55345503/querying-in-multilevel-json-in-mariadb-mysql-for-specific-value