MySQL 5.7 получил новый нативный тип данных JSON и набор SQL функций для работы с ним. Сегодня мы продемонстрируем несколько простых примеров этих возможностей с испльзованием данных SF OpenData.

Не пропустите еще одну статью по этой теме - Использование JSON в MySQL.

Импортируем данные

Важно использовать качественные тестовые данные, потому что так проще проверять точны ли результаты запросов. Также они имеют хорошую структуру, что важно при добавлении индексов.

Выбранный нами набор данных из SF OpenData содержит около 200 тысяч позиций. Сначала скачаем их и импортируем в базу.

cd /tmp
curl -O https://raw.githubusercontent.com/zemirco/sf-city-lots-json/master/citylots.json
grep "^{ \"type" citylots.json > features.json

CREATE TABLE features (
 id INT NOT NULL AUTO_INCREMENT,
 feature JSON NOT NULL,
 PRIMARY KEY (id)
);
 
LOAD DATA INFILE '/tmp/features.json' INTO TABLE features (feature);

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

{
   "type":"Feature",
   "geometry":{
      "type":"Polygon",
      "coordinates":[
         [
            [-122.42200352825247,37.80848009696725,0],
            [-122.42207601332528,37.808835019815085,0],
            [-122.42110217434865,37.808803534992904,0],
            [-122.42106256906727,37.80860105681814,0],
            [-122.42200352825247,37.80848009696725,0]
         ]
      ]
   },
   "properties":{
      "TO_ST":"0",
      "BLKLOT":"0001001",
      "STREET":"UNKNOWN",
      "FROM_ST":"0",
      "LOT_NUM":"001",
      "ST_TYPE":null,
      "ODD_EVEN":"E",
      "BLOCK_NUM":"0001",
      "MAPBLKLOT":"0001001"
   }
}

В нашем случае все 200 тысяч документов имеют один формат, но это необязательно, так как JSON в MySQL - schema-less.

Примеры запросов

Запрос 1: Найдем участок земли, занимаемый улицей с названием "Market"

SELECT * FROM features 
WHERE feature->"$.properties.STREET" = 'MARKET' 
LIMIT 1\G

************************* 1. row *************************
     id: 12250
feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
1 row in set (0.02 sec)

С помощью оператора-сокращения для JSON_EXTRACT (->) мы можем делать запросы к JSON столбцам удобным способом. Синтаксис "$.properties.STREET" чем то похож на CSS селекторы, которые используются в jQuery. Узнать больше о синтаксисе JSON запросов можно из документации

Запрос 2: Найдем все участки земли, где не указана улица:

SELECT * FROM features  
WHERE feature->"$.properties.STREET" IS NULL 
LIMIT 1\G
Empty set (0.39 sec)

Так как JSON в MySQL - schemaless, он находит все документы, не имеющие требуемой структуры. В этом примере мы видим что у всех документов определено свойство $.properties.STREET, поэтому запрос возвращает нулевой результат.

Сравнение типов JSON и TEXT

В этом примере мы выполним запрос, который обработает все 200 тысяч JSON документов. Это будет что-то вроде небольшого теста производительности, но без использования индексов.

# как JSON тип
SELECT DISTINCT
 feature->"$.type" as json_extract
FROM features;
+--------------+
| json_extract |
+--------------+
| "Feature"    |
+--------------+
1 row in set (1.25 sec)
 
# как TEXT тип
ALTER TABLE features CHANGE feature feature LONGTEXT NOT NULL;
SELECT DISTINCT
 feature->"$.type" as json_extract
FROM features;
+--------------+
| json_extract |
+--------------+
| "Feature"    |
+--------------+ 
1 row in set (12.85 sec)

Что мы здесь сделали:

• Для упрощения мы убедились что в обоих примерах данные помещаются в памяти.
• Фуркции для работы с JSON, включая алиас (->) для json_extract() работают одинаково как для JSON типа данных, так и для TEXT/BLOB/VARCHAR типов. Это очень полезно для пользователей кто только обновился до MySQL 5.7, но уже хранит данные в JSON.
• Мы видим что JSON тип примерно в 10 раз быстрее чем TEXT тип - 1.25 секунд против 12.85. Это объясняется тем, что нативный тип JSON не требует никакого разбора или проверки данных, и можно эффективно строить запросы к JSON документам.

Поддержку JSON документов уже добавили в query builder Laravel'а. И в будущем поддержка скорее всего появится и в других фреймворках.

Если не нашли ответов на свои вопросы можете посмотреть в другой статье - Использование JSON в MySQL.