← Cloudflare D1 / d1 / sql-api

Dotazování JSON

D1 nabízí vestavěnou podporu pro dotazování a parsování dat JSON uložených v databázi. To vám umožňuje:

• Cesty dotazování uvnitř uloženého objektu JSON, například přímým získáním hodnoty pojmenovaného klíče nebo indexu pole, což je užitečné zejména u větších objektů JSON.
• Vloží a/nebo nahradí hodnoty v objektu nebo poli.
• Rozbalte obsah objektu JSON nebo pole do více řádků, například pro použití jako součást WHERE ... IN predikát.
• Vytvořit generované sloupce která se automaticky naplní hodnotami z objektů JSON, které vkládáte.

Jednou z největších výhod přímého parsování JSON v D1 je, že přímo snižuje počet zpátečních cest (dotazů) do databáze. Omezuje případy, kdy musíte objekt JSON načíst do aplikace (1), zpracovat ho a poté zapsat zpět (2).

Díky tomu můžete data dotazovat přesněji a zmenšit výslednou sadu, kterou vaše aplikace musí dále zpracovávat a filtrovat.

Data JSON jsou uložena jako TEXT sloupec v D1. Typy JSON se řídí stejnými pravidla pro převod typů jako D1 obecně, včetně:

• JSON hodnota null se v D1 považuje za NULL.
• JSON číslo se považuje za INTEGER nebo REAL.
• Booleovské hodnoty jsou považovány za INTEGER hodnoty: true jako 1 a false jako 0.
• Hodnoty typu objekt a pole jako TEXT.

Následující tabulka popisuje funkce JSON zabudované do D1 a příklady jejich použití.

• json zástupný symbol argumentu může být objekt JSON, pole, řetězec, číslo nebo hodnota null.
• value argument přijímá pouze řetězcové literály a vstup zpracovává jako řetězec, i když jde o platný JSON. Výjimkou z tohoto pravidla je vnořování json_* funkce: vnější (obalující) funkce interpretuje návratovou hodnotu vnitřní (obalené) funkce jako JSON.
• path argument přijímá syntaxi procházení ve stylu cesty, například $ k odkazování na objekt/pole nejvyšší úrovně, $.key1.key2 k odkazování na vnořený objekt a $.key[2] k indexování do pole.

SQLite Rozšíření JSON ↗, na kterém D1 staví, obsahuje další příklady použití.

Funkce JSON vrátí malformed JSON chybu při práci s daty, která nejsou JSON nebo nejsou platným JSON. D1 považuje za platný JSON RFC 7159 ↗ odpovídá standardu.

V následujícím příkladu volání json_extract nad řetězcem (neplatný JSON) způsobí, že dotaz vrátí malformed JSON chyba:

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

Tím se vrátí chyba:

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

podpora D1 pro generované sloupce umožňuje vytvářet dynamické sloupce, které se generují na základě hodnot jiných sloupců, včetně extrahovaných nebo vypočítaných hodnot z dat JSON.

Tyto sloupce lze dotazovat stejně jako kterýkoli jiný sloupec a mohou mít indexy na nich definované. Pokud máte data JSON, která často dotazujete a filtrujete, může vytvoření generovaného sloupce a indexu výrazně zlepšit výkon dotazů.

Pokud chcete například definovat sloupec na základě hodnoty uvnitř většího objektu JSON, použijte AS klíčové slovo kombinované s Funkce JSON ke generování typovaného sloupce:

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
)

Viz Generované sloupce a dozvíte se více o generování sloupců.

V D1 existují tři způsoby, jak z objektu JSON extrahovat hodnotu:

• json_extract() funkce, například json_extract(text_column_containing_json, '$.path.to.value).
• -> operátor, který vrací reprezentaci hodnoty ve formátu JSON.
• ->> operátor, který vrací reprezentaci hodnoty ve formátu SQL.

-> a ->> operátory fungují obdobně jako stejné operátory v PostgreSQL a MySQL/MariaDB.

Uvažujme následující objekt JSON ve sloupci s názvem sensor_reading, můžete z něj hodnoty extrahovat přímo.

{
    "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

Délku pole JSON můžete zjistit dvěma způsoby:

1. Voláním json_array_length(value) přímo
2. Voláním json_array_length(value, path) k určení cesty k poli uvnitř objektu nebo vnějšího pole.

Vezměme si například následující objekt JSON uložený ve sloupci s názvem login_history, můžete přímo získat počet posledních přihlášení:

{
    "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

Můžete také použít json_array_length jako predikát ve složitějším dotazu, například WHERE json_array_length(some_column, '$.path.to.value') >= 5.

Hodnotu do existujícího objektu nebo pole JSON můžete vložit pomocí json_insert(). Například pokud máte TEXT sloupec nazvaný login_history v users tabulku obsahující následující objekt:

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

Chcete-li přidat nové časové razítko do history pole v rámci našeho login_history sloupec napište dotaz podobný tomuto:

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'

Zadejte tři argumenty pro json_insert:

1. Název sloupce obsahujícího JSON, který chcete upravit.
2. Cesta ke klíči v objektu, který chcete upravit.
3. Hodnota JSON, kterou chcete vložit. Pomocí [#] říká json_insert pro připojení na konec pole.

Pro nahrazení existující hodnoty použijte json_replace(), který přepíše existující dvojici klíč-hodnota, pokud již existuje. Chcete-li hodnotu nastavit bez ohledu na to, zda již existuje, použijte json_set().

Použijte json_each k rozbalení pole do více řádků. To se hodí při sestavování WHERE column IN (?) dotaz nad více hodnotami. Pokud byste například chtěli aktualizovat seznam uživatelů podle jejich celočíselného id, použijte json_each k vrácení tabulky, kde je každá hodnota sloupcem s názvem value:

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

Tím by se extrahovala pouze value sloupec z tabulky vrácené json_each, přičemž každý řádek představuje ID uživatelů, která jste předali jako pole.

json_each ve skutečnosti vrací tabulku s více sloupci, z nichž nejdůležitější jsou:

• key - klíč (nebo index).
• value - doslovná hodnota každého prvku analyzovaného pomocí json_each.
• type - typ hodnoty: jeden z null, true, false, integer, real, text, array, nebo object.
• fullkey - úplná cesta k prvku, například $[1] pro druhý prvek v poli, nebo $.path.to.key pro vnořený objekt.
• path - cesta nejvyšší úrovně - $ jako cestu pro prvek s fullkey z $[0].

V tomto příkladu SELECT * FROM json_each('[183183, 13913, 94944]') by vrátil tabulku podobnou té níže:

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

Můžete použít json_each hodnotou D1 Workers Binding API ve Workeru vytvořením příkazu a použitím JSON.stringify k předání pole jako vázaný parametr:

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()

Tím by se aktualizovaly pouze řádky ve vaší users tabulku, kde id odpovídá jedné ze tří zadaných možností.