← Cloudflare D1 / d1 / sql-api

Запрос JSON

D1 предлагает встроенную поддержку запросов и разбора данных JSON, хранящихся в базе данных. Это позволяет вам:

• Пути выполнения запросов внутри сохранённого объекта JSON, например для прямого извлечения значения по имени ключа или по индексу массива, что особенно полезно для больших объектов JSON.
• Вставляет и (или) заменяет значения в объекте или массиве.
• Развёртывание содержимого объекта JSON или массив на несколько строк, например для использования в составе WHERE ... IN предикат.
• Создайте генерируемые столбцы которые автоматически заполняются значениями из вставляемых вами объектов JSON.

Одно из главных преимуществ разбора JSON непосредственно в D1 состоит в том, что это сокращает количество обращений (запросов) к базе данных. Он уменьшает число случаев, когда вам нужно считать объект JSON в приложение (1), разобрать его и записать обратно (2).

Это позволяет точнее формировать запросы к данным и сократить набор результатов, которые приложению потребуется дополнительно разбирать и фильтровать.

Данные JSON хранятся в виде TEXT столбец в D1. Типы JSON подчиняются тем же правила преобразования типов как D1 в целом, включая:

• Значение JSON null в D1 трактуется как NULL.
• JSON-число трактуется как INTEGER или REAL.
• Значения типа boolean обрабатываются как INTEGER значения: true как 1 и false как 0.
• Значения объектов и массивов как TEXT.

В следующей таблице перечислены встроенные в D1 функции JSON и примеры их использования.

• json заполнитель аргумента может быть объектом JSON, массивом, строкой, числом или значением null.
• value аргумент принимает только строковые литералы и обрабатывает входные данные как строку, даже если это корректный JSON. Исключение из этого правила составляет вложение json_* функции: внешняя (оборачивающая) функция интерпретирует возвращаемое значение внутренней (оборачиваемой) функции как JSON.
• path аргумент принимает синтаксис навигации в стиле пути, например $ чтобы обратиться к объекту/массиву верхнего уровня, $.key1.key2 чтобы обратиться к вложенному объекту, а $.key[2] чтобы обратиться по индексу к элементу массива.

SQLite Расширение JSON ↗, на основе которого построен D1, содержит дополнительные примеры использования.

Функции JSON возвращают malformed JSON ошибку при работе с данными, которые не являются JSON и/или не являются корректным JSON. D1 считает корректным JSON RFC 7159 ↗ соответствует стандарту.

В следующем примере вызов json_extract над строкой (недопустимым JSON) приведёт к тому, что запрос вернёт malformed JSON ошибка:

SELECT json_extract('not valid JSON: just a string', '$')

Это вернёт ошибку:

ERROR 9015: SQL engine error: query error: Error code 1: SQL error or missing database (malformed
  JSON)`

поддержка D1 для генерируемые столбцы позволяет создавать динамические столбцы, значения которых формируются на основе других столбцов, включая извлечённые или вычисленные значения из JSON.

К этим столбцам можно обращаться в запросах как к любым другим, и для них можно задать индексы определённые для них. Если у вас есть данные JSON, которые вы часто запрашиваете и фильтруете, создание вычисляемого столбца и индекса может значительно повысить производительность запросов.

Например, чтобы определить столбец на основе значения из большего объекта JSON, используйте AS ключевое слово в сочетании с Функция JSON чтобы сгенерировать типизированный столбец:

CREATE TABLE some_table (
    -- other columns omitted
    raw_data TEXT -- JSON: {"measurement":{"aqi":[21,42,58],"wind_mph":"13","location":"US-NY"}}
    location AS (json_extract(raw_data, '$.measurement.location')) STORED
)

См. Генерируемые столбцы чтобы узнать больше о генерировании столбцов.

Извлечь значение из объекта JSON в D1 можно тремя способами:

• json_extract() функция, например, json_extract(text_column_containing_json, '$.path.to.value).
• -> оператор, который возвращает JSON-представление значения.
• ->> оператор, который возвращает SQL-представление значения.

-> и ->> операторы и функции работают аналогично одноимённым операторам в PostgreSQL и MySQL/MariaDB.

При следующем объекте JSON в столбце с именем sensor_reading, вы можете напрямую извлекать из него значения.

{
    "measurement": {
        "temp_f": "77.4",
        "aqi": [21, 42, 58],
        "o3": [18, 500],
        "wind_mph": "13",
        "location": "US-NY"
    }
}

-- Extract the temperature value
json_extract(sensor_reading, '$.measurement.temp_f')-- returns "77.4" as TEXT

-- Extract the maximum PM2.5 air quality reading
sensor_reading -> '$.measurement.aqi[3]' -- returns 58 as a JSON number

-- Extract the o3 (ozone) array in full
sensor_reading -\-> '$.measurement.o3' -- returns '[18, 500]' as TEXT

Длину JSON-массива можно получить двумя способами:

1. Вызывая json_array_length(value) напрямую
2. Вызывая json_array_length(value, path) чтобы указать путь к массиву внутри объекта или внешнего массива.

Например, при следующем объекте JSON, хранящемся в столбце с именем login_history, вы могли бы напрямую получить количество последних входов в систему:

{
    "user_id": "abc12345",
    "previous_logins": ["2023-03-31T21:07:14-05:00", "2023-03-28T08:21:02-05:00", "2023-03-28T05:52:11-05:00"]
}

json_array_length(login_history, '$.previous_logins') --> returns 3 as an INTEGER

Также можно использовать json_array_length в качестве предиката в более сложном запросе, например WHERE json_array_length(some_column, '$.path.to.value') >= 5.

Вставить значение в существующий JSON-объект или массив можно с помощью json_insert(). Например, если у вас есть TEXT столбец с именем login_history в users таблицу, содержащую следующий объект:

{"history": ["2023-05-13T15:13:02+00:00", "2023-05-14T07:11:22+00:00", "2023-05-15T15:03:51+00:00"]}

Чтобы добавить новую временную метку в history массив в нашем login_history столбец напишите запрос, похожий на следующий:

UPDATE users
SET login_history = json_insert(login_history, '$.history[#]', '2023-05-15T20:33:06+00:00')
WHERE user_id = 'aba0e360-1e04-41b3-91a0-1f2263e1e0fb'

Передайте три аргумента в json_insert:

1. Имя столбца, содержащего JSON, который нужно изменить.
2. Путь к ключу внутри объекта, который нужно изменить.
3. Значение JSON для вставки. Используя [#] указывает json_insert чтобы добавить элемент в конец массива.

Чтобы заменить существующее значение, используйте json_replace(), которое перезапишет существующую пару ключ-значение, если она уже есть. Чтобы задать значение независимо от того, существует ли оно уже, используйте json_set().

Используйте json_each чтобы развернуть массив в несколько строк. Это может быть полезно при составлении WHERE column IN (?) запрос по нескольким значениям. Например, если вы хотите обновить список пользователей по их целочисленному id, используйте json_each чтобы вернуть таблицу, в которой каждое значение представлено как столбец с именем value:

UPDATE users
SET last_audited = '2023-05-16T11:24:08+00:00'
WHERE id IN (SELECT value FROM json_each('[183183, 13913, 94944]'))

Это извлечёт только value столбец из таблицы, возвращаемой json_each, где каждая строка представляет идентификаторы пользователей, переданные вами в виде массива.

json_each фактически возвращает таблицу с несколькими столбцами, из которых наиболее важны следующие:

• key - ключ (или индекс).
• value - буквальное значение каждого элемента, полученное при разборе с помощью json_each.
• type - тип значения: один из null, true, false, integer, real, text, array, или object.
• fullkey - полный путь к элементу, например $[1] для второго элемента массива, или $.path.to.key для вложенного объекта.
• path - путь верхнего уровня - $ в качестве пути для элемента с fullkey $[0].

В этом примере SELECT * FROM json_each('[183183, 13913, 94944]') вернёт таблицу, похожую на приведённую ниже:

key|value|type|id|fullkey|path
0|183183|integer|1|$[0]|$
1|13913|integer|2|$[1]|$
2|94944|integer|3|$[2]|$

Вы можете использовать json_each с D1 Workers Binding API в Worker, создав выражение и используя JSON.stringify чтобы передать массив как привязанный параметр:

const stmt = context.env.DB
    .prepare("UPDATE users SET last_audited = ? WHERE id IN (SELECT value FROM json_each(?1))")
const resp = await stmt.bind(
    "2023-05-16T11:24:08+00:00",
    JSON.stringify([183183, 13913, 94944])
    ).run()

Это обновит только строки в вашей users таблицу, где id соответствует одному из трёх указанных значений.