JSON-Verarbeitungsfunktionen

JSON-Extraktionsfunktionen

Es gibt zwei Extraktionsfunktionen mit jeweiligen Operatoren. Die Operatoren können nur verwendet werden, wenn die Zeichenkette als logischer Typ JSON gespeichert ist. Diese Funktionen unterstützen dieselben zwei Ortsangaben wie die JSON-Skalarfunktionen.

Funktion	Alias	Operator	Beschreibung
json_exists(json, path)			Gibt true zurück, wenn der angegebene Pfad im json existiert, sonst false.
json_extract(json, path)	json_extract_path	->	Extrahiert JSON aus json am angegebenen path. Ist path eine LIST, ist das Ergebnis eine LIST von JSON.
json_extract_string(json, path)	json_extract_path_text	->>	Extrahiert VARCHAR aus json am angegebenen path. Ist path eine LIST, ist das Ergebnis eine LIST von VARCHAR.
json_value(json, path)			Extrahiert JSON aus json am angegebenen path. Ist das json am angegebenen Pfad kein Skalarwert, wird NULL zurückgegeben.

Beachten Sie, dass der Pfeiloperator ->, der für JSON-Extrakte verwendet wird, eine niedrige Präzedenz hat, da er auch in Lambda-Funktionen vorkommt. Deshalb müssen Sie den Operator -> bei Vergleichen wie Gleichheit (=) in Klammern setzen. Zum Beispiel:

SELECT ((JSON '{"field": 42}')->'field') = 42;

Warning Der JSON-Datentyp von DuckDB verwendet 0-basierte Indizierung.

Beispiele:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json_extract(j, '$.family') FROM example;

"anatidae"

SELECT j->'$.family' FROM example;

"anatidae"

SELECT j->'$.species[0]' FROM example;

"duck"

SELECT j->'$.species[*]' FROM example;

["duck", "goose", "swan", null]

SELECT j->>'$.species[*]' FROM example;

[duck, goose, swan, null]

SELECT j->'$.species'->0 FROM example;

"duck"

SELECT j->'species'->['/0', '/1'] FROM example;

['"duck"', '"goose"']

SELECT json_extract_string(j, '$.family') FROM example;

anatidae

SELECT j->>'$.family' FROM example;

anatidae

SELECT j->>'$.species[0]' FROM example;

duck

SELECT j->'species'->>0 FROM example;

duck

SELECT j->'species'->>['/0', '/1'] FROM example;

[duck, goose]

Beachten Sie, dass der JSON-Datentyp von DuckDB 0-basierte Indizierung verwendet.

Wenn mehrere Werte aus demselben JSON extrahiert werden sollen, ist es effizienter, eine Liste von Pfaden zu extrahieren:

Das Folgende führt dazu, dass das JSON zweimal geparst wird:

Dadurch wird die Abfrage langsamer und verbraucht mehr Speicher:

SELECT
    json_extract(j, 'family') AS family,
    json_extract(j, 'species') AS species
FROM example;

family	species
“anatidae”	[“duck”,“goose”,“swan”,null]

Das Folgende liefert dasselbe Ergebnis, ist aber schneller und speichereffizienter:

WITH extracted AS (
    SELECT json_extract(j, ['family', 'species']) AS extracted_list
    FROM example
)
SELECT
    extracted_list[1] AS family,
    extracted_list[2] AS species
FROM extracted;

JSON-Skalarfunktionen

Die folgenden skalaren JSON-Funktionen können verwendet werden, um Informationen über gespeicherte JSON-Werte zu erhalten. Mit Ausnahme von json_valid(json) erzeugen alle JSON-Funktionen einen Fehler, wenn ungültiges JSON übergeben wird.

Wir unterstützen zwei Arten von Notation für Orte innerhalb von JSON: JSON Pointer und JSONPath.

Funktion	Beschreibung
json_array_length(json[, path])	Gibt die Anzahl der Elemente im JSON-Array json zurück, oder 0, wenn es kein JSON-Array ist. Ist path angegeben, wird die Anzahl der Elemente im JSON-Array am angegebenen path zurückgegeben. Ist path eine LIST, ist das Ergebnis eine LIST von Array-Längen.
json_contains(json_haystack, json_needle)	Gibt true zurück, wenn json_needle in json_haystack enthalten ist. Beide Parameter sind vom Typ JSON, json_needle kann aber auch ein numerischer Wert oder eine Zeichenkette sein; die Zeichenkette muss in doppelte Anführungszeichen gesetzt werden.
json_keys(json[, path])	Gibt die Schlüssel von json als LIST von VARCHAR zurück, wenn json ein JSON-Objekt ist. Ist path angegeben, werden die Schlüssel des JSON-Objekts am angegebenen path zurückgegeben. Ist path eine LIST, ist das Ergebnis eine LIST von LIST von VARCHAR.
json_structure(json)	Gibt die Struktur von json zurück. Standard ist JSON, wenn die Struktur inkonsistent ist (z. B. inkompatible Typen in einem Array).
json_type(json[, path])	Gibt den Typ des übergebenen json zurück: eines von ARRAY, BIGINT, BOOLEAN, DOUBLE, OBJECT, UBIGINT, VARCHAR und NULL. Ist path angegeben, wird der Typ des Elements am angegebenen path zurückgegeben. Ist path eine LIST, ist das Ergebnis eine LIST von Typen.
json_valid(json)	Gibt zurück, ob json gültiges JSON ist.
json(json)	Parst und minifiziert json.

Die JSONPointer-Syntax trennt jedes Feld mit einem /. Um zum Beispiel das erste Element des Arrays mit dem Schlüssel duck zu extrahieren, können Sie Folgendes tun:

SELECT json_extract('{"duck": [1, 2, 3]}', '/duck/0');

1

Die JSONPath-Syntax trennt Felder mit einem ., greift auf Array-Elemente mit [i] zu und beginnt immer mit $. Mit demselben Beispiel können wir Folgendes tun:

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');

1

Beachten Sie, dass der JSON-Datentyp von DuckDB 0-basierte Indizierung verwendet.

JSONPath ist ausdrucksstärker und kann auch vom Ende von Listen zugreifen:

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');

3

JSONPath erlaubt auch das Escapen von Syntax-Tokens mit doppelten Anführungszeichen:

SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');

2

Beispiele anhand der biologischen Familie Anatidae:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json(j) FROM example;

{"family":"anatidae","species":["duck","goose","swan",null]}

SELECT j.family FROM example;

"anatidae"

SELECT j.species[0] FROM example;

"duck"

SELECT json_valid(j) FROM example;

true

SELECT json_valid('{');

false

SELECT json_array_length('["duck", "goose", "swan", null]');

4

SELECT json_array_length(j, 'species') FROM example;

4

SELECT json_array_length(j, '/species') FROM example;

4

SELECT json_array_length(j, '$.species') FROM example;

4

SELECT json_array_length(j, ['$.species']) FROM example;

[4]

SELECT json_type(j) FROM example;

OBJECT

SELECT json_keys(j) FROM example;

[family, species]

SELECT json_structure(j) FROM example;

{"family":"VARCHAR","species":["VARCHAR"]}

SELECT json_structure('["duck", {"family": "anatidae"}]');

["JSON"]

SELECT json_contains('{"key": "value"}', '"value"');

true

SELECT json_contains('{"key": 1}', '1');

true

SELECT json_contains('{"top_key": {"key": "value"}}', '{"key": "value"}');

true

JSON-Aggregatfunktionen

Es gibt drei JSON-Aggregatfunktionen.

Funktion	Beschreibung
json_group_array(any)	Gibt ein JSON-Array mit allen Werten von any in der Aggregation zurück.
json_group_object(key, value)	Gibt ein JSON-Objekt mit allen key-/value-Paaren in der Aggregation zurück.
json_group_structure(json)	Gibt die kombinierte json_structure aller json in der Aggregation zurück.

Beispiele:

CREATE TABLE example1 (k VARCHAR, v INTEGER);
INSERT INTO example1 VALUES ('duck', 42), ('goose', 7);

SELECT json_group_array(v) FROM example1;

[42, 7]

SELECT json_group_object(k, v) FROM example1;

{"duck":42,"goose":7}

CREATE TABLE example2 (j JSON);
INSERT INTO example2 VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_group_structure(j) FROM example2;

{"family":"VARCHAR","species":["VARCHAR"],"coolness":"DOUBLE","hair":"BOOLEAN"}

JSON in geschachtelte Typen transformieren

In vielen Fällen ist es ineffizient, Werte einzeln aus JSON zu extrahieren. Stattdessen können wir alle Werte auf einmal „extrahieren“ und JSON in die geschachtelten Typen LIST und STRUCT transformieren.

Funktion	Beschreibung
json_transform(json, structure)	Transformiert json gemäß der angegebenen structure.
from_json(json, structure)	Alias für json_transform.
json_transform_strict(json, structure)	Wie json_transform, wirft aber einen Fehler, wenn die Typumwandlung fehlschlägt.
from_json_strict(json, structure)	Alias für json_transform_strict.

Das Argument structure ist JSON in derselben Form, wie sie von json_structure zurückgegeben wird. Das Argument structure kann angepasst werden, um das JSON in die gewünschte Struktur und die gewünschten Typen zu transformieren. Es ist möglich, weniger Schlüssel-Wert-Paare zu extrahieren, als im JSON vorhanden sind, und auch mehr: fehlende Schlüssel werden zu NULL.

Beispiele:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_transform(j, '{"family": "VARCHAR", "coolness": "DOUBLE"}') FROM example;

{'family': anatidae, 'coolness': 42.420000}
{'family': canidae, 'coolness': NULL}

SELECT json_transform(j, '{"family": "TINYINT", "coolness": "DECIMAL(4, 2)"}') FROM example;

{'family': NULL, 'coolness': 42.42}
{'family': NULL, 'coolness': NULL}

SELECT json_transform_strict(j, '{"family": "TINYINT", "coolness": "DOUBLE"}') FROM example;

Invalid Input Error:
Failed to cast value: "anatidae"

JSON-Tabellenfunktionen

DuckDB implementiert zwei JSON-Tabellenfunktionen, die einen JSON-Wert entgegennehmen und daraus eine Tabelle erzeugen.

Funktion	Beschreibung
json_each(json[ ,path]	Durchläuft json und gibt eine Zeile für jedes Element im Array oder Objekt der obersten Ebene zurück.
json_tree(json[ ,path]	Durchläuft json tiefenorientiert und gibt eine Zeile für jedes Element in der Struktur zurück.

Ist das Element kein Array oder Objekt, wird das Element selbst zurückgegeben. Ist das optionale Argument path angegeben, beginnt der Durchlauf am Element des angegebenen Pfads statt am Wurzelelement.

Die resultierende Tabelle hat die folgenden Spalten:

Feld	Typ	Beschreibung
key	VARCHAR	Schlüssel des Elements relativ zum Elternknoten
value	JSON	Wert des Elements
type	VARCHAR	json_type (Funktion) dieses Elements
atom	JSON	json_value (Funktion) dieses Elements
id	UBIGINT	Elementkennung, nummeriert nach Parse-Reihenfolge
parent	UBIGINT	id des Elternelements
fullkey	VARCHAR	JSON-Pfad zum Element
path	VARCHAR	JSON-Pfad zum Elternelement
json	JSON (virtuell)	Der Parameter json
root	TEXT (virtuell)	Der Parameter path
rowid	BIGINT (virtuell)	Die Zeilenkennung

Diese Funktionen entsprechen den gleichnamigen Funktionen von SQLite. Beachten Sie, dass json_each und json_tree auf vorherige Unterabfragen in derselben FROM-Klausel verweisen und daher Lateral Joins sind.

Beispiele:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT je.*, je.rowid
FROM example AS e, json_each(e.j) AS je;

key	value	type	atom	id	parent	fullkey	path	rowid
family	“anatidae”	VARCHAR	“anatidae”	2	NULL	$.family	$	0
species	[“duck”,“goose”]	ARRAY	NULL	4	NULL	$.species	$	1
coolness	42.42	DOUBLE	42.42	8	NULL	$.coolness	$	2
family	“canidae”	VARCHAR	“canidae”	2	NULL	$.family	$	0
species	[“labrador”,“bulldog”]	ARRAY	NULL	4	NULL	$.species	$	1
hair	true	BOOLEAN	true	8	NULL	$.hair	$	2

SELECT je.*, je.rowid
FROM example AS e, json_each(e.j, '$.species') AS je;

key	value	type	atom	id	parent	fullkey	path	rowid
0	“duck”	VARCHAR	“duck”	5	NULL	$.species[0]	$.species	0
1	“goose”	VARCHAR	“goose”	6	NULL	$.species[1]	$.species	1
0	“labrador”	VARCHAR	“labrador”	5	NULL	$.species[0]	$.species	0
1	“bulldog”	VARCHAR	“bulldog”	6	NULL	$.species[1]	$.species	1

SELECT je.key, je.value, je.type, je.id, je.parent, je.fullkey, je.rowid
FROM example AS e, json_tree(e.j) AS je;

key	value	type	id	parent	fullkey	rowid
NULL	{“family”:“anatidae”,“species”:[“duck”,“goose”],“coolness”:42.42}	OBJECT	0	NULL	$	0
family	“anatidae”	VARCHAR	2	0	$.family	1
species	[“duck”,“goose”]	ARRAY	4	0	$.species	2
0	“duck”	VARCHAR	5	4	$.species[0]	3
1	“goose”	VARCHAR	6	4	$.species[1]	4
coolness	42.42	DOUBLE	8	0	$.coolness	5
NULL	{“family”:“canidae”,“species”:[“labrador”,“bulldog”],“hair”:true}	OBJECT	0	NULL	$	0
family	“canidae”	VARCHAR	2	0	$.family	1
species	[“labrador”,“bulldog”]	ARRAY	4	0	$.species	2
0	“labrador”	VARCHAR	5	4	$.species[0]	3
1	“bulldog”	VARCHAR	6	4	$.species[1]	4
hair	true	BOOLEAN	8	0	$.hair	5