Как искать массив JSON в MySQL?

Допустим, у меня есть столбец JSON с именем data в некоторой таблице MySQL, и этот столбец представляет собой один массив. Так, например, данные могут содержать:

[1,2,3,4,5]

Теперь я хочу выбрать все строки, в которых есть столбец данных, где один из элементов массива больше 2. Это возможно?

Я пробовал следующее, но, кажется, это всегда так, независимо от значений в массиве:

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

Ответ 1

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

ложно

Ответ 2

Возможным способом решения проблемы является сопоставление строк. Преобразуйте JSON в строку и сопоставьте.

Или вы можете использовать JSON_CONTAINS.

Ответ 3

Я не знаю, нашли ли мы решение. Я нашел с MariaDB путь, чтобы искать путь в массиве. Например, в массиве [{ "id": 1}, { "id": 2}], я хочу найти путь с id равным 2.

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

Результат:

"$[1].id"

Звездочка указывает на поиск всего массива

Ответ 4

Начиная с 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)

Ответ 5

Вы можете использовать извлечение JSON для поиска и выбора данных

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

Ответ 6

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;

result: "$[0].SongLabels[0].SongLabelName"

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

результат: ["Funk", "RnB"]