MySQL에서 JSON 어레이를 검색하는 방법

MySQL에서 JSON 어레이를 검색하는 방법

일부 MySQL 테이블에 data라는 이름의 JSON 열이 있으며 이 열은 단일 배열이라고 가정합니다.예를 들어, 데이터에는 다음이 포함됩니다.

[1,2,3,4,5]

이제 배열 요소 중 하나가 2보다 큰 데이터 열이 있는 모든 행을 선택합니다.이게 가능합니까?

다음을 시도했지만 배열의 값에 관계없이 항상 참인 것 같습니다.

SELECT * from my_table
WHERE JSON_EXTRACT(data, '$[*]') > 2;

다음과 같이 정수 배열을 검색할 수 있습니다.

  JSON_CONTAINS('[1,2,3,4,5]','7','$') Returns: 0
  JSON_CONTAINS('[1,2,3,4,5]','1','$') Returns: 1

다음과 같이 문자열 배열을 검색할 수 있습니다.

  JSON_CONTAINS('["a","2","c","4","x"]','"x"','$') Returns: 1
  JSON_CONTAINS('["1","2","3","4","5"]','"7"','$') Returns: 0

주의: JSON_CONTENS는 1 또는 0을 반환합니다.

이 경우 다음과 같은 쿼리를 사용하여 검색할 수 있습니다.

SELECT * from my_table
WHERE JSON_CONTAINS(data, '2', '$');

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2") is not null 

맞다

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "6") is not null

거짓이다

MySQL 8부터 JSON_TABLE이라는 새로운 기능이 있습니다.

CREATE TABLE my_table (id INT, data JSON);

INSERT INTO my_table VALUES 
  (1, "[1,2,3,4,5]"), 
  (2, "[0,1,2]"), 
  (3, "[3,4,-10]"), 
  (4, "[-1,-2,0]");

SELECT DISTINCT my_table.* 
FROM my_table, JSON_TABLE(data, "$[*]" COLUMNS(nr INT PATH '$')) as ids 
WHERE ids.nr > 2;

+------+-----------------+
| id   | data            |
+------+-----------------+
|    1 | [1, 2, 3, 4, 5] |
|    3 | [3, 4, -10]     |
+------+-----------------+
2 rows in set (0.00 sec)

JSON_EXTRACT와 JSON_CONTENS(MariaDB)의 조합을 사용하고 있습니다.

SELECT * FROM table WHERE JSON_CONTAINS(JSON_EXTRACT(json_field, '$[*].id'), 11, '$');

우리가 해결책을 찾았는지 모르겠다.MariaDB를 사용하여 배열 내의 경로를 검색하는 방법을 찾았습니다.예를 들어 배열에서[{"id":1}, {"id":2}]id가 2인 경로를 찾습니다.

SELECT JSON_SEARCH('name_field', 'one', 2, null, '$[*].id')
FROM name_table

결과는 다음과 같습니다.

"$[1].id"

아스타리스크는 어레이 전체를 검색함을 나타냅니다.

가능한 방법은 이 문제를 문자열 매칭으로 처리하는 것입니다.JSON을 문자열로 변환하여 일치시킵니다.

또는 JSON_CONTENS를 사용할 수 있습니다.

이 예는 mysql에서 사용할 수 있습니다.5.7위에

SET @j = '{"a": [ "8428341ffffffff", "8428343ffffffff", "8428345ffffffff", "8428347ffffffff","8428349ffffffff", "842834bffffffff", "842834dffffffff"], "b": 2, "c": {"d": 4}}';
select JSON_CONTAINS(JSON_EXTRACT(@j , '$.a'),'"8428341ffffffff"','$') => returns 1
             

에 대해 통지하다."검색어 주변,'"8428341ffffffff"'

JSON 추출을 사용하여 데이터를 검색하고 선택할 수 있습니다.

SELECT data, data->"$.id" as selectdata
FROM table
WHERE JSON_EXTRACT(data, "$.id") = '123'
#ORDER BY c->"$.name";
limit 10 ;

SET @doc = '[{"SongLabels": [{"SongLabelId": "111", "SongLabelName": "Funk"}, {"SongLabelId": "222", "SongLabelName": "RnB"}], "SongLabelCategoryId": "test11", "SongLabelCategoryName": "曲风"}]';
SELECT *,  JSON_SEARCH(@doc, 'one', '%un%', null, '$[*].SongLabels[*].SongLabelName')FROM t_music_song_label_relation;

결과: "$[0]Song Labels [ 0 ]Song Label Name"

SELECT song_label_content->'$[*].SongLabels[*].SongLabelName' FROM t_music_song_label_relation;

결과: ['펑크', 'RnB']

유사한 문제가 있습니다. 기능을 통해 검색하십시오.

create function searchGT(threshold int, d JSON)
returns int
    begin
        set @i = 0;
        while @i < json_length(d) do
            if json_extract(d, CONCAT('$[', @i, ']')) > threshold then
                return json_extract(d, CONCAT('$[', @i, ']'));
            end if;
            set @i = @i + 1;
        end while;
        return null;
    end;

select searchGT(3, CAST('[1,10,20]' AS JSON));

언급URL : https://stackoverflow.com/questions/36249828/how-to-search-json-array-in-mysql