Работа с JSON данными в СУБД Mysql 5.7

Выборка из масива Тип поля в БД должен быть json

В поле хранится по типу [one,two,three,four,five]

SELECT * FROM `table_name` WHERE JSON_CONTAINS(`field`,'[value]');

В поле хранится по типу ID ТАБЛИЦЫ 1 - ["one","two","three","four","five"]

SELECT * FROM `table_name` WHERE JSON_CONTAINS(`field`,'['\"value\"']');
SELECT * FROM `table_name` WHERE JSON_CONTAINS(`field`,JSON_ARRAY('value'));
SELECT JSON_EXTRACT((SELECT `field` FROM `table_name` WHERE `id` = 1),'$[index]') as title;

Выборка из обьекта

В поле хранится по типу ID ТАБЛИЦЫ 2

{"one":1,"two":2,"three":3,"four":4,"five":5}

SELECT * FROM `table_name` WHERE JSON_CONTAINS(`field`,'{\"key\":value}');
SELECT * FROM `table_name` WHERE JSON_CONTAINS(`field`,JSON_OBJECT('value'));
SELECT * FROM `table_name` WHERE JSON_CONTAINS(`field`,JSON_OBJECT('key','value','key2','value2'));
SELECT JSON_EXTRACT((SELECT `field` FROM `table_name` WHERE `id` = 2),'$.*') as title;
SELECT JSON_EXTRACT((SELECT `field` FROM `table_name` WHERE `id` = 2),'$.key') as title;

Более сложная выборка

В поле хранится по типу ID ТАБЛИЦЫ 3

{"0":"one",
"1":{"one":1,"two":2,"five":["value1","value2"],"four":4,"three":3},
"2":["six","seven","eight"],"3":"five","key":"value"}

SELECT JSON_EXTRACT((SELECT `field` FROM `table_name` WHERE `id` = 3),'$.*.key') as title;
SELECT JSON_EXTRACT((SELECT `field` FROM `table_name` WHERE `id` = 3),'$.*.key') as title;
SELECT JSON_EXTRACT((SELECT `field` FROM `table_name` WHERE `id` = 3),'$.*.key[index]') as title;
SELECT `field`, `field2`->'$.*.key' AS `field2` FROM `table_name`;
SELECT `field`, `field2` AS `field2` FROM `table_name` WHERE `field2`->'$.*.key[index]' IS NOT NULL;

Источник для более развернутого ответа
https://webformyself.com/rabota-s-json-dannymi-v-subd-mysql-5-7/
https://www.youtube.com/watch?time_continue=1082&v=riOQT4HS7yI

Bash, Ubuntu, Apache, SQL

Написать комментарий